Trouble running Excel Macro from Access with VBA
Posted on 2002-03-22
I have exported a report to Excel, no prob.
Now I need to tell Excel to run a macro. The macro works, and if I pull up the macros list (from tools, macro) I can see it fine.
However, trying to call it with VBA I get the error that the macro cannot be found.
Here is the code:
Set XL = Excel.Application
Set WrkBk = Windows.Application.Workbooks.Open("Graphdata.xls")
WrkBk.Application.Visible = True
Set WrkBk2 = Windows.Application.Workbooks.Open("GraphdataNew.xls")
WrkBk2.Application.Visible = True
I also tried
I always get the same error.
I tried it without quotes, and it said variable not defined.
I tried Excel.Application.Run("GraphData!sheet1.FindSheetNames")
In the immediate window this last line would execute the first line of the macro (which is just a msg box to let me know it started) then would say 'subscript out of range.'
I can run the macro fine, no errors, from Excel!
I have to have this fixed today. pls help!