/
ExcelMacroRan.vbs
47 lines (42 loc) · 1.38 KB
/
ExcelMacroRan.vbs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
Function excel_macro_ran(ByVal fileName, ByVal macroName)
'Running excel VBA
'
'Parameters
'----------
'fileName : String
' Excel file name
'macroName : String
' will run function name or sub name the macro
'
'Return
'----------
'boolen
' success(True) , failure(False)
excel_macro_ran = False
'start up excel application
Dim excelApp :Set excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
'open file
Dim excelWorkbook :Set excelWorkbook = excelApp.Workbooks.Open(fileName)
On Error Resume Next
'start VBA
WScript.Echo "Ran " + macroName
Call excelApp.Run(macroName)
If Err.Number <> 0 Then
'We will leave the Excel without closing for the review of VBA.
WScript.Echo "Error : " + macroName
Else
'Exit the Excel application.
Call excelWorkbook.Save()
Call excelWorkbook.Close(False)
excelApp.Workbooks.Close
excelApp.Quit
WScript.Echo fileName + " of " + macroName + " was executed."
excel_macro_ran = True
End If
End Function
'test code
'pathLen = len(wscript.scriptfullname) - len(wscript.scriptname)
'parPath = left(wscript.scriptfullname,pathLen)
'Call excel_macro_ran(parPath + "test.xlsm", "main")
'Call excel_macro_ran(parPath + "test.xlsm", "error")