I posted this to the Word programming newsgroup, too, in hopes of getting
someone who knows both applications well enough to answer.

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. I have also found that
if I step through the code line by line, it works correctly. I can't figure
out why it would work using F8 and not when just run by itself. I have that
error on other parts of the macro, too, but I've usually found a workaround.

Is there a reason for this?

Pflugs