Hi all.

I have the following problem.

We have one excel file with a lot of VBA-macros inside to create
customer product lists. One of these macros is inserted to each new
customer list (customer lists ar generated to new workbook). This marco
simply does some sheet formatting before printing.

Now, I open this file from another application through automation and
call certain macro to create customer list. This certain macro inserts
a code module, copies needed functions for printing to that new file and
associates printing macro with a button. After that it saves the worbook
to specified file. Everything works well except the inserted code module
is not save to new file if this is created through automation. When
called inside excel, the codemodule is saved.

A sample code i use:

' This will be called only through automation
Public Sub CreateCustomerList(targetFile As String)
Dim sh As Worksheet, ssDeleted As SubSectionPage
Dim m As Integer, trInfo As TranslationInfo

On Error GoTo hErr
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Interactive = False
Application.Cursor = xlWait

' This one makes a customer list to new file and
' inserts a CodeModule and methods for printing
If DoGenerateList(False, False) = True Then
' Save file to specified location
m_newWB.SaveAs targetFile
End If
' Error handling etc.
.....
.....
.....
End Sub

Private Function DoGenerateList(Optional ActivateNewWB As Boolean =
True, Optional ActivateThisWB As Boolean = True) As Boolean
.....
.....
' Actual Workbook creation
.....
.....

' Insert printing template sheet to new workbook
ThisWorkbook.Sheets("PrintTemplate").
Copy After:=newWB.Sheets(newWB.Sheets.Count)

Set cModTarget = newWB.VBProject.VBComponents.Add(vbext_ct_StdModul e)
Set cModSource = ThisWorkbook.VBProject.
VBComponents("modPrintExport").CodeModule

' Insert printing macro
cModTarget.CodeModule.AddFromString(cModSource.Lin es(1,cModSource.CountOfLines)

' Hide printing template sheet
newWB.Sheets("PrintTemplate").visible = modCreate.XlSheetVeryHidden
.....
.....
Rest of the macro and clean up
.....
.....
DoGenerateList = True
End Function



I debugged the macros above when they are called through automation and
they works just fine (module and code is inserted to new workbook and
stays in new workbook even after calling SaveAs method). But when the
saved workbook closed and opened again the CodeModule is gone!!!

Any ideas what i´m doing wrong?
Or is it a bug?

Excel automation using version independent excel COM cause this should
work int excel versions from 97....2007. OS is Windows XP.

Cheers

Asko.