blocks|key|1145887|text|您可以编写一个vbscript来通过createobject()方法创建一个excel实例，然后打开工作簿并运行宏。可以直接调用vbscript，也可以从批处理文件中调用vbscript。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1145888|这是我刚刚偶然发现的一个资源：http://www.codeguru.com/forum/showthread.php?t=376401|offset|length|1145889|entityMap|0|LINK|mutability|MUTABLE|url|http://www.codeguru.com/forum/showthread.php?t=376401^0|0|F|1H|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

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.

Here is a resource I just stumbled accross:
<a href="http://www.codeguru.com/forum/showthread.php?t=376401" rel="nofollow noreferrer">http://www.codeguru.com/forum/showthread.php?t=376401</a>

blocks|key|1361008|text|如果您更喜欢在Excel/VBA中工作，请使用open事件并测试环境:有一个信号文件、一个注册表项或一个控制open事件功能的环境变量。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1361009|您可以在外部创建文件/设置并在内部进行测试(对env-vars使用GetEnviromentVariable+)，并且可以轻松地进行测试。我写过VBScript，但与VBA的相似之处让我更多的是焦虑而不是轻松。|1361010|更多|1361011|据我所知，您希望在大多数/某些情况下使用电子表格，但要让它批量运行，并执行一些额外/不同的操作。您可以从excel.exe命令行打开工作表，但除非它知道它所在的位置，否则您无法控制它所做的事情。使用环境变量相对简单，并且使测试电子表格变得容易。|1361012|为了清楚起见，请使用下面的函数来检查环境。在模块中声明：|1361013|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|code-block|syntax|javascript|1361014|在工作簿打开事件中(与其他事件一样)：|1361015|Private+Sub+Workbook_Open()
++++If+GetEnvironmentVariable("InBatch")+=+"TRUE"+Then
++++++++Debug.Print+"Batch"
++++Else
++++++++Debug.Print+"Normal"
++++End+If
End+Sub|1361016|根据需要添加活动代码。在批处理文件中，使用|1361017|set+InBatch=TRUE|1361018|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|11|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|12|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|13|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|L|7|15|8|@]|9|@]|A|$M|N]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|V|5|L|7|17|8|@]|9|@]|A|$M|N]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

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.

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..

[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.

To clarify, use the function below to examine the environment. In a module declare:

<pre><code>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
</code></pre>

In the Workbook open event (as others):

<pre><code>Private Sub Workbook_Open()
 If GetEnvironmentVariable("InBatch") = "TRUE" Then
 Debug.Print "Batch"
 Else
 Debug.Print "Normal"
 End If
End Sub
</code></pre>

Add in active code as applicable. In the batch file, use

<pre><code>set InBatch=TRUE
</code></pre>

blocks|key|1146054|text|最简单的方法是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1146055|1)从批处理文件启动Excel，打开包含宏的工作簿：|1146056|EXCEL.EXE+/e+"c:\YourWorkbook.xls"|code-block|syntax|javascript|1146057|2)从工作簿的Workbook_Open事件调用宏，例如：|offset|length|style|CODE|1146058|Private+Sub+Workbook_Open()
++++Call+MyMacro1++++++++++'+Call+your+macro
++++ActiveWorkbook.Save++++'+Save+the+current+workbook,+bypassing+the+prompt
++++Application.Quit+++++++'+Quit+Excel
End+Sub|1146059|现在，这会将控件返回到批处理文件以进行其他处理。|1146060|entityMap^0|0|0|0|7|D|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|X|8|@$K|Y|L|Z|M|N]]|9|@]|A|$]]|$1|O|3|P|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

The simplest way to do it is to:

1) Start Excel from your batch file to open the workbook containing your macro:

<pre><code>EXCEL.EXE /e "c:\YourWorkbook.xls"
</code></pre>

2) Call your macro from the workbook's <code>Workbook_Open</code> event, such as:

<pre><code>Private Sub Workbook_Open()
 Call MyMacro1 ' Call your macro
 ActiveWorkbook.Save ' Save the current workbook, bypassing the prompt
 Application.Quit ' Quit Excel
End Sub
</code></pre>

This will now return the control to your batch file to do other processing.

blocks|key|1146093|text|不是直接比较字符串(VB不会发现它们相等，因为GetEnvironmentVariable返回一个长度为255的字符串)，而是这样写：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1146094|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+|code-block|syntax|javascript|1146095|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Instead of directly comparing the strings (VB won't find them equal since GetEnvironmentVariable returns a string of length 255) write this:

<pre><code>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 
</code></pre>

blocks|key|1146162|text|我总是在Workbook_Open()中测试打开的工作簿的数量。如果为1，则该工作簿是通过命令行打开的(或者用户关闭了所有工作簿，然后打开了此工作簿)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1146163|If+Workbooks.Count+=+1+Then

++++'+execute+the+macro+or+call+another+procedure+-+I+always+do+the+latter++
++++PublishReport

++++ThisWorkbook.Save

++++Application.Quit

End+If|code-block|syntax|javascript|1146164|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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). 

