vba 从命令行或批处理文件运行 Excel 宏的方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2050505/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Way to run Excel macros from command line or batch file?
提问by Polymeron
I have an Excel VBA macro which I need to run when accessing the file from a batch file, but not every time I open it (hence not using the open file event). Is there a way to run the macro from the command line or batch file? I'm not familiar with such a command.
我有一个 Excel VBA 宏，在从批处理文件访问文件时需要运行它，但不是每次打开它时都需要运行（因此不使用打开文件事件）。有没有办法从命令行或批处理文件运行宏？我不熟悉这样的命令。
Assume a Windows NT environment.
假设一个 Windows NT 环境。
回答by Robert Mearns
You can launch Excel, open the workbook and run the macro from a VBScript file.
您可以启动 Excel，打开工作簿并从 VBScript 文件运行宏。
Copy the code below into Notepad.
将下面的代码复制到记事本中。
Update the 'MyWorkbook.xls' and 'MyMacro' parameters.
更新“ MyWorkbook.xls”和“ MyMacro”参数。
Save it with a vbs extension and run it.
使用 vbs 扩展名保存并运行它。
Option Explicit
On Error Resume Next
ExcelMacroExample
Sub ExcelMacroExample()
Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\MyWorkbook.xls", 0, True)
xlApp.Run "MyMacro"
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
The key line that runs the macro is:
运行宏的关键行是：
xlApp.Run "MyMacro"
xlApp.Run "MyMacro"
回答by bvukas
The simplest way to do it is to:
最简单的方法是：
1) Start Excel from your batch file to open the workbook containing your macro:
1) 从您的批处理文件启动 Excel 以打开包含您的宏的工作簿：
EXCEL.EXE /e "c:\YourWorkbook.xls"
2) Call your macro from the workbook's Workbook_Openevent, such as:
2）从工作簿的Workbook_Open事件中调用您的宏，例如：
Private Sub Workbook_Open()
Call MyMacro1 ' Call your macro
ActiveWorkbook.Save ' Save the current workbook, bypassing the prompt
Application.Quit ' Quit Excel
End Sub
This will now return the control to your batch file to do other processing.
现在，这会将控制权返回给您的批处理文件以进行其他处理。
回答by omegastripes
The method shown below allows to run defined Excel macro from batch file, it uses environment variable to pass macro name from batch to Excel.
下面显示的方法允许从批处理文件运行定义的 Excel 宏，它使用环境变量将宏名称从批处理传递到 Excel。
Put this code to the batch file (use your paths to EXCEL.EXEand to the workbook):
将此代码放入批处理文件（使用指向EXCEL.EXE和指向工作簿的路径）：
Set MacroName=MyMacro
"C:\Program Files\Microsoft Office\Office15\EXCEL.EXE" "C:\MyWorkbook.xlsm"
Put this code to Excel VBA ThisWorkBook Object:
将此代码放入 Excel VBA ThisWorkBook 对象：
Private Sub Workbook_Open()
Dim strMacroName As String
strMacroName = CreateObject("WScript.Shell").Environment("process").Item("MacroName")
If strMacroName <> "" Then Run strMacroName
End Sub
And put your code to Excel VBA Module, like as follows:
并将您的代码放入 Excel VBA 模块，如下所示：
Sub MyMacro()
MsgBox "MyMacro is running..."
End Sub
Launch the batch file and get the result:
启动批处理文件并得到结果：
For the case when you don't intend to run any macro just put empty value Set MacroName=to the batch.
对于不打算运行任何宏的情况，只需将空值放入Set MacroName=批处理即可。
回答by Fink
you could write a vbscript to create an instance of excel via the createobject() method, then open the workbook and run the macro. You could either call the vbscript directly, or call the vbscript from a batch file.
您可以编写一个 vbscript 通过 createobject() 方法创建一个 excel 实例，然后打开工作簿并运行宏。您可以直接调用 vbscript，也可以从批处理文件调用 vbscript。
Here is a resource I just stumbled accross: http://www.codeguru.com/forum/showthread.php?t=376401
这是我刚刚偶然发现的一个资源：http: //www.codeguru.com/forum/showthread.php?t =376401
回答by Brian Hoffman
I have always tested the number of open workbooks in Workbook_Open(). If it is 1, then the workbook was opened by the command line (or the user closed all the workbooks, then opened this one).
我一直在 Workbook_Open() 中测试打开的工作簿的数量。如果为 1，则该工作簿是通过命令行打开的（或者用户关闭了所有工作簿，然后打开了这个）。
If Workbooks.Count = 1 Then
' execute the macro or call another procedure - I always do the latter
PublishReport
ThisWorkbook.Save
Application.Quit
End If
回答by Axn40
@ Robert: I have tried to adapt your code with a relative path, and created a batch file to run the VBS.
@ Robert：我尝试使用相对路径调整您的代码，并创建了一个批处理文件来运行 VBS。
The VBS starts and closes but doesn't launch the macro... Any idea of where the issue could be?
VBS 启动和关闭但不启动宏...知道问题出在哪里吗？
Option Explicit
On Error Resume Next
ExcelMacroExample
Sub ExcelMacroExample()
Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Application")
Set objFSO = CreateObject("Scripting.FileSystemObject")
strFilePath = objFSO.GetAbsolutePathName(".")
Set xlBook = xlApp.Workbooks.Open(strFilePath, "Excels\CLIENTES.xlsb") , 0, True)
xlApp.Run "open_form"
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
I removed the "Application.Quit" because my macro is calling a userform taking care of it.
我删除了“Application.Quit”，因为我的宏正在调用一个用户表单来处理它。
Cheers
干杯
EDIT
编辑
I have actually worked it out, just in case someone wants to run a userform "alike" a stand alone application:
我实际上已经解决了，以防万一有人想运行一个“类似”的用户表单一个独立的应用程序：
Issues I was facing:
我面临的问题：
1 - I did not want to use the Workbook_Open Event as the excel is locked in read only. 2 - The batch command is limited that the fact that (to my knowledge) it cannot call the macro.
1 - 我不想使用 Workbook_Open 事件，因为 excel 被锁定为只读。2 - 批处理命令受限于（据我所知）它不能调用宏的事实。
I first wrote a macro to launch my userform while hiding the application:
我首先编写了一个宏来在隐藏应用程序的同时启动我的用户表单：
Sub open_form()
Application.Visible = False
frmAddClient.Show vbModeless
End Sub
I then created a vbs to launch this macro (doing it with a relative path has been tricky):
然后我创建了一个 vbs 来启动这个宏（用相对路径来做这件事很棘手）：
dim fso
dim curDir
dim WinScriptHost
set fso = CreateObject("Scripting.FileSystemObject")
curDir = fso.GetAbsolutePathName(".")
set fso = nothing
Set xlObj = CreateObject("Excel.application")
xlObj.Workbooks.Open curDir & "\Excels\CLIENTES.xlsb"
xlObj.Run "open_form"
And I finally did a batch file to execute the VBS...
我终于做了一个批处理文件来执行VBS ...
@echo off
pushd %~dp0
cscript Add_Client.vbs
Note that I have also included the "Set back to visible" in my Userform_QueryClose:
请注意，我还在我的Userform_QueryClose:
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
ThisWorkbook.Close SaveChanges:=True
Application.Visible = True
Application.Quit
End Sub
Anyway, thanks for your help, and I hope this will help if someone needs it
无论如何，感谢您的帮助，如果有人需要，我希望这会有所帮助
回答by Chris Chambers
If you're more comfortable working inside Excel/VBA, use the open event and test the environment: either have a signal file, a registry entry or an environment variable that controls what the open event does.
如果您更习惯在 Excel/VBA 中工作，请使用 open 事件并测试环境：要么拥有一个信号文件、一个注册表项或一个控制 open 事件作用的环境变量。
You can create the file/setting outside and test inside (use GetEnviromentVariable for env-vars) and test easily. I've written VBScript but the similarities to VBA cause me more angst than ease..
您可以在外部创建文件/设置并在内部进行测试（对 env-vars 使用 GetEnviromentVariable）并轻松测试。我写过 VBScript 但与 VBA 的相似之处让我更焦虑而不是轻松。
[more]
[更多的]
As I understand the problem, you want to use a spreadsheet normally most/some of the time yet have it run in batch and do something extra/different. You can open the sheet from the excel.exe command line but you can't control what it does unless it knows where it is. Using an environment variable is relatively simple and makes testing the spreadsheet easy.
据我了解这个问题，您希望在大多数/某些时间通常使用电子表格，但要批量运行它并做一些额外的/不同的事情。您可以从 excel.exe 命令行打开工作表，但除非它知道它在哪里，否则您无法控制它的作用。使用环境变量相对简单，并且可以轻松测试电子表格。
To clarify, use the function below to examine the environment. In a module declare:
为了澄清，请使用下面的函数来检查环境。在模块中声明：
Private Declare Function GetEnvVar Lib "kernel32" Alias "GetEnvironmentVariableA" _
(ByVal lpName As String, ByVal lpBuffer As String, ByVal nSize As Long) As Long
Function GetEnvironmentVariable(var As String) As String
Dim numChars As Long
GetEnvironmentVariable = String(255, " ")
numChars = GetEnvVar(var, GetEnvironmentVariable, 255)
End Function
In the Workbook open event (as others):
在工作簿打开事件中（和其他人一样）：
Private Sub Workbook_Open()
If GetEnvironmentVariable("InBatch") = "TRUE" Then
Debug.Print "Batch"
Else
Debug.Print "Normal"
End If
End Sub
Add in active code as applicable. In the batch file, use
根据需要添加活动代码。在批处理文件中，使用
set InBatch=TRUE
回答by laybmw
Instead of directly comparing the strings (VB won't find them equal since GetEnvironmentVariable returns a string of length 255) write this:
而不是直接比较字符串（VB 不会发现它们相等，因为 GetEnvironmentVariable 返回一个长度为 255 的字符串）写这个：
Private Sub Workbook_Open()
If InStr(1, GetEnvironmentVariable("InBatch"), "TRUE", vbTextCompare) Then
Debug.Print "Batch"
Call Macro
Else
Debug.Print "Normal"
End If
End Sub
回答by t3dodson
I'm partial to C#. I ran the following using linqpad. But it could just as easily be compiled with csc and ran through the called from the command line.
我偏爱 C#。我使用 linqpad 运行了以下命令。但是它可以很容易地用 csc 编译并运行从命令行调用。
Don't forget to add excel packages to namespace.
不要忘记将 excel 包添加到命名空间。
void Main()
{
var oExcelApp = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
try{
var WB = oExcelApp.ActiveWorkbook;
var WS = (Worksheet)WB.ActiveSheet;
((string)((Range)WS.Cells[1,1]).Value).Dump("Cell Value"); //cel A1 val
oExcelApp.Run("test_macro_name").Dump("macro");
}
finally{
if(oExcelApp != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcelApp);
oExcelApp = null;
}
}
回答by Adam Law
You can check if Excel is already open. There is no need to create another isntance
您可以检查 Excel 是否已经打开。没有必要创建另一个实例
If CheckAppOpen("excel.application") Then
'MsgBox "App Loaded"
Set xlApp = GetObject(, "excel.Application")
Else
' MsgBox "App Not Loaded"
Set wrdApp = CreateObject(,"excel.Application")
End If
