vba 从命令行使用 VBScript 从 Excel 外部运行 Excel 宏

Run Excel Macro from Outside Excel Using VBScript From Command Line

提问by muttley91

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

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

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

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

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 中运行宏的解决方案？走到这一步（当然还有修改），但它似乎没有用。我不断收到错误“Microsoft Office Excel：找不到宏“Macro.TestMacro”。

EDIT: Excel 2003.

编辑：Excel 2003。

回答by Wolf-Kun

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.

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

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

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

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

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

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

回答by Siddharth Rout

I think you are trying to do this? (TRIED AND TESTED)

我认为你正在尝试这样做？（久经考验）

This code will open the file Test.xls and run the macro TestMacrowhich will in turn write to the text file TestResult.txt

此代码将打开文件 Test.xls 并运行宏TestMacro，该宏将依次写入文本文件TestResult.txt

Option Explicit

Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
'~~> Change Path here
Set xlBook = xlApp.Workbooks.Open("C:\Test.xls", 0, True)
xlApp.Run "TestMacro"
xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

WScript.Echo "Finished."
WScript.Quit

回答by access_granted

Since my related question was removed by a righteous hand after I had killed the whole day searching how to beat the "macro not found or disabled" error, posting here the only syntax that worked for me (application.run didn't, no matter what I tried)

由于在我杀了一整天寻找如何解决“未找到或禁用宏”错误后，我的相关问题已被正义之手删除，因此在此处发布唯一对我有用的语法（application.run 没有，无论如何我试过的）

Set objExcel = CreateObject("Excel.Application")

' Didn't run this way from the Modules
'objExcel.Application.Run "c:\app\Book1.xlsm!Sub1"
' Didn't run this way either from the Sheet
'objExcel.Application.Run "c:\app\Book1.xlsm!Sheet1.Sub1"
' Nor did it run from a named Sheet
'objExcel.Application.Run "c:\app\Book1.xlsm!Named_Sheet.Sub1"

' Only ran like this (from the Module1)

Set objWorkbook = objExcel.Workbooks.Open("c:\app\Book1.xlsm")
objExcel.Run "Sub1"

Excel 2010, Win 7

Excel 2010，Win 7

回答by Axn40

I tried to adapt @Siddhart's code to a relative path to run my open_formmacro, but it didn't seem to work. Here was my first attempt. My working solution is below.

我试图将@Siddhart 的代码调整为运行我的open_form宏的相对路径，但它似乎不起作用。这是我的第一次尝试。我的工作解决方案如下。

Option Explicit

Dim xlApp, xlBook
dim fso
dim curDir
set fso = CreateObject("Scripting.FileSystemObject")
curDir = fso.GetAbsolutePathName(".")
set fso = nothing

Set xlApp = CreateObject("Excel.Application")
'~~> Change Path here
Set xlBook = xlApp.Workbooks.Open(curDir & "Excels\CLIENTES.xlsb", 0, true)
xlApp.Run "open_form"
xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

WScript.Echo "Finished."

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 maryhadalittlelamb

I tried the above methods but I got the "macro cannot be found" error. This is final code that worked!

我尝试了上述方法，但出现“找不到宏”错误。这是最终有效的代码！

Option Explicit

Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

  ' Import Add-Ins
xlApp.Workbooks.Open "C:\<pathOfXlaFile>\MyMacro.xla"
xlApp.AddIns("MyMacro").Installed = True

'
Open Excel workbook
Set xlBook = xlApp.Workbooks.Open("<pathOfXlsFile>\MyExcel.xls", 0, True)

' Run Macro
xlApp.Run "Sheet1.MyMacro"

xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

WScript.Quit

In my case, MyMacro happens to be under Sheet1, thus Sheet1.MyMacro.

就我而言，MyMacro 恰好在 Sheet1 下，因此 Sheet1.MyMacro。

回答by FreeSoftwareServers

I generally store my macros in xlamadd-ins separately from my workbooks so I wanted to open a workbook and then run a macro stored separately.

我通常将我的宏xlam与我的工作簿分开存储在加载项中，所以我想打开一个工作簿，然后运行一个单独存储的宏。

Since this required a VBS Script, I wanted to make it "portable" so I could use it by passing arguments. Here is the final script, which takes 3 arguments.

由于这需要一个 VBS 脚本，我想让它“可移植”，这样我就可以通过传递参数来使用它。这是最终的脚本，它需要 3 个参数。

• Full Path to Workbook
• Macro Name
• [OPTIONAL] Path to separate workbook with Macro

• 工作簿的完整路径
• 宏名称
• [可选] 使用宏分隔工作簿的路径

I tested it like so:

我是这样测试的：

"C:\Temp\runmacro.vbs" "C:\Temp\Book1.xlam" "Hello"

"C:\Temp\runmacro.vbs" "C:\Temp\Book1.xlsx" "Hello" "%AppData%\Microsoft\Excel\XLSTART\Book1.xlam"

runmacro.vbs:

运行宏.vbs：

Set args = Wscript.Arguments

ws = WScript.Arguments.Item(0)
macro = WScript.Arguments.Item(1)
If wscript.arguments.count > 2 Then
 macrowb= WScript.Arguments.Item(2)
End If

LaunchMacro

Sub LaunchMacro() 
  Dim xl
  Dim xlBook  

  Set xl = CreateObject("Excel.application")
  Set xlBook = xl.Workbooks.Open(ws, 0, True)
  If wscript.arguments.count > 2 Then
   Set macrowb= xl.Workbooks.Open(macrowb, 0, True)
  End If
  'xl.Application.Visible = True ' Show Excel Window
  xl.Application.run macro
  'xl.DisplayAlerts = False  ' suppress prompts and alert messages while a macro is running
  'xlBook.saved = True ' suppresses the Save Changes prompt when you close a workbook
  'xl.activewindow.close
  xl.Quit

End Sub 

回答by xarokk

Hi used this thread to get the solution , then i would like to share what i did just in case someone could use it.

嗨使用这个线程来获得解决方案，然后我想分享我所做的以防万一有人可以使用它。

What i wanted was to call a macro that change some cells and erase some rows, but i needed for more than 1500 excels( approximately spent 3 minuts for each file)

我想要的是调用一个宏来更改一些单元格并擦​​除一些行，但我需要超过 1500 个 excel（每个文件大约花费 3 分钟）

Mainly problem: -when calling the macro from vbe , i got the same problem, it was imposible to call the macro from PERSONAL.XLSB, when the script oppened the excel didnt execute personal.xlsb and wasnt any option in the macro window

主要问题：-当从 vbe 调用宏时，我遇到了同样的问题，从 PERSONAL.XLSB 调用宏是不可能的，当脚本打开时，excel 没有执行 personal.xlsb 并且宏窗口中没有任何选项

I solved this by keeping open one excel file with the macro loaded(a.xlsm)(before executing the script)

我通过保持打开一个加载了宏的 excel 文件（a.xlsm）（在执行脚本之前）解决了这个问题

Then i call the macro from the excel oppened by the script

然后我从脚本打开的 excel 中调用宏

    Option Explicit
    Dim xl
    Dim counter

   counter =10



  Do

  counter = counter + 1

  Set xl = GetObject(, "Excel.Application")
  xl.Application.Workbooks.open "C:\pruebas\macroxavi\IA_030-08-026" & counter & ".xlsx"
  xl.Application.Visible = True
  xl.Application.run "'a.xlsm'!eraserow" 
  Set xl = Nothing


  Loop Until counter = 517

  WScript.Echo "Finished."
  WScript.Quit

回答by Eduardo Dennis

If you are trying to run the macro from your personal workbook it might not work as opening an Excel file with a VBScript doesnt automatically open your PERSONAL.XLSB. you will need to do something like this:

如果您尝试从您的个人工作簿运行宏，它可能无法正常工作，因为使用 VBScript 打开 Excel 文件不会自动打开您的 PERSONAL.XLSB。你需要做这样的事情：

Dim oFSO
Dim oShell, oExcel, oFile, oSheet
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oShell = CreateObject("WScript.Shell")
Set oExcel = CreateObject("Excel.Application")
Set wb2 = oExcel.Workbooks.Open("C:\..\PERSONAL.XLSB") 'Specify foldername here

oExcel.DisplayAlerts = False


For Each oFile In oFSO.GetFolder("C:\Location\").Files
    If LCase(oFSO.GetExtensionName(oFile)) = "xlsx" Then
        With oExcel.Workbooks.Open(oFile, 0, True, , , , True, , , , False, , False)



            oExcel.Run wb2.Name & "!modForm"


            For Each oSheet In .Worksheets



                oSheet.SaveAs "C:\test\" & oFile.Name & "." & oSheet.Name & ".txt", 6


            Next
            .Close False, , False
        End With
    End If



Next
oExcel.Quit
oShell.Popup "Conversion complete", 10

So at the beginning of the loop it is opening personals.xlsb and running the macro from there for all the other workbooks. Just thought I should post in here just in case someone runs across this like I did but cant figure out why the macro is still not running.

因此，在循环开始时，它打开personals.xlsb 并从那里为所有其他工作簿运行宏。只是想我应该在这里发帖，以防有人像我一样遇到这个问题，但无法弄清楚为什么宏仍然没有运行。