Error importing modules from .bas files(Excel2003)

marpashuk · August 27th, 2004, 06:43 PM

Hi Guys/Girls!

I have a problem and hope someone can help.
I wrote this code in Excel XP and it worked fine. Later I switched to 2003 at I can't remember if I tested it since then.

Now I'm trying to improve my project, and it turns out that it's not working. Here's the code:

Code:

Private Sub CopyModule()
On Error GoTo ModuleError
    Dim pstrModuleName As String
    pstrModuleName = ThisWorkbook.Path & "\UpdateCalculate.bas"
    Workbooks(pstrName & ".xls").VBProject.VBComponents.Import pstrModuleName
    Exit Sub
ModuleError:
    MsgBox "An error in the CopyModule sub...again"
End Sub

Here pstrName is the public variable holding the name of the job for which the file was created. So the files are called "somename.xls" or "anothername.xls". This workbook and the UpdateCalculate.bas files are located in the same folder, so ThisWorkbook.Path & "\UpdateCalculate.bas" creates a correct address string. There are about 10-12 other modules working together and they are all fine, the error occurs when I'm trying to import my .bas module using this particular method. Was there a change in Excel 2003 that disabled this method?
I'm lost - I know this should work because it DID! I was searching microsoft site - nothing's there. Excel help says there is a vbproject property, but according to them there is no such thing as vbcomponents.
Any ideas?

Thanks in advance.

tcarnahan · August 27th, 2004, 10:06 PM

Is it hanging on the "VBProject.VBComponents" part?

If so, could be you don't have the "Visual Basic for Applications Extensibility" reference set in the VB Editor.

... I don't have 2003, but that was the first thing to come to mind.

--- Tom

marpashuk · August 27th, 2004, 10:47 PM

Hi Tom.

I already fixed the problem and should have posted that earlier...
Yes, the problem was in references - extensibility was not checked.
Strange, but I remember I did not add any references to this project in Excel XP. Is it possible it was there by default? And what about "Trust access to VB Project" from Security tab in Excel? I know for sure I didn't specifically set it to true when I was writing the code. And everything worked fine... But now I had to do both: add a reference and manually check the box to Trust access to VBP.

Anyway, it's OK now, everything's working fine and I learned my lesson - if it works on one system, be prepared to debug it on another...

Thanks for reply.
Maria.

There's always an answer. Your task is to find it.