从VB .NET自动化Excel [英] Automating Excel from VB .NET
问题描述
从VB .NET自动化Excel有一个很大的问题。
让我想起我差不多3年前遇到的一个问题，这个问题是由诺顿造成的。
诺顿Auntie Virus Office插件。
任何人都可以重现下面描述的行为吗？
对于这个例子，我使用的是Excel 2002和VS .NET 2002以及VB 6.
MSFT知识库文章304661给出了一个简单的早期和晚期绑定示例
Excel
来自VB .NET 。请注意文章中有一个变量命名错误，所以
你更好
关闭使用我在下面包含的代码。
我提供3段代码：
1.知识库文章中的VB .NET代码，带有我的更正。
2.等价的VB 6代码，来自我。
3.通过importin生成的VB.NET代码将VB 6代码输入VB .NET 2002.
代码，您将看到两种创建Excel对象的方法。
在所有3组代码中使用正确输出的新结果。
使用CreateObject结果只输出正确的输出VB 6代码。
为了重现错误，我可以：
1.创建一个Windows应用程序类型的新VB .NET项目。
2.添加对Excel 10对象库的引用。
3.在表单上添加一个按钮。
4.使用下面的代码为Button1 Click事件。
5.对于VB 6代码，请按照相同的步骤，但是tton被命名为
Command1。
这里是KB文章的更正代码：
--------- ------------------------------------
Public Class Form1
继承System.Windows.Forms.Form
#Region" Windows窗体设计器生成的代码
Public Sub New（）
MyBase.New（）
''Windows需要此调用表单设计器。
InitializeComponent（）
''在InitializeComponent（）调用后添加任何初始化
End Sub
''表格覆盖处理以清理组件列表。
受保护的重载覆盖子处理（ByVal处理为布尔值）
如果处置则
如果不是（组件什么都没有）那么
components.Dispose（）
结束如果
结束如果
MyBase.Dispose（disposing）
End Sub
''Windows窗体设计器要求
私有组件As System.ComponentModel.IContainer
''注意：Windows窗体设计器需要以下步骤
''可以使用Windows窗体设计器修改它。
''不要使用代码编辑器修改它。
朋友WithEvents Button1 As System.Windows.Forms.Button
< System.Diagnostics.DebuggerStepThrough（）> Private Sub InitializeComponent（）
Me.Button1 = New System.Windows.Forms.Button（）
Me.SuspendLayout（）
''
''Button1
''
Me.Button1.Location = New System.Drawing.Point（40,40）
Me.Button1.Name =" Button1"
Me.Button1.Size = New System.Drawing.Size（176,40）
Me.Button1 .TabIndex = 0
Me.Button1.Text =" Button1"
''
''Form1
''
Me.AutoScaleBaseSize = New System.Drawing.Size（5,13）
Me.ClientSize = New System.Drawing.Size（292,273）
Me.Controls.AddRange（New System.Windows.Forms.Control（）{Me.Button1}）
Me.Name =" Form1"
Me.Text =" Form1"
Me.ResumeLayout（False）
End Sub
#End Region
Private Sub Button1_Click（ByVal sender As System.Object，ByVal e As
System.EventArgs）处理Button1.Click
Dim objApp As Excel.Application
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
''实例化Excel并启动一个新的工作簿。 />
objApp =新Excel.Application（）''这个工作
''objApp = CreateObject（" Excel.Application"）''这不起作用
objBooks = objApp.Workbooks
objBook = objBooks.Add
objSheets = objBook.Worksheets
objSheet = objSheets.Item（1）
objrange = objSheet.Range（" A1"）
''设置范围值。
objrange.Value ="你好， World！"
''将Excel的控制权交还给用户。
objApp.Visible = True
objApp.UserControl = True
结束次级
结束班级
------------------------ ---------------------
他重新是VB 6代码：
----------------------------------- ----------
选项明确
私有子Command1_Click（）
Dim objApp作为Excel。应用
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
''实例化Excel并启动一个新工作簿。
''设置objApp =新Excel.Application''这个工作
设置objApp = CreateObject（" Excel.Application"）''这个ALSO工作
设置objBooks = objApp.Workbooks
设置objBook = objBooks.Add
设置objSheets = objBook.Worksheets
设置objSheet = objSheets.Item（1）
设置objrange = objSheet.Range（" A1"）
''设置范围值。
objrange .Value =" Hello，World！"
''将Excel的控制权交还给用户。
objApp.Visible = True
objApp.UserControl = True
End Sub
-------------------------------------------- -
这是从VB 6代码生成的VB .NET代码：
------------------- --------------------------
期权严格关闭
期权明确On
朋友类Form1
继承System.Windows.Forms.Form
#Region" Windows窗体设计器生成的代码
Public Sub New（）
MyBase.New（）
如果m_vb6FormDefInstance什么都没有那么
如果m_InitializingDefInstance那么
m_vb6FormDefInstance =我
否则
尝试
''对于启动表单，创建的第一个实例是默认实例。
如果System.Reflection.Assembly.GetExecutingAssembly.En tryPoint.DeclaringType
是Me.GetType那么
m_vb6FormDefInstance = Me
结束如果
Catch
结束尝试
结束如果
结束如果
' 'Windows窗体设计器需要此调用。
InitializeComponent（）
End Sub
''表格覆盖dispose以清理组件清单。
受保护的重载覆盖子处理（ByVal处理为布尔值）
如果处置则
如果不是组件则没有那么
components.Dispose（）
结束如果
结束如果
MyBase.Dispose（Disposing）
结束Sub
''Windows窗体设计器要求
私有组件As System.ComponentModel.IContainer
Public ToolTip1 As System.Windows。 Forms.ToolTip
Public WithEvents Command1 As System.Windows.Forms.Button
''注意：Windows窗体设计器需要以下步骤
''可以使用Windows窗体设计器修改它。
''不要使用它修改它e代码编辑器。
< System.Diagnostics.DebuggerStepThrough（）> Private Sub InitializeComponent（）
Dim resources As System.Resources.ResourceManager = New
System.Resources.ResourceManager（GetType（Form1））
Me.components = New System.ComponentModel.Container（）
Me.ToolTip1 = New System.Windows.Forms.ToolTip（components）
Me.ToolTip1.Active = True
Me.Command1 = New System.Windows.Forms.Button
Me.Text =" Form1"
Me.ClientSize =新系统.Drawing.Size（312,213）
Me.Location = New System.Drawing.Point（4,23）
Me.StartPosition =
System.Windows.Forms.FormStartPosition.WindowsDefa ultLocation
Me.Font = New System.Drawing.Font（" Arial"，8！，
System.Drawing .FontStyle.Regular，System.Drawing.GraphicsUnit.Point，
CType（0，Byte））
Me.AutoScaleBaseSize = New System.Drawing.Size（5,13 ）
Me.BackColor = System.Drawing.SystemColors.Control
Me.FormBorderStyle = System.Windows .Forms.FormBorderStyle.Sizable
Me.ControlBox = True
Me.Enabled = True
Me.KeyPreview = False
Me.MaximizeBox = True
Me.MinimizeBox = True
Me.Cursor = System.Windows.Forms.Cursors.Default
Me.RightToLeft = System.Windows.Forms.RightToLeft.No
Me.ShowInTaskbar = True
Me.HelpButton = False
Me.WindowState = System.Windows.Forms.FormWindowState.Normal
Me.Name =" Form1"
Me.Command1.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
Me.Command1.Text =" Command1"
Me.Command1.Size = New System.Drawing.Size（73,73）
Me。 Command1.Location = New System.Drawing.Point（96,48）
Me.Command1.TabIndex = 0
Me.Command1.Font = New System.Drawing.Font （" Arial"，8！，
System.Drawing.FontStyle.Regular，System.Drawing.GraphicsUnit.Point，
CType（0，Byte））
Me.Command1.Bac kColor = System.Drawing.SystemColors.Control
Me.Command1.CausesValidation = True
Me.Command1.Enabled = True
Me.Command1 .ForeColor = System.Drawing.SystemColors.ControlText
Me.Command1.Cursor = System.Windows.Forms.Cursors.Default
Me.Command1.RightToLeft = System.Windows .Forms.RightToLeft.No
Me.Command1.TabStop = True
Me.Command1.Name =" Command1"
Me.Controls .Add（Command1）
End Sub
#End Region
#Region"升级支持"
私有共享m_vb6FormDefInstance为Form1
私有共享m_InitializingDefInstance为布尔值
公共共享属性DefInstance（）为Form1
获取
如果m_vb6FormDefInstance什么都没有OrElse m_vb6FormDefInstance.IsDisposed
那么
m_InitializingDefInstance = True
m_vb6FormDefInstance =新Form1（）
m_InitializingDefInstance = False
结束如果
DefInstance = m_vb6FormDefInstance
结束获取
设置
m_vb6FormDefInstance =价值
结束集
结束财产
#End Region
Private Sub Command1_Click（ByVal eventSender As System.Object，ByVal
eventArgs As System.EventArgs）处理Command1.Click
Dim objApp As Excel.Application
Dim objBook as Excel.Workbook
Dim objBooks作为Excel.Workbooks
Dim objSheets作为Excel.Sheets
Dim objSheet作为Excel.Worksheet
Dim objrange作为Excel.Range
''实例化Excel并启动一个新工作簿。
objApp =新Excel.Application（）''这工作
''objApp = CreateObject（" Excel.Application"）''这不起作用
objBooks = objApp.Workbooks
objBook = objBooks.Add
objSheets = objBook.Worksheets
objSheet = objSheets.Item（1）
objrange = objSheet.Range （A1）
''设置范围值。
objrange.Value =" Hello，World！"
' '将Excel的控制权交还给用户。
objApp.Visible = True
objApp.UserControl = True
End Sub
结束班级
-
http://www.standards.com/; 请参阅Howard Kaikow的网站。
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.
InitializeComponent()
''Add any initialization after the InitializeComponent() 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.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
''Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
''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.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.Button1 = New System.Windows.Forms.Button()
Me.SuspendLayout()
''
''Button1
''
Me.Button1.Location = New System.Drawing.Point(40, 40)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(176, 40)
Me.Button1.TabIndex = 0
Me.Button1.Text = "Button1"
''
''Form1
''
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(292, 273)
Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.Button1})
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim objApp As Excel.Application
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.Application() '' This works
''objApp = CreateObject("Excel.Application") '' This does NOT work
objBooks = objApp.Workbooks
objBook = objBooks.Add
objSheets = objBook.Worksheets
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.UserControl = True
End Sub
End Class
---------------------------------------------
Here is the VB 6 code:
---------------------------------------------
Option Explicit
Private Sub Command1_Click()
Dim objApp As Excel.Application
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.Application '' This works
Set objApp = CreateObject("Excel.Application") '' This ALSO works
Set objBooks = objApp.Workbooks
Set objBook = objBooks.Add
Set objSheets = objBook.Worksheets
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.UserControl = 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_vb6FormDefInstance Is Nothing Then
If m_InitializingDefInstance Then
m_vb6FormDefInstance = Me
Else
Try
''For the start-up form, the first instance created is the default instance.
If System.Reflection.Assembly.GetExecutingAssembly.En tryPoint.DeclaringType
Is Me.GetType Then
m_vb6FormDefInstance = Me
End If
Catch
End Try
End If
End If
''This call is required by the Windows Form Designer.
InitializeComponent()
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.Dispose()
End If
End If
MyBase.Dispose(Disposing)
End Sub
''Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
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.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Dim resources As System.Resources.ResourceManager = New
System.Resources.ResourceManager(GetType(Form1))
Me.components = New System.ComponentModel.Container()
Me.ToolTip1 = New System.Windows.Forms.ToolTip(components)
Me.ToolTip1.Active = 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.StartPosition =
System.Windows.Forms.FormStartPosition.WindowsDefa ultLocation
Me.Font = New System.Drawing.Font("Arial", 8!,
System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point,
CType(0, Byte))
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.BackColor = System.Drawing.SystemColors.Control
Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Sizable
Me.ControlBox = True
Me.Enabled = True
Me.KeyPreview = False
Me.MaximizeBox = True
Me.MinimizeBox = True
Me.Cursor = System.Windows.Forms.Cursors.Default
Me.RightToLeft = System.Windows.Forms.RightToLeft.No
Me.ShowInTaskbar = True
Me.HelpButton = False
Me.WindowState = System.Windows.Forms.FormWindowState.Normal
Me.Name = "Form1"
Me.Command1.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
Me.Command1.Text = "Command1"
Me.Command1.Size = New System.Drawing.Size(73, 73)
Me.Command1.Location = New System.Drawing.Point(96, 48)
Me.Command1.TabIndex = 0
Me.Command1.Font = New System.Drawing.Font("Arial", 8!,
System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point,
CType(0, Byte))
Me.Command1.BackColor = System.Drawing.SystemColors.Control
Me.Command1.CausesValidation = True
Me.Command1.Enabled = True
Me.Command1.ForeColor = System.Drawing.SystemColors.ControlText
Me.Command1.Cursor = System.Windows.Forms.Cursors.Default
Me.Command1.RightToLeft = System.Windows.Forms.RightToLeft.No
Me.Command1.TabStop = True
Me.Command1.Name = "Command1"
Me.Controls.Add(Command1)
End Sub
#End Region
#Region "Upgrade Support "
Private Shared m_vb6FormDefInstance As Form1
Private Shared m_InitializingDefInstance As Boolean
Public Shared Property DefInstance() As Form1
Get
If m_vb6FormDefInstance Is Nothing OrElse m_vb6FormDefInstance.IsDisposed
Then
m_InitializingDefInstance = True
m_vb6FormDefInstance = New Form1()
m_InitializingDefInstance = False
End If
DefInstance = m_vb6FormDefInstance
End Get
Set
m_vb6FormDefInstance = Value
End Set
End Property
#End Region
Private Sub Command1_Click(ByVal eventSender As System.Object, ByVal
eventArgs As System.EventArgs) Handles Command1.Click
Dim objApp As Excel.Application
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.Application() '' This works
''objApp = CreateObject("Excel.Application") '' This does NOT work
objBooks = objApp.Workbooks
objBook = objBooks.Add
objSheets = objBook.Worksheets
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.UserControl = True
End Sub
End Class
--
http://www.standards.com/; See Howard Kaikow''s web site.
推荐答案
霍华德，
如果你以一个可以使用的形式发布代码拉链，有人会更有可能尝试使用。
在查询中发布的长代码列表会因布局丢失而遭受损失，
包裹线条，纯粹看起来太压倒!!
我要睡觉了。 :-)
晚安，
Fergus
Hi Howard,
Someone''s more likely to try if you post a zip of the code in a form
that''s ready to use.
Long code listings posted within a query suffer from loss of layout,
wrapped lines, and sheer looking too overwhelming!!
I''m off to bed. :-)
Good night,
Fergus
最好看一下KB文章尝试重现我的所作所为。
-
http://www.standards.com/; 请参阅Howard Kaikow的网站。
" Fergus Cooney" <音响****** @ tesco.net>在消息中写道
新闻：uI ************* @ TK2MSFTNGP12.phx.gbl ...
It would be best to look at the KB article to try to reproduce what I did.
--
http://www.standards.com/; See Howard Kaikow''s web site.
"Fergus Cooney" <fi******@tesco.net> wrote in message
news:uI*************@TK2MSFTNGP12.phx.gbl...
嗨霍华德，
如果你以一个可以使用的形式发布代码的拉链，有人会更有可能尝试。
查询遭受布局的损失，包裹的线条，纯粹看起来太压倒!!
我要睡觉了。 :-)
晚安，
Fergus
Hi Howard,
Someone''s more likely to try if you post a zip of the code in a form
that''s ready to use.
Long code listings posted within a query suffer from loss of layout,
wrapped lines, and sheer looking too overwhelming!!
I''m off to bed. :-)
Good night,
Fergus
" Fergus Cooney" <音响****** @ tesco.net> schrieb
"Fergus Cooney" <fi******@tesco.net> schrieb
如果您以
形式发布代码的拉链，那么有人会更有可能尝试使用。
Someone''s more likely to try if you post a zip of the code in a
form
that''s ready to use.
Long code listings posted within a query suffer from loss of
layout,
wrapped lines, and sheer looking too overwhelming!!
它更多如果仅发布链接并且不附加
zip，则可能会被阅读。 （> 50KB =>被杀）;-)
-
Armin
It''s more likely to be read at all if posted only a link and do NOT attach a
zip. (>50KB => killed) ;-)
--
Armin
这篇关于从VB .NET自动化Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
