By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,647 Members | 1,178 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,647 IT Pros & Developers. It's quick & easy.

Problem interacting between VB.NET and VBA6 with Excel 2003

P: n/a
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.E xcel\
11.0.0.0__71e9bce111e9429c\Microsoft.Office.Intero p.Excel.dll

Assembly office
C:\WINDOWS\assembly\GAC\Office\11.0.0.0__71e9bce11 1e9429c\
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..Applica tion.ActiveWorkbook
SourceProject = SourceBook.VBProject
SourceComponents = SourceBook.VBProject.VBComponents
TargetBook=Microsoft.Office.Interop.Excel..Applica tion.Workbooks.Add
TargetProject = TargetBook.VBProject
TargetProject.Name = SourceProject.Name
TargetComponents = TargetProject.VBComponents
Apr 10 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
why use .net?

Vba / vb6 works fine without this .net crap-- and this .net crap all
it does is create deployment nightmares

office doesn't support .net yet-- so screw .net


On Apr 9, 11:06 pm, gur...@oz.net (Nicholas Dreyer) wrote:
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.E xcel\
11.0.0.0__71e9bce111e9429c\Microsoft.Office.Intero p.Excel.dll

Assembly office
C:\WINDOWS\assembly\GAC\Office\11.0.0.0__71e9bce11 1e9429c\
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..Applica tion.ActiveWorkbook
SourceProject = SourceBook.VBProject
SourceComponents = SourceBook.VBProject.VBComponents

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

.
.
.
'
' Copy workbook components
'
IsTheBook = False
For Each SourceComponent In SourceComponents
If SourceComponent.Type = vbext_ct_Document Then
For Each ThisProperty In SourceComponent.Properties
If ThisProperty.Name = "IsAddin" Then
'
' The following illustrates a strange work-around
'
' With SourceComponent ' Reference to .CodeModule.CountOfLines Fails
every time with the message at the top of this message, on the other
hand
With SourceComponents(SourceComponent.Name) ' Reference to
.CodeModule.CountOfLines Succeeds
'
'
'

TargetComponents(IndexOfBook).Properties("_CodeNam e").Value=
SourceComponent.Name
If .CodeModule.CountOfLines 0 Then _
TargetetComponents(IndexOfBook).CodeModule.InsertL ines( _

1,.CodeModule.Lines(1,.CodeModule.CountOfLines))
End With
IsTheBook = True
Exit For
End If
Next
End If
If IsTheBook Then Exit For
Next
'
' Copy references
'
With SourceProject
'
' The following always generates the error
'
For Each SourceReference In .References ' Reference to .References
Fails every time with the error at the top of this message
'
'
'
CheckRefName = SourceReference.Name
IsNewRef = True
For Each TargetRef In TargetProject.References
If TargetRef.Name = CheckRefName Then
IsNewRef = False
Exit For
End If
Next
If IsNewRef Then _
TargetProject.References.AddFromFile _
(SourceReference.FullPath)
Next
.
.
.

End Sub

Any and all insights are greatly appreciated. Thanks, Nick

|\|.

================================================== =========================*=====

Hey you know something people? They won't go for no more
I'm not black, but there's a whole lotsa times Great Midwestern hardware store
I wish I could say I'm not white. Philosophy that turnsaway
(1965, Frank Zappa in Trouble Every Day From those who aren'tafraid to say
-Freak Out) What's on their mind -- The left behinds
Of The Great Society.
(1965, Frank Zappa inHungry Freaks, Daddy
-Freak Out)

Apr 10 '07 #2

P: n/a
Just FYI, ignore the Larry Linson guy. He's trolling.

Robin S.
-----------------------------
"Nicholas Dreyer" <gu****@oz.netwrote in message
news:46*************@news.earthlink.net...
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.E xcel\
11.0.0.0__71e9bce111e9429c\Microsoft.Office.Intero p.Excel.dll

Assembly office
C:\WINDOWS\assembly\GAC\Office\11.0.0.0__71e9bce11 1e9429c\
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..Applica tion.ActiveWorkbook
SourceProject = SourceBook.VBProject
SourceComponents = SourceBook.VBProject.VBComponents
TargetBook=Microsoft.Office.Interop.Excel..Applica tion.Workbooks.Add
TargetProject = TargetBook.VBProject
TargetProject.Name = SourceProject.Name
TargetComponents = TargetProject.VBComponents
.
.
.
'
' Copy workbook components
'
IsTheBook = False
For Each SourceComponent In SourceComponents
If SourceComponent.Type = vbext_ct_Document Then
For Each ThisProperty In SourceComponent.Properties
If ThisProperty.Name = "IsAddin" Then
'
' The following illustrates a strange work-around
'
' With SourceComponent ' Reference to .CodeModule.CountOfLines Fails
every time with the message at the top of this message, on the other
hand
With SourceComponents(SourceComponent.Name) ' Reference to
.CodeModule.CountOfLines Succeeds
'
'
'

TargetComponents(IndexOfBook).Properties("_CodeNam e").Value=
SourceComponent.Name
If .CodeModule.CountOfLines 0 Then _
TargetetComponents(IndexOfBook).CodeModule.InsertL ines( _

1,.CodeModule.Lines(1,.CodeModule.CountOfLines))
End With
IsTheBook = True
Exit For
End If
Next
End If
If IsTheBook Then Exit For
Next
'
' Copy references
'
With SourceProject
'
' The following always generates the error
'
For Each SourceReference In .References ' Reference to .References
Fails every time with the error at the top of this message
'
'
'
CheckRefName = SourceReference.Name
IsNewRef = True
For Each TargetRef In TargetProject.References
If TargetRef.Name = CheckRefName Then
IsNewRef = False
Exit For
End If
Next
If IsNewRef Then _
TargetProject.References.AddFromFile _
(SourceReference.FullPath)
Next
.
.
.

End Sub
Any and all insights are greatly appreciated. Thanks, Nick

|\|.

================================================== ==============================

Hey you know something people? They won't go for no
more
I'm not black, but there's a whole lotsa times Great Midwestern
hardware store
I wish I could say I'm not white. Philosophy that turns
away
(1965, Frank Zappa in Trouble Every Day From those who aren't
afraid to say
-Freak Out) What's on their
mind -- The left behinds
Of The Great Society.
(1965, Frank Zappa in
Hungry Freaks, Daddy
-Freak Out)

Apr 11 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.