如何以编程方式添加excel 2010宏 [英] How to add excel 2010 macro programmatically
问题描述
是否有任何方法以编程方式将宏添加到Excel文件?
我有太多的Excel文件,我想向它们添加一个宏。
手动添加(手动)似乎不可能。
我需要创建一个工具来做到这一点。
Is there any method to add macro to Excel file programmatically ?
I have too many Excel files I want to add a macro to them.
Adding manually (by hand) seems impossible.
I need to create a tool to do this.
推荐答案
是的,你可以这样编程,你可以访问VB集成开发环境通过代码。以下网站非常适合学习VBIDE,我已经用它们来组合这个代码。如果运行 WorkbookModuleImport
,将弹出两个打开的对话框,首先要求工作簿导入模块,第二个选择要导入的模块。
Yes, you can do this programatically, you can access the VB Integrated Development Environment through code. The following website are excellent for learning about the VBIDE, I've used them to put together this code. If you run WorkbookModuleImport
two open dialog boxes will pop up, the first asking for workbooks to import the modules into, the second to select the modules for importing.
Sub WorkbookModuleImport()
Dim ii As Integer, vFileNames As Variant, vModules As Variant
'We'll use the Application.GetOpenFilename to get a list of all the excel workbooks we want to import into
vFileNames = Application.GetOpenFilename(",*.xls;*.xlsx;*.xlsm", , "Select Workbooks to Import Modules To", , True)
'If the result is not an array it means the cancel button has been pressed
If Not IsArray(vFileNames) Then Exit Sub
'Use the same method to get all the modules/classes/forms to input
vModules = Application.GetOpenFilename(",*.cls, *.bas, *.frm", , "Select Modules/Forms/Class Modules to Import", , True)
If Not IsArray(vModules) Then Exit Sub
'Now loop through all the workbooks to import the modules
For ii = LBound(vFileNames) To UBound(vFileNames)
Call ImportModules(VBA.CStr(vFileNames(ii)), vModules)
Next ii
End Sub
Public Sub ImportModules(sWorkbookName As String, vModules As Variant)
Dim cmpComponents As VBIDE.VBComponents, ii As Integer
Dim wkbTarget As Excel.Workbook
'We need to open the workbook in order to be able to import the code module
Set wkbTarget = Workbooks.Open(sWorkbookName)
'If the project is protected with a password we can't import so just set tell us in the immediate window
If wkbTarget.VBProject.Protection = 1 Then
'Give a message
Debug.Print wkbTarget.Name & " has a protected project, cannot import module"
GoTo Cancelline
End If
'This is where we set the reference to the components of the Visual Basic project
Set cmpComponents = wkbTarget.VBProject.VBComponents
'Loop through all the modules to import
For ii = LBound(vModules) To UBound(vModules)
cmpComponents.Import vModules(ii)
Next ii
Cancelline:
'If it's in Excel 2007+ format but doesn't already have macros, we'll have to save it as a macro workbook
If wkbTarget.FileFormat = xlOpenXMLWorkbook Then
wkbTarget.SaveAs wkbTarget.Name, xlOpenXMLWorkbookMacroEnabled
wkbTarget.Close SaveChanges:=False
Else
'Otherwise, just save the workbook and close it
wkbTarget.Close SaveChanges:=True
End If
'I don't trust excel, so set the workbook object to nothing
Set wkbTarget = Nothing
End Sub
这些网页是伟大的参考资料:
http://www.cpearson.com/excel/vbe.aspx 和
http://www.rondebruin.nl/vbaimportexport.htm 。我以罗恩为起点。
These webpages are great references: http://www.cpearson.com/excel/vbe.aspx and http://www.rondebruin.nl/vbaimportexport.htm. I used Ron's as a starting point.
这篇关于如何以编程方式添加excel 2010宏的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!