Problem interacting between VB.NET and VBA6 with Excel 2003

  • Thread starter Nicholas Dreyer
  • Start date
N

Nicholas Dreyer

The following error

Run-time exception thrown :
System.Runtime.InteropServices.COMException - Error loading type
library/DLL.

happens while running the code listed at the bottom of this message in
the environment shown here:

Operating System: Microsoft Windows Version 5.1 (Build
2600.xpsp_sp2_gdr.050301-1519 : Service Pack 2)

Visual Basic: MIcrosoft Visual Basic 6.3 Version 9972 VBA: Retail
6.4.9972 Forms3: 11.0.6550 (comes with Excel 2003)

The following library references are used in the VB.NET project:

Assembly Microsoft.Office.Interop.Excel
C:\WINDOWS\assembly\GAC\Microsoft.Office.Interop.Excel\
11.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll

Assembly office
C:\WINDOWS\assembly\GAC\Office\11.0.0.0__71e9bce111e9429c\
Office.dll

Assembly Microsoft.VisualBasic.Compatibility
C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\
Microsoft.VisualBasic.Compatibility.dll

Assembly System
C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\
System.dll

Assembly System.Data
C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\
System.Data.dll

Assembly System.Windows.Forms
C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\
System.Windows.Forms.dll

Assembly Interop.VBA
C:\vb\Nick\PlanningToolsModules\bin\
Interop.VBA.dll

Assembly Microsoft.Vbe.Interop

C:\WINDOWS\assembly\GAC\Microsoft.Vbe.Interop\11.0.0.0__71e9bce111e9429c\
Microsoft.Vbe.Interop.dll


and the following "Imports" statements are included:

Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop.Excel.Constants
Imports Microsoft.Office.Interop.Excel.XlLookAt
Imports Microsoft.Office.Interop.Excel.XlSearchOrder
Imports Microsoft.Office.Interop.Excel.XlFindLookIn
Imports Microsoft.Office.Interop.Excel.XlDirection
Imports Microsoft.Office.Interop.Excel.XlAxisType
Imports Microsoft.Vbe.Interop
Imports Microsoft.Vbe.Interop.vbext_ComponentType

The code below is the relevant portion, extracted from a much larger
project Sub, hopefully enough to get a glimpse of what's happening.
In summary, it is VB.NET code accessing Excel VBA6 library objects to
control the Excel VBA environment. The purpose of the two odd
sections (first one showing a peculiar work-around) is in the first
instance to copy a source workbook project codemodule component, and
in the second, all project library references.

To me the most suspicious aspect of the problem lies in the fact the
crash occurs when accessing a VBComponent.Codemodule (in the first
instance) and a VBProject.Reference, both out of library
Microsoft.Vbe.Interop, and this all began after I had to switch from
picking up these objects through the VBIDE library to the
Microsoft.Vbe.Interop library.
_______________________________________________


Sub CreateCleanWorkbook()

Dim SourceReference As Reference, SourceProject As VBProject
Dim TargetProject As VBProject, SourceComponent As VBComponent
Dim TargetComponent As VBComponent, CheckRefName As String
Dim TargetRef As Reference, IsNewRef As Boolean, IsTheBook As
Boolean
Dim TargetComponents As VBComponents, SourceComponents As
VBComponents


SourceBook=Microsoft.Office.Interop.Excel..Application.ActiveWorkbook
SourceProject = SourceBook.VBProject
SourceComponents = SourceBook.VBProject.VBComponents


TargetBook=Microsoft.Office.Interop.Excel..Application.Workbooks.Add
TargetProject = TargetBook.VBProject
TargetProject.Name = SourceProject.Name
TargetComponents = TargetProject.VBComponents
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top