How do I run a macro in an Excel W/book From Access

[JackiD]

How do I run a macro in an Excel W/book that is linked to
an Access database from within the Database. As the
Workbook is open when the Linked table (or it's form) is
open I do not need to open the workbook, just call the
macro to run but I can't get the code right Duh!
any help would be appreciated.
Regards

 

[Ken Snell]

Here's some sample code for opening an EXCEL workbook and running a VBA
macro that's in the workbook in a public module:

Public Sub TestMacroRun()
Dim xlx As Object, xlw As Object
Set xlx = CreateObject("excel.application")
Set xlw = xlx.workbooks.Open("C:\Filename.xls")
xlx.Run "Filename.xls!MacroName()"
xlw.Close False
xlx.Quit
Set xlw = Nothing
Set xlx = Nothing
End Sub

 

[JackiD]

Thank you for your reply
But.., I have a form based on a table that is linked to
an excel spredsheet, when the form is open (or the table
for that matter) the spreadsheet is open? so this returns
can't open as workbook is already open and all I want to
do is run the Macro from that position?!
Regards
JackiD

 

[Ken Snell]

I'm not sure that you can run a macro from that workbook? Although the file
is "open" while it's linked to an open database, I doubt that EXCEL is
running as well. Without EXCEL running, I'm not sure if you can run a macro
in the linked book.

While your database is open, go to the Immediate Window and see if you can
run the macro manually:

Excel.Workbooks("Filename.xls").Run "Filename.xls!MacroName()"

(try it with and without the () at the end of the MacroName).

This may give us a clue as to whether what you seek to do can be done.

 

[JackiD]

Hi Ken

I have tried various cimbinations of thisand it doesn't
work

I get compile error variable not defined

I'm pretty new to acces so I wasn't too sure how to get
to the immediate window

it's not like Excels Visual Basic so I tried the Code
builder and the macro builder??

Regards

JackiD

I'm not sure that you can run a macro from that workbook?
Although the file

is "open" while it's linked to an open database, I doubt
that EXCEL is

running as well. Without EXCEL running, I'm not sure if
you can run a macro in the linked book.

While your database is open, go to the Immediate Window
and see if you can run the macro manually:

Excel.Workbooks("Filename.xls").Run "Filename.xls!
MacroName()"

(try it with and without the () at the end of the
MacroName).

This may give us a clue as to whether what you seek to do
can be done.

 

[Madelief]

Hi JackiD,

Did you add the Microsoft Excel Object Library to the Access References?
You can check so by going into the VB editor in Access and select Tools,
References. In there the Microsoft Excel Object Library should be marked. If
it is not then just select it.

Brgds,
Madelief

 

[Ken Snell]

Madelief is correct. I forgot that the EXCEL objects library needs to be in
your reference list in order to run that sample code step in the Immediate
Window. (You don't need it to be checked for the VBA code example because
that example uses "late binding" and ACCESS goes and gets its own reference
at that time.)

I think you'll be better off if you can run the EXCEL macro's code steps in
ACCESS VBA code. What is the EXCEL VBA macro supposed to do?

 

[JackiD]

Thanks Madelief and Ken
Been working no chance before now to check this board, or
to check your advice yet will have a go soon.
In answer to Ken's question..
We have an Excel Add-In called PiDas that collects
sampled data from process equipment into an Excel
workbook, to get PiDas to collect the data it has to be
forced. Changing the time or the date on the sheet is the
easiest way to do this and Force the collection. I have a
simple little macro that does that, which I'd like to
run, and another that moves the gathered info to a sheet
where there are no calculations. This worksheet I can use
as a table in my Access Database. There are some little
quirks with PiDas that I wish to get around by doing it
this way.
Regards
Jackid