从命令行使用VBScript从Excel外部运行Excel宏

Question

我正在尝试从Excel文件外部运行Excel宏。我目前正在使用从命令行运行的“.vbs”文件，但它一直告诉我无法找到宏。这是我正在尝试使用的脚本

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("test.xls")

objExcel.Application.Visible = True

objExcel.Workbooks.Add

objExcel.Cells(1, 1).Value = "Test value"

objExcel.Application.Run "Macro.TestMacro()"

objExcel.ActiveWorkbook.Close

objExcel.Application.Quit

WScript.Echo "Finished."

WScript.Quit

这是我正在尝试访问的宏：

Sub TestMacro()

'first set a string which contains the path to the file you want to create.

'this example creates one and stores it in the root directory

MyFile = "C:\Users\username\Desktop\" & "TestResult.txt"

'set and open file for output

fnum = FreeFile()

Open MyFile For Output As fnum

'write project info and then a blank line. Note the comma is required

Write #fnum, "I wrote this"

Write #fnum,

'use Print when you want the string without quotation marks

Print #fnum, "I printed this"

Close #fnum

End Sub

我尝试了位于的解决方案是否可以从外部命令在Excel中运行宏？到目前为止（并修改）但它似乎没有用。我一直收到错误`Microsoft Office Excel：无法找到宏'Macro.TestMacro'。

Answer 1

我试图将@Siddhart的代码改编为运行我的open_form宏的相对路径，但它似乎没有用。这是我的第一次尝试。我的工作解决方案如下。Option ExplicitDim xlApp, xlBookdim fsodim curDirset fso = CreateObject("Scripting.FileSystemObject")curDir = fso.GetAbsolutePathName(".")set fso = nothingSet xlApp = CreateObject("Excel.Application")'~~> Change Path hereSet xlBook = xlApp.Workbooks.Open(curDir & "Excels\CLIENTES.xlsb", 0, true)xlApp.Run "open_form"xlBook.ClosexlApp.QuitSet xlBook = NothingSet xlApp = NothingWScript.Echo "Finished."编辑我实际上已经解决了这个问题，以防万一有人想要运行一个“相似”的用户表单：一个独立的应用程序：我面临的问题：1 - 我不想使用Workbook_Open事件，因为excel被锁定为只读。2 - 批处理命令受限于（据我所知）它无法调用宏的事实。我首先写了一个宏来启动我的userform同时隐藏应用程序：Sub open_form() Application.Visible = False frmAddClient.Show vbModelessEnd Sub然后我创建了一个vbs来启动这个宏（用相对路径做这件事很棘手）：dim fsodim curDirdim WinScriptHostset fso = CreateObject("Scripting.FileSystemObject")curDir = fso.GetAbsolutePathName(".")set fso = nothingSet xlObj = CreateObject("Excel.application")xlObj.Workbooks.Open curDir & "\Excels\CLIENTES.xlsb"xlObj.Run "open_form"我终于做了一个批处理文件来执行VBS ......@echo offpushd %~dp0cscript Add_Client.vbs请注意，我还在我的网站中包含了“设置回可见” Userform_QueryClose：Private Sub cmdClose_Click()Unload MeEnd SubPrivate Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)    ThisWorkbook.Close SaveChanges:=True    Application.Visible = True    Application.QuitEnd Sub无论如何，感谢您的帮助，我希望如果有人需要，这将有所帮助