Is it Possible to run a macro in an Excel W/book From Access

[Jax]

Is it Possible to run a macro in an Excel workbook From an Access Database?
I am in the process of making a database in Access 97 that has links to a couple of Excel 97 WorkSheets (in same Workbook). There is a macro in the workbook that I'd like to run, preferably without getting the user to have to open the workbook run, the macro then return to Access. I'm almost sure that I read somewhere that this was possible but I've no idea how it would be done.
Any suggestions would be appreciated.
Thanks in advance
Jax

 

[smozgur]

Re: Is it Possible to run a macro in an Excel W/book From Ac

Hi Jax,

This will still open the workbook but user won't see it. And it will close it after running macro.

'----
Dim xls As Object
Dim wrk As Object
Set xls = CreateObject("Excel.Application")
Set wrk = xls.workbooks.Open("C:\Book1.xls")
xls.Run ("Module1.SubName")
wrk.Close False
xls.Quit
Set xls = Nothing
'----

I hope this helps.
Suat

 

[Jax]

Re: Is it Possible to run a macro in an Excel W/book From Ac

Thanks Suat,
I've not done any work with VB in access before so....
I put this in the Form Class module
and I get: compile error variable not defined
And workbooks is highlighted
I've obviously NOT got Something?

Private Sub Command12_Click()
Dim xls As Object
Dim wrk As Object
Set xls = CreateObject("Excel.Application")
Set wrk = xls.Workbooks.Open("D:\Excel 97 Files\Trial.xls")
xls.Run ("Module1. ReSetTime()")
wrk.Close False
xls.Quit
Set xls = Nothing
End Sub

Regards
Jax
PS I did try Set wrk = xls.Workbooks.Open("D:\Excel 97 Files\Trial.xls") without xls. to no avail

 

[smozgur]

Re: Is it Possible to run a macro in an Excel W/book From Ac

Hi Jax,

Create a button on your form, then put a command button on this form (click Cancel when it goes to wizard to create button) then right click on the button and point to Built Event. Select Code Builder in the list. It will goto VBA and show you the Click event of the Command button, just like below:

Private Sub Command1_Click() 

End Sub

Now put your code into this event sub, it will seem like below when you place the code in it:

Private Sub Command1_Click() 
Dim xls As Object 
Dim wrk As Object 
Set xls = CreateObject("Excel.Application") 
Set wrk = xls.Workbooks.Open("D:\Excel 97 Files\Trial.xls") 
xls.Run ("Module1.ReSetTime") 
wrk.Close False 
xls.Quit 
Set xls = Nothing 
End Sub

Please note I have changed your code, because it had blanks before and after period (.). You should NOT use spaces before/after periods because they belong to left side object and properties or methods.

(Module1.ReSetTime: means you are calling a macro in workbook, named as "ReSetTime" and which is placed in a module named as "Module1")

I hope this helps.
Suat

 

[Jax]

Re: Is it Possible to run a macro in an Excel W/book From Ac

Resolved
Thank you Suat,
I doubt whether I’d have picked up the Blanks!
It worked well when I put the command button onto a form that is NOT linked to the worksheet in the workbook that I want to run the Macro in. Some things are just so very obvious after you figure them out they make you feel sooo… stupid!!
Also thank you for the tips, much appreciated.
Thanks again
Jax

 

[smozgur]

Re: Is it Possible to run a macro in an Excel W/book From Ac

Glad to hear it is solved, Jax!

Suat