Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb
|
|||
|
|||
Automating Excel from VB .NET
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb
|
|||
|
|||
Automating Excel from VB .NET
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. |
#3
Posted to microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb
|
|||
|
|||
Automating Excel from VB .NET
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb
|
|||
|
|||
Automating Excel from VB .NET
Fergus,
You fool, you 'v been up till 8 O'clock in the morning helping people. Is that not a little bit overdone? :-) Cor |
#5
Posted to microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb
|
|||
|
|||
Automating Excel from VB .NET
Hi Howard,
|| It would be best to look at the KB article to try || to reproduce what I did. Best for <you, sure, but like I said, I'm to bed. Regards, Fergus's astral body in temporary corporate form |
#6
Posted to microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb
|
|||
|
|||
Automating Excel from VB .NET
LOL
|
#7
Posted to microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb
|
|||
|
|||
Automating Excel from VB .NET
Cheerzzz, Herfried, ZZzzzz, ZZzzzz :-)
|
#8
Posted to microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb
|
|||
|
|||
Automating Excel from VB .NET
Hello,
"Fergus Cooney" schrieb: Cheerzzz, Herfried, ZZzzzz, ZZzzzz :-) I think I misinterpreted your posting. ;-))) Regards, Herfried K. Wagner -- MVP · VB Classic, VB .NET http://www.mvps.org/dotnet |
#9
Posted to microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb
|
|||
|
|||
Automating Excel from VB .NET
Hi Herfried,
No, you're right - I'm sleep-newsgrouping!!. Lol. Regards, Fergus |
#10
Posted to microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb
|
|||
|
|||
Automating Excel from VB .NET
See the Temporary Links at http://www.standards.com/index.html#Temporary.
-- http://www.standards.com/; See Howard Kaikow's web site. "Fergus Cooney" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb
|
|||
|
|||
Automating Excel from VB .NET
I found a workaround which seems to imply a bug in VB .NET.
Can others reproduce this behavior using the example in the Temporary Links at http://www.standards.com/index.html#Temporary? Using that example: The following creates an Excel object and allows the code to properly execute. oXL = New Excel.Application() ' Works The following does not allow the code to properly execute: oXL = CreateObject("Excel.Application") The following, or equivalent, allows the code to properly execute: oXL = CreateObject("Excel.Application")'On Error Resume Next If 1 < 1 Then oXL = New Excel.Application() oXL = GetObject(, "Excel.Application") End If At first glance, the implication is that some library/code is not getting loaded without including the code in the never executed If ... End If. -- http://www.standards.com/; See Howard Kaikow's web site. |
#12
Posted to microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb
|
|||
|
|||
Automating Excel from VB .NET
I found a workaround which seems to imply a bug in VB .NET.
Can others reproduce this behavior using the example in the Temporary Links at http://www.standards.com/index.html#Temporary? Using that example: The following creates an Excel object and allows the code to properly execute. oXL = New Excel.Application() ' Works The following does not allow the code to properly execute: oXL = CreateObject("Excel.Application") The following, or equivalent, allows the code to properly execute: oXL = CreateObject("Excel.Application") If 1 < 1 Then oXL = New Excel.Application() oXL = GetObject(, "Excel.Application") End If At first glance, the implication is that some library/code is not getting loaded without including the code in the never executed If ... End If. -- http://www.standards.com/; See Howard Kaikow's web site. |
#13
Posted to microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb
|
|||
|
|||
Automating Excel from VB .NET
Howard,
I use the code successfully all the time. Not sure why you have a problem with CreateObject. ============================== Dim objXL As Object Dim objWBS As Object Dim objWB As Object Dim objWS As Object Dim mRow As DataRow Dim colIndex As Integer Dim rowIndex As Integer Dim col As DataColumn Try 'get a running instance of Excel objXL = GetObject(, "Excel.Application") Catch ex As Exception 'create a new instance of Excel if there isn't one running. objXL = CreateObject("Excel.Application") End Try -- Joe Fallon "Howard Kaikow" wrote in message ... See the Temporary Links at http://www.standards.com/index.html#Temporary. -- http://www.standards.com/; See Howard Kaikow's web site. "Fergus Cooney" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automating a control box in excel | Excel Worksheet Functions | |||
Automating Excel | Excel Discussion (Misc queries) | |||
Automating Excel w VB.Net 2003 | Excel Discussion (Misc queries) | |||
Automating Excel on a Server | Excel Programming | |||
Automating Acrobat from Excel | Excel Programming |