查看：103
发布时间：2021/5/5 20:47:56excel
vbscript
vba
本文介绍了使用VBS的Excel宏循环的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述我想运行一个名为 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屋！查看全文
}