我一直在嘗試找到一種方法來使用 VB 腳本運行 personal.xlsb 宏（一個適用于所有 excel 檔案的宏），但我收到錯誤訊息：無法運行宏...此作業簿中可能沒有宏（盡管如果我在不同的 excel 檔案上從 excel 運行宏，宏作業得非常好）。
這是我的代碼：
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
uj5u.com熱心網友回復：
通過自動化打開 Excel 時，不會自動加載 Personal.xlsb 和加載項。您需要先打開personal.xlsb，然后才能運行宏。
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/479843.html