Simulating a Click event through Automation from Access

[oliver james]

I'm trying to automate an Excel spreadsheet from Access. I've
established a link and loaded data from a recordset onto a worksheet
in Excel. I now want to perform some manipulation on the data. I
previously set up a command button in the spreadsheet to run the VBA
to do this manipulation; this VBA is stored in the button's Click
event. Now that I am learning about Automation, I would like to "call"
this VBA from Access.

Can anyone tell me how I can simulate clicking this command button on
the spreadsheet through VBA from Access?

Or is there another way to approach this such as creating a public
procedure in the spreadsheet which can be called from Access?

Your assistance is very much appreciated.

Oliver

[Tom van Stiphout]

On Tue, 06 Nov 2007 16:58:45 -0800, oliver james
<olive...@mailinator.com> wrote:

That's right. Change the event procedure from Private to Public, then
call it like any other public procedure.

-Tom.

[oliver james]

On Nov 7, 4:00 am, Tom van Stiphout <no.spam.tom7...@cox.net> wrote:
> On Tue, 06 Nov 2007 16:58:45 -0800, oliver james
>

> <oliverja...@mailinator.com> wrote:
>
> That's right. Change the event procedure from Private to Public, then
> call it like any other public procedure.
>
> -Tom.
>

I'm struggling to call a public procedure in a standard module in
Excel from Access, can anyone tell me why the following code does not
work?

In the Excel spreadsheet I have declared the following public function
in a standard module 'PubFns':

Public Function pubintTest()
pubintTest = 100
End Function

In an Access database I have the following subroutine:

Public Sub pubsubRunProc()
Dim xlapp As Excel.Application
Dim xlwkb As Excel.Workbook
Dim intX As Integer

Set xlapp = New Excel.Application
xlapp.Visible = True
Set xlwkb = xlapp.Workbooks.Open("C:\Test1.xls")

intX = xlapp.pubintTest
Debug.Print intX
End Sub

When I run pubsubRunProc from the Immediate window the spreadsheet
opens but I get the error message: "Run-time error '438' Object
doesn't support this property or method"

Any assistance would be appreciated.

Cheers,

Oliver

[CDMAP...@fortunejames.com]

I got an example to work using the same code as yours except I used:

intX = xlapp.Application.Run("pubintTest")

That idea came from recording a macro that ran a macro.

James A. Fortune
CDMAP...@FortuneJames.com

[oliver james]

> CDMAPos...@FortuneJames.com- Hide quoted text -
>
> - Show quoted text -

That works perfectly thank you.

I also had a case where I had to pass a parameter to the called
procedure. For this I used the following syntax:

intX = xlapp.Application.Run "pubintTest", "1"

In other words, without the brackets.