Copying modules from Excel to Word

[Pflugs]

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

 

[Pflugs]

I have now 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?

 

[Jean-Guy Marcil]

Pflugs was telling us:
Pflugs nous racontait que :

I have now 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?

Just an idea like that...
Maybe the IMporting oocirs too fast, the Export may not be finished.

Have you tried with a DoEvents or a call to a "Wait" type of function
between

Module.Export fname

and

xlApp.ActiveWorkbook.VBProject.VBComponents.import fname

???

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Pflugs]

I did, and that didn't work either. I have since decided to forego
automating in Word to Excel and just open Excel directly. It makes the code
a lot simpler.

Thanks for your time!

Pflugs