Late Binding

[Barry]

Since I have users who use Office 2007 and Office 2003, I am attempting to
use late binding to open an Excel worksheet from MS Access. The worksheet I
am trying to open contains an autoexec macro.

I cannot get the code below to work.

Any assistance will be greatly appreciated.

Function Excel_Macro1()
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open "K:\RM\Apps\Access\218Labor\Macro1.xls"
objExcel.Quit
Set objExcel = Nothing
End Function

 

[Douglas J. Steele]

What does "cannot get the code below to work" mean in this context? If
you're getting an error, what is it? If you're not getting an error, what
are you getting (and what do you want instead)?

Of course, the fact that you've got a Quit statement immediately after you
open the workbook does imply that the code isn't going to do anything
useful...

 

[Dale Fye]

In addition to Doug's observations, you may have difficulties with the
autoexec macro in the 2007 Excel workbook, unless the file is in a trusted
location. If you open the application, manually, on a machine running 2007,
does the macro run automatically, or do you have to enable macros each time
the workbook is opened?

 

[Barry]

I am trying to open "K:\RM\Apps\Access\218Labor\Macro1.xls" which contains an
AutoOpen Macro that opens another worksheet, does some formatting and saves
the other worksheet. I get no error but it appaers that the AutoOpen Macro
is not executing.

 

[Barry]

I have enable all macros set in the trust center so there are no problems
automatically executing the macro when the worksheet is opened manually.

 

[Barry]

The solution was to add the following code to run the macro:

With objExcel.Application
.Workbooks.Open "K:\RM\Apps\Access\218Labor\Macro1.xls"
.Visible = True
.ActiveWorkbook.RunAutoMacros 1
End With