I wrote a set of macros that modify a text file and import the result into
Excel. Once in Excel, more formatting changes are made, and finally I want
to copy three modules into the target's VBE. These modules provide for a
custom outline expansion, and the code for these works once in Excel.
I am having trouble with the copying. I took my method from Chip Pearson's
"Programming to the VBE" website, but I've noticed that Word seems to use the
VBE differently than Excel. I either get a message that says "Subscript out
of Range" or it fails to copy the modules completely.
Here is the code:
Sub addCollapse(xlApp)
Dim fname As String
Dim fso
Dim VBCodeMod As CodeModule
Dim VBComp As VBComponent
Dim LineNum As Long
Application.Activate
' Search for "collapse" modules and transfer code to target
For Each Module In Application.VBE.ActiveVBProject.VBComponents
If Module.Type = 1 And (Module.Name = "copy_collapse_functions" Or _
Module.Name = "copy_collapse_main" Or _
Module.Name = "copy_collapse_hide") Then
' Export module to text file
fname = ActiveDocument.Path & "\" & Module.Name & ".txt"
Module.Export pthVisual & Module.Name & ".txt"
Module.Export fname
' Import module into spreadsheet
xlApp.ActiveWorkbook.VBProject.VBComponents.import fname
' Delete text file
Set fso = CreateObject("scripting.filesystemobject")
fso.deletefile fname
End If
Next
' Rename modules
xlApp.ActiveWorkbook.VBProject.VBComponents("copy_collapse_main").Name =
"collapse_main"
xlApp.ActiveWorkbook.VBProject.VBComponents("copy_collapse_functions").Name =
"collapse_functions"
xlApp.ActiveWorkbook.VBProject.VBComponents("copy_collapse_hide").Name =
"collapse_hide"
xlApp.Application.Run "collapse"
End Function
In the code, xlApp refers to the Excel Application. If anyone could help me
find the error or ambiguity, please let me know. Thank you
Pflugs