-1

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
braX
  • 11,506
  • 5
  • 20
  • 33
Gracella Q Sumarlin
  • 35
  • 6
  • The closing quotation mark in the second line of your code s missing: `set oFSO = CreateObject("Scripting.FileSystemObject)` – Stefan Wuebbe May 22 '22 at 16:41

1 Answers1

1

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
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Hi thank you so much for your answer! But i got error on this line code aft following ur instruction: ExcelApp.Workbooks.Open MacroPath -> error: cldnt find the file, is it possible it was moved, renamed or deleted? (Despite existing thru Excel file directory) Thank u once agn! – Gracella Q Sumarlin May 23 '22 at 00:22
  • You adjusted the path to remove the `!Insert_Testing` at the end? – Tim Williams May 23 '22 at 00:33
  • Nevermind I followed this comment and got the answer thank you so much! – Gracella Q Sumarlin May 23 '22 at 00:35