[AccessD] More Access to Excel Help Needed

Hi Thomas

What I did for a similar scenario, was to open Excel and open a Workbook containing the code, and then add the new WorkSheet to hold the data.
Further, to prevent bloat, the very first thing was to create the Workbook with the code as a copy of a master Workbook.

/gustav

>>> ewaldt at gdls.com 19-09-2007 16:11 >>>
I've been trying to narrow down the problem, and I believe I've found it. 
I commented out the line that calls a module that I use to copy a module 
from my Access database to the Excel workbook. Then I put a breakpoint in 
right before it, and manually copied the module into the workbook. It 
worked fine. I tried it more than once with no problem at all. The 
problem, it would seem, must be in the CopyModule module.

Can someone see a problem with the module below? I think I remember 
adapting it from an Excel-to-Excel puppy, so apparently my adaptation is 
incorrect. I don't specifically open anything from within this module, so 
it does not seem that I should close anything. Of course I could be wrong.

I send it the name of the workbook (variable = xlBook, so I send it 
xlBook.Name). "CR_Impacts" is the name of the module within Access that I 
want to copy.

As always, thanks for any and all help.

Thomas F. Ewald
Stryker Mass Properties
General Dynamics Land Systems


Sub CopyModule(strXL As String)
'Copy CR_Impacts module to Excel workbook

    Dim CodeLines As String
    Dim ModuleTocopy As VBComponent, NewModule As VBComponent
 
    Set NewModule = _
        Workbooks(strXL).VBProject.VBComponents.Add(vbext_ct_StdModule)
    Set ModuleTocopy = _
        Application.VBE.ActiveVBProject.VBComponents("CR_Impacts")
 
    CodeLines = ModuleTocopy.CodeModule.Lines _
            (ModuleTocopy.CodeModule.CountOfLines)
 
    NewModule.CodeModule.AddFromString CodeLines
 
    NewModule.Name = ModuleTocopy.Name
 
End Sub