如何从命令行或批处理文件运行Excel宏?

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宏，在从批处理文件访问文件时需要运行它，但不是每次打开它时(因此不使用open file事件)。是否有从命令行或批处理文件运行宏的方法?我不熟悉这样的命令。

Assume a Windows NT environment.

假设一个Windows NT环境。

10 个解决方案

#1

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。运行“MyMacro”

#2

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_Open event, 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.

这将返回控件到批处理文件中进行其他处理。

#3

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?

#4

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

#5

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

#6

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

#7

@ Robert: I have tried to adapt your code with a relative path, and created a batch file to run the VBS.

我已经尝试用一个相对路径修改你的代码，并创建了一个批处理文件来运行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.

我把“应用程序。退出“因为我的宏正在调用一个userform来处理它。

Cheers

干杯

EDIT

编辑

I have actually worked it out, just in case someone wants to run a userform "alike" a stand alone application:

实际上我已经算出来了，以防有人想要运行一个userform "类似"一个独立的应用程序:

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只被锁定在read中。2 -批处理命令的限制在于(据我所知)它不能调用宏。

I first wrote a macro to launch my userform while hiding the application:

我首先编写了一个宏来启动我的userform，同时隐藏应用程序:

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中包含了“设置为visible”:

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

不管怎样，谢谢你的帮助，如果有人需要的话，我希望这能帮上忙

#8

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

对于不打算运行任何宏的情况，只需将空值集MacroName=放入批处理中。

#9

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

您可以检查Excel是否已经打开。没有必要创建另一个isnof。

   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

#10

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;
    }
}

#1