Use VBA to Open Excel file and Run Excel Macro, Can't get it to work.
Posted on 2007-03-20
I am attempting to open an Excel file via Access VBA and then run an Excel Macro.
I have the code and have looked in the knowledgebase for similar solutions, however for some reason the VBA errors on the line where the Excel Macro is to be run, stating "Run-time error 438: Object doesn't support this property or method."
Am I missing something obvious OR is this related to the MS lawsuit where certain things relating to Excel functionality were eliminating??
Here's my code:
Dim objXL As Object
Dim objWB As Object
Set objXL = CreateObject("Excel.Application")
'To open existing workbook
Set objWB = objXL.Workbooks.Open("C:\PaulChesenData\ExcelImport1.xls")
Set objWB = Nothing
Set objXL = Nothing
The code fails on this line:
I've tried several variations of referencing the Macro, but none have worked including:
The filename is ExcelImport1.xls, the worksheet with the Macro is "TestData" and the Macroname is "UpdateLookupData"