使用VBS的Excel宏循环 [英] Excel macro loop using VBS

问题描述

我想运行一个名为 MyMacro 的VBA宏，对于许多Excel文件，该宏都保存为 MyMacro.bas .我在下面有VBS代码，但是它没有按照我想要的做.如果有人可以看一下，我将不胜感激.

I would like to run a VBA macro named MyMacro, which is saved as MyMacro.bas for many excel files. I have the VBS code below, but it is not doing what I want. I would really appreciate if somebody could take a look at it.

我正在使用Excel2013.文件另存为 .xls .

I am using Excel 2013. The files are saved as .xls.

谢谢.

Const sRootFolder = "C:\Documents"
Const sExportedModule = "C:\Documents\MyMacro.bas"
Const sMacroName = "MyMacro"

Dim oFSO, oFDR, oFile ' File and Folder variables
Dim oExcel, oWB ' Excel variables (Application and Workbook)

Start
'------------------------------
Sub Start()
    Initialize
    ProcessFilesInFolder sRootFolder
    Finish
End Sub
'------------------------------
Sub ProcessFilesInFolder(sFolder)
    ' Process the files in this folder
    For Each oFile In oFSO.GetFolder(sFolder).Files
        If IsExcelFile(oFile) Then ProcessExcelFile oFile.Path
    Next
End Sub
'------------------------------
Sub Initialize()
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oExcel = CreateObject("Excel.Application")
End Sub
'------------------------------
Sub Finish()
    oExcel.Quit
    Set oExcel = Nothing
    Set oFSO = Nothing
End Sub
'------------------------------
Function IsExcelFile(oFile)
    IsExcelFile = (InStr(1, oFSO.GetExtensionName(oFile), "xls", vbTextCompare) > 0) And (Left(oFile.Name, 1) <> "~")
End Function
'------------------------------
Sub ProcessExcelFile(sFileName)
    On Error Resume Next
    wscript.echo "Processing file: " & sFileName ' Comment this unless using cscript in command prompt
    Set oWB = oExcel.Workbooks.Open(sFileName)
    oWB.VBProject.VBComponents.Import sExportedModule
    oExcel.Run sMacroName
    oWB.Save
    oWB.Close
    Set oWB = Nothing
End Sub
'------------------------------

以下是适用于单个文件的vbs代码:

Here is a vbs code for a single file which works:

Option Explicit

ExcelMacroExample
Sub ExcelMacroExample() 
  Dim xlApp 
  Dim xlBook 
  Dim objWorkbook  

  Set xlApp = CreateObject("Excel.Application") 
  Set xlBook = xlApp.Workbooks.Open("C:\Documents\test.xls", 0, True) 
  Set objWorkbook = xlApp.Workbooks.Open("C:\Documents\test.xls")  

  xlApp.Run "MyMacro"
  xlApp.Quit 

  Set xlBook = Nothing 
  Set xlApp = Nothing 
End Sub

推荐答案

我终于开始工作了:

Const sRootFolder = "C:\Documents"
Const sExportedModule = "C:\Documents\MyMacro.bas"
Const sMacroName = "Trip"

Dim oFSO, oFile ' File and Folder variables
Dim xlApp, xlBook, objWorkbook 

Start

Sub Start()
    Initialize
    ProcessFilesInFolder sRootFolder
    Finish
End Sub

Sub ProcessFilesInFolder(sFolder)
    ' Process the files in this folder
    For Each oFile In oFSO.GetFolder(sFolder).Files
        If IsExcelFile(oFile) Then ProcessExcelFile oFile.Path
    Next
End Sub

Sub Initialize()
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set xlApp = CreateObject("Excel.Application")   
End Sub

Sub Finish()
    xlApp.Quit
    Set xlBook = Nothing 
    Set xlApp = Nothing    
    Set oFSO = Nothing
End Sub

Function IsExcelFile(oFile)
    IsExcelFile = (InStr(1, oFSO.GetExtensionName(oFile), "xls", vbTextCompare) > 0) And (Left(oFile.Name, 1) <> "~")
End Function

Sub ProcessExcelFile(sFileName)
    wscript.echo "Processing file: " & sFileName ' Comment this unless using cscript in command prompt    
    Set xlBook = xlApp.Workbooks.Open(sFileName, 0, True) 
    Set objWorkbook = xlApp.Workbooks.Open(sFileName)     
    objWorkbook.VBProject.VBComponents.Import sExportedModule
    xlApp.Run sMacroName
End Sub

此外，请确保已启用对VBA项目对象模型的信任访问.我当然可能是错的，但这里的改变游戏规则似乎就是这样:

Also, make sure that Trust access to the VBA project object model enabled. I certainly may be wrong, but the game changer here seems to be this piece:

Set objWorkbook = xlApp.Workbooks.Open(sFileName)

这篇关于使用VBS的Excel宏循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！