Run Excel macro in Personal.xls against multiple xls files
Posted on 2005-03-23
I've looked through a lot of the q&a's but none quite address my problem. I have a macro named "PrepareRange" defined in my Personal.xls file. I have 53 xls files that I can open, go to tools, macros,...and see the macro listed. From Access I am opening both the Personal.xls and *.xls with:
Set xlApp = CreateObject("excel.application") 'open Excel
'open the personal file holding macro
Set psFile = xlApp.Workbooks.Open("C:\Program Files\Microsoft Office\Office\XLStart\Personal.xls")
xlApp.Visible = True
Set wbFile = CreateObject("excel.application")
Set ccFile = wbFile.Workbooks.Open(path) 'path is previously defined
wbFile.Visible = True
When I set a breakpoint after this snippet, I can see the macro from Personal.xls, but not from *.xls. Also, xlApp.Run "PrepareRange" runs the macro in Personal.xls. If I substitute -- wbFile.Run "PrepareRange" nothing happens.
Can somebody help?