run excel macro from word macro

[Joe Patrick]

Hi!

I could have sworn I had this working before but can't get it to work now! I need a macro from word to open an excel spreadsheet and run the Autpen macro. It opens the workbook but just stops there.

Can anyone help me with this? Thank you in advance!

Sub RunExcelMacroNOTWORKING()
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbook.Open "U:\TPCentral_Too\ADA_Requests\ADA_Requests.xls"
On Error Resume Next
Excel.Application.Run "U:\TPCentral_Too\ADA_Requests\ADA_Requests.xls!Autpen"
End Sub

 

[Joe Patrick]

correction:

Sub RunExcelMacroNOTWORKING()
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Open "U:\TPCentral_Too\ADA_Requests\ADA_Requests.xls"
On Error Resume Next
Excel.Application.Run "U:\TPCentral_Too\ADA_Requests\ADA_Requests.xls!Autpen"
End Sub

 

[bertie]

Hi Joe, and welcome to the board.

Note the path to the workbook is enclosed in parenthesis.
And assuming in the excel workbook you have a macro named AutoOpen in the ThisWorkbook module try this:

   With objExcel
      .Visible = True
      .Workbooks.Open[COLOR="Red"] ([/COLOR]"U:\TPCentral_Too\ADA_Requests\ADA_Requests.xls"[COLOR="red"])[/COLOR]
      .Application.Run ("[COLOR="Red"]ThisWorkbook.AutoOpen[/COLOR]")
   End With

 

[Joe Patrick]

Bertie! Thank you so much! That works but I get an 'automation error' at the end that freezes my word.

My AutoOpen in excel does a bunch of stuff then saves as a webpage then closes the workbook. Does that affect it?

The new code with your help:

Sub RunExcelMacro()
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
With objExcel
.Visible = True
.Workbooks.Open ("U:\TPCentral_Too\ADA_Requests\ADA_Requests.xls")
.Application.Run ("ThisWorkbook.AutoOpen")
End With
End Sub

The last bit of the AutoOpen code:

Sub AutoOpen()
'import text, delimitate, format
ActiveWorkbook.Save
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="U:\TPCentral_Too\ADA_Requests\ADA_Requests.htm" _
, FileFormat:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub

 

[bertie]

Hi Joe,

Remember, you are controlling the workbook from within word. So don't close it using the Excel Macro. The excel Macro is:

[color=darkblue]Sub[/color] AutoOpen()
   [color=green]'import text, delimitate, format[/color]

   Application.DisplayAlerts = [color=darkblue]False[/color]
   
   [color=darkblue]With[/color] ThisWorkbook
      .Save
      .SaveAs Filename:="[COLOR="red"]c:\temp\ADA_Requests.htm[/COLOR]", _
              FileFormat:=xlHtml, _
              ReadOnlyRecommended:=False, _
              CreateBackup:=[color=darkblue]False[/color]
   [color=darkblue]End[/color] [color=darkblue]With[/color]
   
   Application.DisplayAlerts = [color=darkblue]True[/color]
   
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

To Control Excel from Word you need to set a reference to the Excel Object Library:
Open the MSWord VBA Editor
Click Tools => References => Microsoft Excel Object xx Library, where xx is a number.

To control the workbook we need a variable for it, wbExcel.

Sub RunExcelMacro()
   '=======================================================
   'requires
   'Tools => Reference => Microsof Excel xx Object Library
   '======================================================
   Dim objExcel As Object
   Dim [COLOR="Red"]wbExcel[/COLOR] As Excel.workbook
   
   Set objExcel = CreateObject("Excel.Application")
   Set [COLOR="red"]wbExcel [/COLOR]= objExcel.Workbooks.Open("[COLOR="Red"]c:\Temp\RunMacro.xls[/COLOR]")
   
   With objExcel
      .Visible = True
      .Application.Run ("ThisWorkbook.AutoOpen")
   End With
   
   'close the workbook, quit excel and tidy up
   [COLOR="red"]wbExcel.Close[/COLOR] SaveChanges:=False
  [COLOR="Blue"] 'objExcel.Quit[/COLOR]
   
   Set objExcel = Nothing
   Set wbExcel = Nothing
   
End Sub

The line objExcel.Quit closes the Excel Application, including any other files you may have open. I have commented this out. You can decide whether or not you want to use it.

EDIT: Change my test Folders.

 

[bertie]

Final thoughts, 'cause it's way past my bedtime.

In the MsWord macro these lines clear the memory set aside for the Excel and Workbook variables.

Put them into the correct order. Clear the Workbook before the Application.

   Set wbExcel = Nothing
   Set objExcel = Nothing
End Sub

 

[Joe Patrick]

Thank you again, that's perfect. You're a star!

 

[RamaG]

I cannot get this past the interpreter because I can't find the necessary object library on my system. Microsoft responses (from their developer site) are not helpful as far as how to get an updated version of the library.

Just for more information, I am currently able to do the reverse (run a word macro from an excel macro), so I am not clear why there would be a difference.

Any thoughts would be appreciated.