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
<ol*********@mailinator.comwrote:

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

[oliver james]

On Nov 7, 4:00 am, Tom van Stiphout <[e-mail]:

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

<[e-mail]:

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

[CDMAPoster]

On Nov 7, 5:29 pm, oliver james <[e-mail]:

On Nov 7, 4:00 am, Tom van Stiphout <[e-mail]:

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

<[e-mail]:

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

[oliver james]

On Nov 8, 12:01 am, [e-mail] wrote:

On Nov 7, 5:29 pm, oliver james <[e-mail]:

On Nov 7, 4:00 am, Tom van Stiphout <[e-mail]:

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

<[e-mail]:

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
[e-mail]- 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.