There's a significant problem in automating Excel from VB .NET.
Reminds me of a problem I encountered almost 3 years ago that was caused by
the Norton Auntie Virus Office plug-in.
Can anybody reproduce the behavior described below?
For this example, I am using Excel 2002 and VS .NET 2002 and VB 6.
MSFT KB article 304661 gives a trivial example of early and late binding to
Excel
from VB .NET. Note that there is a variable naming error in the article, so
you are better
off using the code I am including below.
I am providing 3 pieces of code:
1. The VB .NET code from the KB article, with my corrections.
2. The equivalent VB 6 code, from me.
3. The VB.NET code generated by importin gthe VB 6 code into VB .NET 2002.
In the code, you will see two means for creating the Excel object.
Using New results in correct output for all 3 sets of code.
Using CreateObject results in correct output only for the VB 6 code.
To reproduce the error, I can:
1. Create a new VB .NET project of type Windows application.
2. Add a reference to the Excel 10 object library.
3. Add a button to the Form.
4. Use the code below for the Button1 Click event.
5. For the VB 6 code, follow the same steps, but the button is named
Command1.
Here is corrected code from KB article:
---------------------------------------------
Public Class Form1
Inherits System.Windows. Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeCompo nent()
'Add any initialization after the InitializeCompo nent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Disp ose()
End If
End If
MyBase.Dispose( disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.Componen tModel.IContain er
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents Button1 As System.Windows. Forms.Button
<System.Diagnos tics.DebuggerSt epThrough()> Private Sub InitializeCompo nent()
Me.Button1 = New System.Windows. Forms.Button()
Me.SuspendLayou t()
'
'Button1
'
Me.Button1.Loca tion = New System.Drawing. Point(40, 40)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing. Size(176, 40)
Me.Button1.TabI ndex = 0
Me.Button1.Text = "Button1"
'
'Form1
'
Me.AutoScaleBas eSize = New System.Drawing. Size(5, 13)
Me.ClientSize = New System.Drawing. Size(292, 273)
Me.Controls.Add Range(New System.Windows. Forms.Control() {Me.Button1})
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout (False)
End Sub
#End Region
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Dim objApp As Excel.Applicati on
Dim objBook As Excel._Workbook
Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet As Excel._Workshee t
Dim objrange As Excel.Range
' Instantiate Excel and start a new workbook.
objApp = New Excel.Applicati on() ' This works
'objApp = CreateObject("E xcel.Applicatio n") ' This does NOT work
objBooks = objApp.Workbook s
objBook = objBooks.Add
objSheets = objBook.Workshe ets
objSheet = objSheets.Item( 1)
objrange = objSheet.Range( "A1")
'Set the range value.
objrange.Value = "Hello, World!"
'Return control of Excel to the user.
objApp.Visible = True
objApp.UserCont rol = True
End Sub
End Class
---------------------------------------------
Here is the VB 6 code:
---------------------------------------------
Option Explicit
Private Sub Command1_Click( )
Dim objApp As Excel.Applicati on
Dim objBook As Excel.Workbook
Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet As Excel.Worksheet
Dim objrange As Excel.Range
' Instantiate Excel and start a new workbook.
' Set objApp = New Excel.Applicati on ' This works
Set objApp = CreateObject("E xcel.Applicatio n") ' This ALSO works
Set objBooks = objApp.Workbook s
Set objBook = objBooks.Add
Set objSheets = objBook.Workshe ets
Set objSheet = objSheets.Item( 1)
Set objrange = objSheet.Range( "A1")
'Set the range value.
objrange.Value = "Hello, World!"
'Return control of Excel to the user.
objApp.Visible = True
objApp.UserCont rol = True
End Sub
---------------------------------------------
Here is the VB .NET code generated from the VB 6 code:
---------------------------------------------
Option Strict Off
Option Explicit On
Friend Class Form1
Inherits System.Windows. Forms.Form
#Region "Windows Form Designer generated code "
Public Sub New()
MyBase.New()
If m_vb6FormDefIns tance Is Nothing Then
If m_InitializingD efInstance Then
m_vb6FormDefIns tance = Me
Else
Try
'For the start-up form, the first instance created is the default instance.
If System.Reflecti on.Assembly.Get ExecutingAssemb ly.EntryPoint.D eclaringType
Is Me.GetType Then
m_vb6FormDefIns tance = Me
End If
Catch
End Try
End If
End If
'This call is required by the Windows Form Designer.
InitializeCompo nent()
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal Disposing As Boolean)
If Disposing Then
If Not components Is Nothing Then
components.Disp ose()
End If
End If
MyBase.Dispose( Disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.Componen tModel.IContain er
Public ToolTip1 As System.Windows. Forms.ToolTip
Public WithEvents Command1 As System.Windows. Forms.Button
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
<System.Diagnos tics.DebuggerSt epThrough()> Private Sub InitializeCompo nent()
Dim resources As System.Resource s.ResourceManag er = New
System.Resource s.ResourceManag er(GetType(Form 1))
Me.components = New System.Componen tModel.Containe r()
Me.ToolTip1 = New System.Windows. Forms.ToolTip(c omponents)
Me.ToolTip1.Act ive = True
Me.Command1 = New System.Windows. Forms.Button
Me.Text = "Form1"
Me.ClientSize = New System.Drawing. Size(312, 213)
Me.Location = New System.Drawing. Point(4, 23)
Me.StartPositio n =
System.Windows. Forms.FormStart Position.Window sDefaultLocatio n
Me.Font = New System.Drawing. Font("Arial", 8!,
System.Drawing. FontStyle.Regul ar, System.Drawing. GraphicsUnit.Po int,
CType(0, Byte))
Me.AutoScaleBas eSize = New System.Drawing. Size(5, 13)
Me.BackColor = System.Drawing. SystemColors.Co ntrol
Me.FormBorderSt yle = System.Windows. Forms.FormBorde rStyle.Sizable
Me.ControlBox = True
Me.Enabled = True
Me.KeyPreview = False
Me.MaximizeBox = True
Me.MinimizeBox = True
Me.Cursor = System.Windows. Forms.Cursors.D efault
Me.RightToLeft = System.Windows. Forms.RightToLe ft.No
Me.ShowInTaskba r = True
Me.HelpButton = False
Me.WindowState = System.Windows. Forms.FormWindo wState.Normal
Me.Name = "Form1"
Me.Command1.Tex tAlign = System.Drawing. ContentAlignmen t.MiddleCenter
Me.Command1.Tex t = "Command1"
Me.Command1.Siz e = New System.Drawing. Size(73, 73)
Me.Command1.Loc ation = New System.Drawing. Point(96, 48)
Me.Command1.Tab Index = 0
Me.Command1.Fon t = New System.Drawing. Font("Arial", 8!,
System.Drawing. FontStyle.Regul ar, System.Drawing. GraphicsUnit.Po int,
CType(0, Byte))
Me.Command1.Bac kColor = System.Drawing. SystemColors.Co ntrol
Me.Command1.Cau sesValidation = True
Me.Command1.Ena bled = True
Me.Command1.For eColor = System.Drawing. SystemColors.Co ntrolText
Me.Command1.Cur sor = System.Windows. Forms.Cursors.D efault
Me.Command1.Rig htToLeft = System.Windows. Forms.RightToLe ft.No
Me.Command1.Tab Stop = True
Me.Command1.Nam e = "Command1"
Me.Controls.Add (Command1)
End Sub
#End Region
#Region "Upgrade Support "
Private Shared m_vb6FormDefIns tance As Form1
Private Shared m_InitializingD efInstance As Boolean
Public Shared Property DefInstance() As Form1
Get
If m_vb6FormDefIns tance Is Nothing OrElse m_vb6FormDefIns tance.IsDispose d
Then
m_InitializingD efInstance = True
m_vb6FormDefIns tance = New Form1()
m_InitializingD efInstance = False
End If
DefInstance = m_vb6FormDefIns tance
End Get
Set
m_vb6FormDefIns tance = Value
End Set
End Property
#End Region
Private Sub Command1_Click( ByVal eventSender As System.Object, ByVal
eventArgs As System.EventArg s) Handles Command1.Click
Dim objApp As Excel.Applicati on
Dim objBook As Excel.Workbook
Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet As Excel.Worksheet
Dim objrange As Excel.Range
' Instantiate Excel and start a new workbook.
objApp = New Excel.Applicati on() ' This works
'objApp = CreateObject("E xcel.Applicatio n") ' This does NOT work
objBooks = objApp.Workbook s
objBook = objBooks.Add
objSheets = objBook.Workshe ets
objSheet = objSheets.Item( 1)
objrange = objSheet.Range( "A1")
'Set the range value.
objrange.Value = "Hello, World!"
'Return control of Excel to the user.
objApp.Visible = True
objApp.UserCont rol = True
End Sub
End Class
--
http://www.standards.com/; See Howard Kaikow's web site.
Reminds me of a problem I encountered almost 3 years ago that was caused by
the Norton Auntie Virus Office plug-in.
Can anybody reproduce the behavior described below?
For this example, I am using Excel 2002 and VS .NET 2002 and VB 6.
MSFT KB article 304661 gives a trivial example of early and late binding to
Excel
from VB .NET. Note that there is a variable naming error in the article, so
you are better
off using the code I am including below.
I am providing 3 pieces of code:
1. The VB .NET code from the KB article, with my corrections.
2. The equivalent VB 6 code, from me.
3. The VB.NET code generated by importin gthe VB 6 code into VB .NET 2002.
In the code, you will see two means for creating the Excel object.
Using New results in correct output for all 3 sets of code.
Using CreateObject results in correct output only for the VB 6 code.
To reproduce the error, I can:
1. Create a new VB .NET project of type Windows application.
2. Add a reference to the Excel 10 object library.
3. Add a button to the Form.
4. Use the code below for the Button1 Click event.
5. For the VB 6 code, follow the same steps, but the button is named
Command1.
Here is corrected code from KB article:
---------------------------------------------
Public Class Form1
Inherits System.Windows. Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeCompo nent()
'Add any initialization after the InitializeCompo nent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Disp ose()
End If
End If
MyBase.Dispose( disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.Componen tModel.IContain er
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents Button1 As System.Windows. Forms.Button
<System.Diagnos tics.DebuggerSt epThrough()> Private Sub InitializeCompo nent()
Me.Button1 = New System.Windows. Forms.Button()
Me.SuspendLayou t()
'
'Button1
'
Me.Button1.Loca tion = New System.Drawing. Point(40, 40)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing. Size(176, 40)
Me.Button1.TabI ndex = 0
Me.Button1.Text = "Button1"
'
'Form1
'
Me.AutoScaleBas eSize = New System.Drawing. Size(5, 13)
Me.ClientSize = New System.Drawing. Size(292, 273)
Me.Controls.Add Range(New System.Windows. Forms.Control() {Me.Button1})
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout (False)
End Sub
#End Region
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Dim objApp As Excel.Applicati on
Dim objBook As Excel._Workbook
Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet As Excel._Workshee t
Dim objrange As Excel.Range
' Instantiate Excel and start a new workbook.
objApp = New Excel.Applicati on() ' This works
'objApp = CreateObject("E xcel.Applicatio n") ' This does NOT work
objBooks = objApp.Workbook s
objBook = objBooks.Add
objSheets = objBook.Workshe ets
objSheet = objSheets.Item( 1)
objrange = objSheet.Range( "A1")
'Set the range value.
objrange.Value = "Hello, World!"
'Return control of Excel to the user.
objApp.Visible = True
objApp.UserCont rol = True
End Sub
End Class
---------------------------------------------
Here is the VB 6 code:
---------------------------------------------
Option Explicit
Private Sub Command1_Click( )
Dim objApp As Excel.Applicati on
Dim objBook As Excel.Workbook
Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet As Excel.Worksheet
Dim objrange As Excel.Range
' Instantiate Excel and start a new workbook.
' Set objApp = New Excel.Applicati on ' This works
Set objApp = CreateObject("E xcel.Applicatio n") ' This ALSO works
Set objBooks = objApp.Workbook s
Set objBook = objBooks.Add
Set objSheets = objBook.Workshe ets
Set objSheet = objSheets.Item( 1)
Set objrange = objSheet.Range( "A1")
'Set the range value.
objrange.Value = "Hello, World!"
'Return control of Excel to the user.
objApp.Visible = True
objApp.UserCont rol = True
End Sub
---------------------------------------------
Here is the VB .NET code generated from the VB 6 code:
---------------------------------------------
Option Strict Off
Option Explicit On
Friend Class Form1
Inherits System.Windows. Forms.Form
#Region "Windows Form Designer generated code "
Public Sub New()
MyBase.New()
If m_vb6FormDefIns tance Is Nothing Then
If m_InitializingD efInstance Then
m_vb6FormDefIns tance = Me
Else
Try
'For the start-up form, the first instance created is the default instance.
If System.Reflecti on.Assembly.Get ExecutingAssemb ly.EntryPoint.D eclaringType
Is Me.GetType Then
m_vb6FormDefIns tance = Me
End If
Catch
End Try
End If
End If
'This call is required by the Windows Form Designer.
InitializeCompo nent()
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal Disposing As Boolean)
If Disposing Then
If Not components Is Nothing Then
components.Disp ose()
End If
End If
MyBase.Dispose( Disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.Componen tModel.IContain er
Public ToolTip1 As System.Windows. Forms.ToolTip
Public WithEvents Command1 As System.Windows. Forms.Button
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
<System.Diagnos tics.DebuggerSt epThrough()> Private Sub InitializeCompo nent()
Dim resources As System.Resource s.ResourceManag er = New
System.Resource s.ResourceManag er(GetType(Form 1))
Me.components = New System.Componen tModel.Containe r()
Me.ToolTip1 = New System.Windows. Forms.ToolTip(c omponents)
Me.ToolTip1.Act ive = True
Me.Command1 = New System.Windows. Forms.Button
Me.Text = "Form1"
Me.ClientSize = New System.Drawing. Size(312, 213)
Me.Location = New System.Drawing. Point(4, 23)
Me.StartPositio n =
System.Windows. Forms.FormStart Position.Window sDefaultLocatio n
Me.Font = New System.Drawing. Font("Arial", 8!,
System.Drawing. FontStyle.Regul ar, System.Drawing. GraphicsUnit.Po int,
CType(0, Byte))
Me.AutoScaleBas eSize = New System.Drawing. Size(5, 13)
Me.BackColor = System.Drawing. SystemColors.Co ntrol
Me.FormBorderSt yle = System.Windows. Forms.FormBorde rStyle.Sizable
Me.ControlBox = True
Me.Enabled = True
Me.KeyPreview = False
Me.MaximizeBox = True
Me.MinimizeBox = True
Me.Cursor = System.Windows. Forms.Cursors.D efault
Me.RightToLeft = System.Windows. Forms.RightToLe ft.No
Me.ShowInTaskba r = True
Me.HelpButton = False
Me.WindowState = System.Windows. Forms.FormWindo wState.Normal
Me.Name = "Form1"
Me.Command1.Tex tAlign = System.Drawing. ContentAlignmen t.MiddleCenter
Me.Command1.Tex t = "Command1"
Me.Command1.Siz e = New System.Drawing. Size(73, 73)
Me.Command1.Loc ation = New System.Drawing. Point(96, 48)
Me.Command1.Tab Index = 0
Me.Command1.Fon t = New System.Drawing. Font("Arial", 8!,
System.Drawing. FontStyle.Regul ar, System.Drawing. GraphicsUnit.Po int,
CType(0, Byte))
Me.Command1.Bac kColor = System.Drawing. SystemColors.Co ntrol
Me.Command1.Cau sesValidation = True
Me.Command1.Ena bled = True
Me.Command1.For eColor = System.Drawing. SystemColors.Co ntrolText
Me.Command1.Cur sor = System.Windows. Forms.Cursors.D efault
Me.Command1.Rig htToLeft = System.Windows. Forms.RightToLe ft.No
Me.Command1.Tab Stop = True
Me.Command1.Nam e = "Command1"
Me.Controls.Add (Command1)
End Sub
#End Region
#Region "Upgrade Support "
Private Shared m_vb6FormDefIns tance As Form1
Private Shared m_InitializingD efInstance As Boolean
Public Shared Property DefInstance() As Form1
Get
If m_vb6FormDefIns tance Is Nothing OrElse m_vb6FormDefIns tance.IsDispose d
Then
m_InitializingD efInstance = True
m_vb6FormDefIns tance = New Form1()
m_InitializingD efInstance = False
End If
DefInstance = m_vb6FormDefIns tance
End Get
Set
m_vb6FormDefIns tance = Value
End Set
End Property
#End Region
Private Sub Command1_Click( ByVal eventSender As System.Object, ByVal
eventArgs As System.EventArg s) Handles Command1.Click
Dim objApp As Excel.Applicati on
Dim objBook As Excel.Workbook
Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet As Excel.Worksheet
Dim objrange As Excel.Range
' Instantiate Excel and start a new workbook.
objApp = New Excel.Applicati on() ' This works
'objApp = CreateObject("E xcel.Applicatio n") ' This does NOT work
objBooks = objApp.Workbook s
objBook = objBooks.Add
objSheets = objBook.Workshe ets
objSheet = objSheets.Item( 1)
objrange = objSheet.Range( "A1")
'Set the range value.
objrange.Value = "Hello, World!"
'Return control of Excel to the user.
objApp.Visible = True
objApp.UserCont rol = True
End Sub
End Class
--
http://www.standards.com/; See Howard Kaikow's web site.
Comment