I'm trying to copy modules from one Workbook to another using export and import. I found some very helpful code on Chip Pearson's site Programming to the Visual Basic Editor, thank you.
Using Excel 2000 and Win 98.
The code asks for the first file to update,
- creates an array of all the excel files in the directory of the first file found
For x = 1 to UBound of the array
- opens the first file in the array
- deletes all the code from that workbook including sheets and Thisworkbook
- exports all modules and forms from Thisworkbook (the workbook controlling the copy) and imports them into first file in array (excluding sheets and Thisworkbook - I couldn't get this to work - used a read of each line from a txt file)
- saves the updated file
- closes the updated file
Next x

The first file gets saved
The next file gets opened and the error message EXCEL caused an invalid page fault in EXCEL.EXE comes up.
Following is the code
Option Explicit
Sub UpdateCode()
'Update all modules in all Workbooks in selected directory
Dim x As Integer
Dim TheFileNameArray() As Variant
'Loop to open each excel file in the selected directory
TheFileNameArray = GetTheFileNameArray
For x = 1 To UBound(TheFileNameArray)
'Open and make active the found file
If Not IsEmpty(TheFileNameArray(x)) Then
'The following line causes an invalid page fault in EXCEL.EXE on the second file
Workbooks.Open TheFileNameArray(x)
' MsgBox "Each file name is " & TheFileNameArray(x) & " activeworkbook is " & ActiveWorkbook.Name
'Delete all the modules in the selected file
DeleteAllVBA
Workbooks(TheFileNameArray(x)).Save
'Copy all modules from this workbook
CopyAllModules TheFileNameArray(x)
Workbooks(TheFileNameArray(x)).Save
ActiveWorkbook.Close 'SaveChanges:=True
End If
Next x
End Sub
Function GetTheFileNameArray() As Variant
'Get the name of first file in the selected directory
Dim TheFileName As String, TheFilePath As String
Dim j As Long
Dim f As Variant
Dim FileNameArray() As Variant
Dim x As Integer
TheFileName = Application.GetOpenFileName( _
Title:="Please select any file from the folder that contains the files to be updated, then click Open.")
For j = Len(TheFileName) To 1 Step -1
If Mid(TheFileName, j, 1) = "\" Then Exit For
Next j
TheFilePath = Left(TheFileName, j)
TheFileName = TheFilePath & "*.xls"
f = Dir(TheFileName, vbNormal)
Do Until f = ""
x = x + 1
ReDim Preserve FileNameArray(1 To x)
If f < ThisWorkbook.Name Then 'Don't update the calling workbook
FileNameArray(x) = f 'Return the file name
End If
f = Dir
Loop
GetTheFileNameArray = FileNameArray
End Function
Sub DeleteAllVBA()
Dim VBComp As VBIDE.VBComponent, VBCompTest As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
'MsgBox "The module name to delete is " & VBComp.Name & " Workbook is " & ActiveWorkbook.Name
VBComps.Remove VBComp
Case Else
'MsgBox "The module name to delete is " & VBComp.Name & " Workbook is " & ActiveWorkbook.Name
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub
Sub CopyAllModules(SelectedFile As Variant)
Dim FName As String, ClsFName As String
Dim VBComp As VBIDE.VBComponent

With Workbooks(ThisWorkbook.Name)
FName = .Path & "\code.txt"
If Dir(FName) < "" Then
Kill FName
End If
For Each VBComp In .VBProject.VBComponents
If VBComp.Type < vbext_ct_Document Then
VBComp.Export FName
'MsgBox "VBComp name is in Type Not vbext_ct_Document " & VBComp.Name & " VBComp.Type is " & VBComp.Type
Workbooks(SelectedFile).VBProject.VBComponents.Imp ort FName
Kill FName
'Else
' ClsFName = VBComp.Name & ".txt"
' If Dir(ClsFName) < "" Then
' Kill ClsFName
' End If
' VBComp.Export VBComp.Name & ".txt"
'If ClsFName = "ThisWorkbook" Then
' InsertProcedureCodeFromCode Workbooks(SelectedFile), VBComp.Name, .Path & "\" & ClsFName
' MsgBox "VBComp name is in Type vbext_ct_Document " & VBComp.Name & " VBComp.Type is " & VBComp.Type
' Kill ClsFName
'End If
End If
Next VBComp
End With
End Sub

Please help.
Thank you.


-

Janet Martin wrote:

I'm trying to copy modules from one Workbook to another using export
and import. I found some very helpful code on Chip Pearson's site
Programming to the Visual Basic Editor, thank you. Using Excel 2000
and Win 98.

Have you tried this in other Excel versions (XP, 2003) and on other
Windows versions (2000, XP, 2003)? Particularly Windows 98 is notorious
for its bad memory management. Which is what the error is about: Excel
trying to access an invalid memory page.

--
Amedee Van Gasse using XanaNews 1.16.3.1
If it has an "X" in the name, it must be Linux?

Please don't thank me in advance. Thank me afterwards if it works or
smack me in the face if it doesn't. ;-)

Hello
Yes, I've tried Win XP with Excel 2002, on a different machine, same thing, although I think it also complained about VB6.dll.
But I've just created 3 blank workbooks and the routine works perfectly with them. I should have done this before posting the question. There must be something in the workbooks I'm working with that's messing things up.
I'll try removing all unused cells. Any other suggestions?

"Amedee Van Gasse" wrote:

Janet Martin wrote:

I'm trying to copy modules from one Workbook to another using export
and import. I found some very helpful code on Chip Pearson's site
Programming to the Visual Basic Editor, thank you. Using Excel 2000
and Win 98.

Have you tried this in other Excel versions (XP, 2003) and on other
Windows versions (2000, XP, 2003)? Particularly Windows 98 is notorious
for its bad memory management. Which is what the error is about: Excel
trying to access an invalid memory page.

--
Amedee Van Gasse using XanaNews 1.16.3.1
If it has an "X" in the name, it must be Linux?

Please don't thank me in advance. Thank me afterwards if it works or
smack me in the face if it doesn't. ;-)

Hello
Yes, I've tried Win XP with Excel 2002 on a different PC, same thing, although it also complained about VB6.dll, I think.
However, I've just created 3 blank workbooks, and it works like a charm. Sorry, should have done this before posting. There must be something in the workbooks I'm working with, I'll try removing all unused cells. Any other ideas?

"Amedee Van Gasse" wrote:

Janet Martin wrote:

I'm trying to copy modules from one Workbook to another using export
and import. I found some very helpful code on Chip Pearson's site
Programming to the Visual Basic Editor, thank you. Using Excel 2000
and Win 98.

Have you tried this in other Excel versions (XP, 2003) and on other
Windows versions (2000, XP, 2003)? Particularly Windows 98 is notorious
for its bad memory management. Which is what the error is about: Excel
trying to access an invalid memory page.

--
Amedee Van Gasse using XanaNews 1.16.3.1
If it has an "X" in the name, it must be Linux?

Please don't thank me in advance. Thank me afterwards if it works or
smack me in the face if it doesn't. ;-)

Hello
Yes, I've tried Win XP with Excel 2002 on a different PC, same thing, although it also complained about VB6.dll, I think.
However, I've just created 3 blank workbooks, and it works like a charm. Sorry, should have done this before posting. Copied and pasted into new workbook the used cells, crashes Excel 2000 in Win 98, works in Excel 2002 Win XP.


"Amedee Van Gasse" wrote:

Janet Martin wrote:

I'm trying to copy modules from one Workbook to another using export
and import. I found some very helpful code on Chip Pearson's site
Programming to the Visual Basic Editor, thank you. Using Excel 2000
and Win 98.

Have you tried this in other Excel versions (XP, 2003) and on other
Windows versions (2000, XP, 2003)? Particularly Windows 98 is notorious
for its bad memory management. Which is what the error is about: Excel
trying to access an invalid memory page.

--
Amedee Van Gasse using XanaNews 1.16.3.1
If it has an "X" in the name, it must be Linux?

Please don't thank me in advance. Thank me afterwards if it works or
smack me in the face if it doesn't. ;-)

Hello
Yes, I've tried Win XP with Excel 2002 on a different PC, same thing, although it also complained about VB6.dll, I think.
However, I've just created 3 blank workbooks, and it works like a charm. Sorry, should have done this before posting. Copied and pasted into new workbook the used cells, crashes Excel 2000 in Win 98, works in Excel 2002 Win XP.


"Amedee Van Gasse" wrote:

Janet Martin wrote:

I'm trying to copy modules from one Workbook to another using export
and import. I found some very helpful code on Chip Pearson's site
Programming to the Visual Basic Editor, thank you. Using Excel 2000
and Win 98.

Have you tried this in other Excel versions (XP, 2003) and on other
Windows versions (2000, XP, 2003)? Particularly Windows 98 is notorious
for its bad memory management. Which is what the error is about: Excel
trying to access an invalid memory page.

--
Amedee Van Gasse using XanaNews 1.16.3.1
If it has an "X" in the name, it must be Linux?

Please don't thank me in advance. Thank me afterwards if it works or
smack me in the face if it doesn't. ;-)

Sorry about the multi replies, the system said it was down and to try again later. Obviously it wasn't down.
I've now tried another approach, basically with the modules in a template and I'm now merging the sheet into the template and saving the file, thus avoiding the creating of the modules on the fly.
Works like a charm in Win XP Excel 2002, but Win 98 still causes invalid page fault.
My question - can I preserve the memory, or something, to make Win 98 happy?

"Amedee Van Gasse" wrote:

Janet Martin wrote:

I'm trying to copy modules from one Workbook to another using export
and import. I found some very helpful code on Chip Pearson's site
Programming to the Visual Basic Editor, thank you. Using Excel 2000
and Win 98.

Have you tried this in other Excel versions (XP, 2003) and on other
Windows versions (2000, XP, 2003)? Particularly Windows 98 is notorious
for its bad memory management. Which is what the error is about: Excel
trying to access an invalid memory page.

--
Amedee Van Gasse using XanaNews 1.16.3.1
If it has an "X" in the name, it must be Linux?

Please don't thank me in advance. Thank me afterwards if it works or
smack me in the face if it doesn't. ;-)