从命令行使用 VBScript 从 Excel 外部运行 Excel 宏 [英] Run Excel Macro from Outside Excel Using VBScript From Command Line

问题描述

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

I'm trying to run an Excel macro from outside of the Excel file. I'm currently using a ".vbs" file run from the command line, but it keeps telling me the macro can't be found. Here is the script I'm trying to use

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:UsersusernameDesktop" & "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".

I tried the solutions located at Is it possible to run a macro in Excel from external command? to get this far (and modified of course) but it didn't seem to work. I keep getting the error `Microsoft Office Excel: The macro 'Macro.TestMacro' cannot be found.

Excel 2003.

Excel 2003.

推荐答案

好吧，其实很简单.假设您的宏在一个模块中，而不是在其中一张工作表中，您可以使用:

Ok, it's actually simple. Assuming that your macro is in a module,not in one of the sheets, you use:

  objExcel.Application.Run "test.xls!dog" 
  'notice the format of 'workbook name'!macro

对于带空格的文件名，用引号将文件名括起来.

For a filename with spaces, encase the filename with quotes.

如果您已将宏放在工作表下，例如工作表 1，则假设工作表 1 拥有该功能，它确实拥有该功能.

If you've placed the macro under a sheet, say sheet1, just assume sheet1 owns the function, which it does.

    objExcel.Application.Run "'test 2.xls'!sheet1.dog"

注意:您不需要一直使用的 macro.testfunction 符号.

Notice: You don't need the macro.testfunction notation you've been using.

这篇关于从命令行使用 VBScript 从 Excel 外部运行 Excel 宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！