Executing an Excel Macro from Word

[rju]

I have the following VBA code in Word:

Dim xls As Object
Dim wrk As Excel.workbook
Dim xlfilename As String
xlfilename = "C:\QtestExcel.xls"
Set xls = CreateObject("Excel.Application")
xls.Visible = True
Set wrk = xls.workbooks.Open(xlfilename)
Application.Run "xlfilename!AggregateFileDocs"

when I run the routine I get "Unable to run the specified macro." Am
referencing the macro incorrectly? I know that WB opens correctly...th
macro does work in Excel, I executed it manually when the WB opened...
also tried

Application.Run "QtestExcel!AggregateFileDocs"

that didn't work either. Thoughts?

Thanks,
Ro

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Might be better to post to microsoft.public.excel.programming. However, if
you rename the macro to auto_open, it might just run when the file is opened
and save you the trouble.

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

 

[Peter Hewett]

Hi rju

The problem is is that you're using the wrong application to run your macro!
The application object is always the host applications application object, in
this case Word. You need to use the Excel application object, something like
this:

Public Sub XLMacroTest()
Dim xls As Object
Dim wrk As Excel.workbook
Dim xlfilename As String

xlfilename = "C:\QtestExcel.xls"
Set xls = New Excel.Application
xls.Visible = True
Set wrk = xls.workbooks.Open(xlfilename)
xls.Run "AggregateFileDocs"
End Sub

Obviously the scope of the macro must be public!

HTH + Cheers - Peter