excel - 用VBS在外部运行Excel宏无效
问题描述
我有一个 excel 宏,可以将某些数据行添加到 excel 工作簿中的某些工作表中。我遇到的问题是,如果我从工作簿中运行宏,它就像一个魅力,但是当我尝试在外部运行它时我就没有那么幸运了。
我正在使用以下 VBS 代码来运行宏,但由于某种原因它没有按预期运行。我可以在资源管理器中看到 excel 文件被打开(到修改的新日期),但里面的工作表没有变化。
Dim WshShell
Set WshShell = CreateObject("WScript.Shell")
' Create an Excel instance
Dim myExcelWorker
Set myExcelWorker = CreateObject("Excel.Application")
' Disable Excel UI elements
myExcelWorker.DisplayAlerts = False
myExcelWorker.AskToUpdateLinks = False
myExcelWorker.AlertBeforeOverwriting = False
myExcelWorker.FeatureInstall = msoFeatureInstallNone
' Tell Excel what the current working directory is
' (otherwise it can't find the files)
Dim strSaveDefaultPath
Dim strPath
strSaveDefaultPath = myExcelWorker.DefaultFilePath
strPath = WshShell.CurrentDirectory
myExcelWorker.DefaultFilePath = strPath
' Open the Workbook specified on the command-line
Dim oWorkBook
Dim strWorkerWB
strWorkerWB = strPath & "\WatchlistMentoring.xlsm"
Set oWorkBook = myExcelWorker.Workbooks.Open(strWorkerWB)
' Build the macro name with the full path to the workbook
Dim strMacroName
strMacroName = "'" & strPath & "\WatchlistMentoring.xlsm!UpdateAllspreads()"
on error resume next
' Run the calculation macro
myExcelWorker.Run strMacroName
if err.number <> 0 Then
' Error occurred - just close it down.
End If
err.clear
on error goto 0
oWorkBook.Save
myExcelWorker.DefaultFilePath = strSaveDefaultPath
' Clean up and shut down
Set oWorkBook = Nothing
' Don’t Quit() Excel if there are other Excel instances
' running, Quit() will shut those down also
if myExcelWorker.Workbooks.Count > 0 Then
myExcelWorker.Quit
End If
Set myExcelWorker = Nothing
Set WshShell = Nothing
显然文件名和宏名本身是正确的,但不确定我是否一起使用它们正确?
解决方案
推荐阅读
- powerbi - 瓷砖,使用rest api在powerbi中将视觉复制为图像
- oracle - PL/SQL:ORA-01031:权限不足 6/1 PL/SQL:SQL 语句被忽略 GRANT
- sql - 使用子查询 Woo Commerce 创建 SQL
- python - 即使在调试之后,Kivymd 应用程序也会在启动时崩溃
- javascript - Sprite 框架作为 div 背景,在 div 旋转时自动缩放错误
- sql-server - t sql 字符串中第一个数字的所有权利
- postgresql - SQL 查询不适用于 PostgreSQL 命令
- android - 在领域中创建了两个表,但保存的数据仅在领域中的第一个表中
- python - 自定义不和谐状态不断收到此错误
- reactjs - React 中的解耦(带钩子)