Simulating a Click event through Automation from Access

>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

On Tue, 06 Nov 2007 16:58:45 -0800, oliver james
>
<oliverja...@mailinator.comwrote:
>
That's right. Change the event procedure from Private to Public, then
call it like any other public procedure.
>
-Tom.
>

On Nov 7, 4:00 am, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
>>>>>
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

On Nov 7, 5:29 pm, oliver james <oliverja...@mailinator.comwrote:
>
>
>
>
>>>>>>>>>>>>>>>>>
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
CDMAPos...@FortuneJames.com- Hide quoted text -
>
- Show quoted text -