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

Re: Simulating a Click event through Automation from Access

On Tue, 06 Nov 2007 16:58:45 -0800, oliver james
wrote:

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

-Tom.


>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

Re: Simulating a Click event through Automation from Access

On Nov 7, 4:00 am, Tom van Stiphout wrote:
> On Tue, 06 Nov 2007 16:58:45 -0800, oliver james
>
> 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

Re: Simulating a Click event through Automation from Access

On Nov 7, 5:29 pm, oliver james wrote:
> On Nov 7, 4:00 am, Tom van Stiphout wrote:
>
> > On Tue, 06 Nov 2007 16:58:45 -0800, oliver james
>
> > 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

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
CDMAPoster@FortuneJames.com

Re: Simulating a Click event through Automation from Access

On Nov 8, 12:01 am, CDMAPos...@FortuneJames.com wrote:
> On Nov 7, 5:29 pm, oliver james wrote:
>
>
>
>
>
> > On Nov 7, 4:00 am, Tom van Stiphout wrote:
>
> > > On Tue, 06 Nov 2007 16:58:45 -0800, oliver james
>
> > > 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
>
> 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 -

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.