从外部Excel使用VBScript从命令行运行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
这里是我试图访问的宏: p>
And here is the Macro I'm trying to access:
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。
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
如果将宏放置在工作表下,说sheet1,只要假定sheet1拥有该函数即可。
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.
这篇关于从外部Excel使用VBScript从命令行运行Excel宏的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!