Export modules using automation (VBA, Excel)
Public Sub ExportAll(targetPath as String)
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim VBComp As VBIDE.VBComponent
' Load workbook
Set xlApp = Application
'xlApp.Visible = False
Set xlWb = ActiveWorkbook 'xlApp.Workbooks.Open(sWorkbook)
' Loop through all files (components) in the workbook
For Each VBComp In xlWb.VBProject.VBComponents
' Export the file
If VBComp.Type = vbext_ct_StdModule Then _
VBComp.Export targetPath & VBComp.Name & ".bas"
Next VBComp
End Sub
Two possible issues must be solved to correctly run this code. The first is to allow the code to access the VBA Object model programmatically. This can be done by correctly setting this from the Trust Center (from Excel Options).
The second is to reference the Microsoft Visual Basic for Applications Extensibility 5.3 object library (from Tools→References) to be able to use the VBIDE object model.
Comments