472,778 Members | 2,460 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,778 software developers and data experts.

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.
Nov 20 '05 #1
22 15234
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
Nov 20 '05 #2
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...
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

Nov 20 '05 #3
"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!!

It's more likely to be read at all if posted only a link and do NOT attach a
zip. (>50KB => killed) ;-)

--
Armin

Nov 20 '05 #4
Cor
Fergus,
You fool, you 'v been up till 8 O'clock in the morning helping people.
Is that not a little bit overdone?
:-)
Cor
Nov 20 '05 #5
Hello,

"Armin Zingler" <az*******@freenet.de> schrieb:
It's more likely to be read at all if posted only a link and do NOT attach a zip. (>50KB => killed) ;-)


Really interesting:

http://msdn.microsoft.com/newsgroups...Pages/tips.asp

The page above says that attachments <= 1 MB can be added, the help document
for the German ngs says that no attachments should be added at all.

;-)

I *hate* attachments.

Regards,
Herfried K. Wagner
--
MVP VB Classic, VB .NET
http://www.mvps.org/dotnet
Nov 20 '05 #6
Hi Cor,

|| You fool, you've been up till 8 o'clock in the morning helping people.

Lol. Guilty as charged, my friend. I <love> doing this. It combines my
programming skills with my desire to be a teacher (although I'm not a teacher,
it's in the blood), and my love for helping people.

As I was saying to Nick (private communication) "the buzz from people's
thanks - I love it. Born helper, me. ... the humour level keeps me well
chuckling :-D."

|| Is that not a little bit overdone?

Oh yes, absolutely. At the moment I'm unemployed , so the time is there,
sort of. However...

Again from my note to Nick "It's incredibly addictive. Every day I say
"right - got to stop this and get looking for work" ... I won't be able to
sustain it the way that I have. I'd love to do it as a job though. Shame it's
voluntary in a way. "

This is - has to be - only a temporary phase. I <do> need to get work. My
intention was to do this for about a week or so at the level that I have, and
then back off. It's just <so much fun> though.

|| :-)

:-) to you too. Thank you. I appreciate your concern.

Best of,
Fergus

ps. Gissajob, lol.
Nov 20 '05 #7
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

Nov 20 '05 #8
Cor
I am in exactly the same situation as you
Nov 20 '05 #9
Hello,

"Fergus Cooney" <fi******@tesco.net> schrieb:
A zip of a working module/class/form is much more useful. As for size - a
zip of textual code is shorter than the code when posted. For example, my bug report is a complete solution, including resx file and weighs in at under 7K.

Simetimes ZIP files are really useful, but I think it's better to upload the
file to a webserver and post the link.
|| I *hate* attachments

A bit of advice, 'cos I like you, - don't tell your fiance that one. She's
already planned the wedding down to the last detail, and is just waiting for the next leap year!!


LOL

Regards,
Herfried K. Wagner
--
MVP VB Classic, VB .NET
http://www.mvps.org/dotnet
Nov 20 '05 #10
Hello,

"Fergus Cooney" <fi******@tesco.net> schrieb:
|| 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


Good night.

;-)

Regards,
Herfried K. Wagner
--
MVP VB Classic, VB .NET
http://www.mvps.org/dotnet
Nov 20 '05 #11
Cheerzzz, Herfried, ZZzzzz, ZZzzzz :-)
Nov 20 '05 #12
Cor
LOL
Nov 20 '05 #13
Cor
What server, yours?
Nov 20 '05 #14
Cor
It is saterday Herfried, you know it wein und weinbrand :-)
Nov 20 '05 #15
Hello,

"Fergus Cooney" <fi******@tesco.net> 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
Nov 20 '05 #16
Hello,

"Cor" <no*@non.com> schrieb:
What server, yours?


A webserver.

Regards,
Herfried K. Wagner
--
MVP VB Classic, VB .NET
http://www.mvps.org/dotnet
Nov 20 '05 #17
Hi Herfried,

No, you're right - I'm sleep-newsgrouping!!. Lol.

Regards,
Fergus
Nov 20 '05 #18
ROFL
Nov 20 '05 #19
See the Temporary Links at http://www.standards.com/index.html#Temporary.

--
http://www.standards.com/; See Howard Kaikow's web site.
"Fergus Cooney" <fi******@tesco.net> wrote in message
news:uI*************@TK2MSFTNGP12.phx.gbl...
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

Nov 20 '05 #20
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.
Nov 20 '05 #21
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.
Nov 20 '05 #22
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" <ka****@standards.com> wrote in message
news:eA*************@tk2msftngp13.phx.gbl...
See the Temporary Links at http://www.standards.com/index.html#Temporary.

--
http://www.standards.com/; See Howard Kaikow's web site.
"Fergus Cooney" <fi******@tesco.net> wrote in message
news:uI*************@TK2MSFTNGP12.phx.gbl...
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


Nov 20 '05 #23

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Nick Carter | last post by:
The following C# lines of code work fine with Excel 2000:- ExcelApplication excelApplication = new Excel.Application(); ExcelWorkbook excelWorkbook =...
0
by: Laurence | last post by:
Hi Folks hope someone can help me out with this wee problem. I'm automating Excel from A2K and want to put a solid black border around a range of cells. I've tried using this: ...
0
by: David Caissie | last post by:
I am trying to automate a Vb.net chart using the excel COM object. The problem occurs when trying to run the code in different systems. It is working fine under one of my computers but once i move...
1
by: chris_j_adams | last post by:
Hi, I'm trying to use Excel VBA ('97) to send details from an Excel sheet to a web page. I'm having some success but I've one issue that's proved difficult to find in the archives. There are...
2
by: Elliot | last post by:
After executing the following line: WB = XL.Workbooks.Open("c:\test.xls") Excel is visible using vb.net 2005. Is this a change from previous versions of VB? I know I can use xl.visible=false...
3
by: saragoesnuts | last post by:
I have a program that automates to Excel with information in tables. I want to add charts to the excel file (pie charts, bar charts, etc). Is this possible?
2
by: cmp80 | last post by:
Hi everyone, I will need to start importing data from an XML file into our Access database on a daily basis. I have tried to import the data directly into a table, but it separates out into...
19
by: LucasLondon | last post by:
Hi there, First of all apologies for the long post. Hope someone can offer some advice. I have about 200 columns of time series data that I need to perform a correlation analysis on in terms...
2
by: nandishp | last post by:
We need to automate download of Reports from Oracle CRM OnDemand. The reports in Siebel CRM OnDemand can be downloaded in the form of Excel, CSV, etc. We have a task of downloading several such...
0
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.