Running Excel macros from an external .vbs file

[SlakeB]

Does anyone know how to do this? I have an excel spreadsheet with two buttons on it. Each button triggers a macro. I want an external .vbs file to run those macros without me having to go into excel and push the buttons.

 

[SlakeB]

Here is what I have so far (something similar worked with Access macros that I was dealing with). It successfully opens the excel document, but it can't find the macro.

Dim oWSH
Dim retVal

Set oWSH = WScript.CreateObject("WScript.Shell")
retVal = oWSH.Run("excel.exe " & Chr(34) & "D:\Docs\JaydipDB\OutputFiles\testExcelMacros.xls" & Chr(34) & " /x macTest1", 1, True)

I also tried this...still can't find the macro

Dim oWSH
Dim retVal

Set oWSH = WScript.CreateObject("WScript.Shell")
retVal = oWSH.Run("excel.exe " & Chr(34) & "D:\Docs\JaydipDB\OutputFiles\testExcelMacros.xls" & Chr(34) & " /x testExcelMacros.XLS'!macTest1", 1, True)

 

[PHV]

Something like this ?
Dim oXL, oWB
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.Workbooks.Open("D:\Docs\JaydipDB\OutputFiles\testExcelMacros.xls")
oXL.Run "macTest1"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SlakeB]

hey PHV, I used the code below and it worked great. Do you know how to stop the "Do you want to save changes" window from popping up?

Dim X

Set X=CreateObject("Excel.Application")
Set W=X.Workbooks.Open("D:\Docs\JaydipDB\OutputFiles\testExcelMacros.xls")
X.Run "macTest1"
X.Run "macTest2"
X.Quit

I tried adding "X.Save" before "X.QUIT" but then I got another pop up window asking if I wanted to replace "RESUME.XLW"? Have you encountered this before? The goal is no pop ups.

 

[PHV]

X.DisplayAlerts = False
W.Save
W.Close
X.Quit
Set W = Nothing
Set X = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886