How to run personal.xlsb macro from vb script-CodePudding

I have been trying to find a way to run personal.xlsb macro (one macro applicable to all excel files) using VB script but I have been getting the error: Cannot run the macro... the macro may not be available in this workbook (despite the macro works perfectly fine if i run it from excel on different excel files).

This is my code:

sPath="H:\msa\Temp\MengKeat\FlukeReport\20220429\CV4T1L2.11"
set oFSO = CreateObject("Scripting.FileSystemObject)
sNewestFile = GetNewestFile(sPath)
if sNewestFile <> "" Then
Wscript.Echo "Newest file is " & sNewestFile 
dFileModDate = oFSO.GetFile(sNewestFile).DateLastModified
if DateDiff("h", dFileModDate, Now) > 24 Then
End if

Else
Wscript.Echo "Directory is empty"
End if

Function GetNewestFile(ByVal sPath)
sNewestFile = Null ' init value
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(sPath)
Set oFiles= oFolder.Files

For Each oFile in oFiles
On Error Resume Next
If IsNull(sNewestFile) Then
sNewestFile = oFile.Path
dPrevDate = oFile.DateLastModified
Elseif dPrevDate < oFile.DateLastModified Then
sNewestFile = oFile.Path
End if
On Error Goto 0
Next

If IsNull(sNewestFile) Then sNewestFile = ""

GetNewestFile = sNewestFile
ExcelFilePath = sNewestFile
MacroPath = "C:\Users\gsumarlin\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB!Insert_Testing"
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = "False"
ExcelApp.DisplayAlerts= False
Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)
ExcelApp.Run MacroPath
wb.Save
ExcelApp.DisplayAlerts = True
wb.Close
ExcelApp.Quit

CodePudding user response：

When Excel is opened via automation, Personal.xlsb and add-ins are not automatically loaded. You will need to open personal.xlsb before you can run the macro.

ExcelFilePath = sNewestFile
MacroPath = "C:\Users\gsumarlin\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB"
MacroName = "PERSONAL.XLSB!Insert_Testing"
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = "False"
ExcelApp.DisplayAlerts= False
Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)
ExcelApp.Workbooks.Open MacroPath               '<< open the file first
ExcelApp.Run MacroName 
wb.Save
ExcelApp.DisplayAlerts = True
wb.Close
ExcelApp.Quit