<pre><code>If Workbooks.Count = 1 Then

 ' execute the macro or call another procedure - I always do the latter 
 PublishReport

 ThisWorkbook.Save

 Application.Quit

End If
</code></pre>

blocks|key|1146235|text|您可以检查Excel是否已打开。不需要创建另一个isntance|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1146236|+++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|code-block|syntax|javascript|1146237|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can check if Excel is already open. There is no need to create another isntance

<pre><code> 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
</code></pre>

blocks|key|1146274|text|我偏爱C#。我使用linqpad运行以下命令。但它可以很容易地用csc编译，并通过从命令行调用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1146275|不要忘记将excel包添加到名称空间中。|1146276|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;
++++}
}|code-block|syntax|javascript|1146277|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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.

Don't forget to add excel packages to namespace.

<pre><code>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;
 }
}
</code></pre>

blocks|key|1361333|text|下面显示的方法允许从批处理文件中运行定义的Excel宏，它使用环境变量将宏名从批处理传递到Excel。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1361334|将此代码放入批处理文件(使用EXCEL.EXE和工作簿的路径)：|offset|length|style|CODE|1361335|Set+MacroName=MyMacro
"C:\Program+Files\Microsoft+Office\Office15\EXCEL.EXE"+"C:\MyWorkbook.xlsm"|code-block|syntax|javascript|1361336|将此代码放入Excel+VBA+ThisWorkBook对象：|1361337|Private+Sub+Workbook_Open()
++++Dim+strMacroName+As+String
++++strMacroName+=+CreateObject("WScript.Shell").Environment("process").Item("MacroName")
++++If+strMacroName+<>+""+Then+Run+strMacroName
End+Sub|1361338|并将代码放到Excel+VBA模块中，如下所示：|1361339|Sub+MyMacro()
++++MsgBox+"MyMacro+is+running..."
End+Sub|1361340|启动批处理文件并获得结果：|1361341|​|1361342|📷|atomic|1361343|1361344|对于不打算运行任何宏的情况，只需将空值Set+MacroName=放入批处理即可。|1361345|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/8750c06f897d262678ac1798ea039969.png|imageAlt^0|0|E|9|0|0|0|0|0|0|0|0|0|1|0|0|0|J|E|0^^$0|@$1|2|3|4|5|6|7|1D|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1E|8|@$D|1F|E|1G|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|1H|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|1I|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|1J|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|1K|8|@]|9|@]|A|$]]|$1|S|3|T|5|J|7|1L|8|@]|9|@]|A|$K|L]]|$1|U|3|V|5|6|7|1M|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1N|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|10|7|1O|8|@]|9|@$D|1P|E|1Q|1|1R]]|A|$]]|$1|11|3|X|5|6|7|1S|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|1T|8|@$D|1U|E|1V|F|G]]|9|@]|A|$]]|$1|14|3|-4|5|6|7|1W|8|@]|9|@]|A|$]]]|15|$16|$5|17|18|19|A|$1A|1B|1C|-4]]]]

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.

Put this code to the batch file (use your paths to <code>EXCEL.EXE</code> and to the workbook):

<pre><code>Set MacroName=MyMacro
"C:\Program Files\Microsoft Office\Office15\EXCEL.EXE" "C:\MyWorkbook.xlsm"
</code></pre>

Put this code to Excel VBA ThisWorkBook Object:

<pre><code>Private Sub Workbook_Open()
 Dim strMacroName As String
 strMacroName = CreateObject("WScript.Shell").Environment("process").Item("MacroName")
 If strMacroName &lt;&gt; "" Then Run strMacroName
End Sub
</code></pre>

And put your code to Excel VBA Module, like as follows:

<pre><code>Sub MyMacro()
 MsgBox "MyMacro is running..."
End Sub
</code></pre>

Launch the batch file and get the result:

<a href="https://i.stack.imgur.com/qMa75.png" rel="noreferrer"><img src="https://i.stack.imgur.com/qMa75.png" alt="macro&#39;s dialog"></a>

For the case when you don't intend to run any macro just put empty value <code>Set MacroName=</code> to the batch.

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.

Assume a Windows NT environment.

Way to run Excel macros from command line or batch file?

Windows

我有一个Excel VBA宏，在从批处理文件访问文件时需要运行它，但不是每次打开它时都需要运行它(因此不使用open file事件)。有没有办法从命令行或批处理文件运行宏？我不熟悉这样的命令。假设是Windows NT环境。

问如何从命令行或批处理文件运行Excel宏？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从命令行或批处理文件运行Excel宏？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从命令行或批处理文件运行Excel宏？
EN