Excel Automation with VB.Net 
Author Message
 Excel Automation with VB.Net

I am trying to open a tab delimited text file (nd.txt) via a button click
with VB .Net.  I have tried it with a reference to the Office 9 Object model
and Office 10 Model.  It works perfectly on a machine with Office XP
installed but I cannot get it to work on a machine with office 2000.  Here
is a code sample,  Any help would be greatly appreciated.  The failure is on
the "Open" call to the Workbooks object.

The error I am getting is "Object Reference not set to an instance of an
object."

 Is this a known problem?

Dim xlapp As Excel.Application

Dim xlbook As Excel.Workbook

Dim obooks As Excel.Workbooks

xlapp = New Excel.Application()

obooks = xlapp.Application.Workbooks

xlbook = obooks.Open(Application.StartupPath & "\nd.txt")

xlapp.Visible = True



Wed, 01 Dec 2004 02:26:32 GMT  
 Excel Automation with VB.Net

I am trying to open a tab delimited text file (nd.txt) via a button click
with VB .Net.  I have tried it with a reference to the Office 9 Object model
and Office 10 Model.  It works perfectly on a machine with Office XP
installed but I cannot get it to work on a machine with office 2000.  Here
is a code sample,  Any help would be greatly appreciated.  The failure is on
the "Open" call to the Workbooks object.

The error I am getting is "Object Reference not set to an instance of an
object."

 Is this a known problem?


Dim xlapp As Excel.Application

Dim xlbook As Excel.Workbook

Dim obooks As Excel.Workbooks

xlapp = New Excel.Application()

obooks = xlapp.Application.Workbooks

xlbook = obooks.Open(Application.StartupPath & "\nd.txt")

xlapp.Visible = True

Does the code work from another automation client on the same machine. It's
possible there is a problem with the registration of the Excel COM objects.


Microsoft MVP (Visual Basic)



Wed, 01 Dec 2004 03:03:02 GMT  
 Excel Automation with VB.Net
Paul,
Thanks for responding...I'm not sure exactly what you mean.  Here's what I
tried:

Created a VB .Net windows form app Added the Excel Object Lib reference,
which also adds the Office.dll reference and VBIDE.dll reference
automatically.

Put in the code to open the excel sheet and compiled.  It workes fine on the
machines I a developing on (both have office XP), but when I take the .EXE
and the DLL's to a machine with Office 2000, the .Net app executes, but
blows up on the .Open Method call.

The machines with Office 2000 do not have VB .Net installed, the machines
with Office XP do have VB .Net installed.  Maybe something is registered
with the installation of VB .Net, is that what you are thinking?

I thought as long as I copied the .DLL's with the compiled .exe to a client
machine with .Net Framework and Office installed I did not have to worry
about registering any DLL manually.

If I do not call the Open method, Excel is displayed and is visible, but of
course has no open document.

Do I need to do something special on my clients to ensure the Excel COM
objects are registered correctly?  What do you mean by "Another automation
client"?

-J

Quote:
> Does the code work from another automation client on the same machine.
It's
> possible there is a problem with the registration of the Excel COM
objects.


> Microsoft MVP (Visual Basic)



Wed, 01 Dec 2004 03:46:37 GMT  
 Excel Automation with VB.Net

Quote:
>The failure is on
>the "Open" call to the Workbooks object.

>The error I am getting is "Object Reference not set to an instance of an
>object."

Do you know if it's the actual Open call that throws the exception, or
if it could be Application.StartupPath that does it? Have you verified
that obooks isn't Nothing?

Mattias

===
Mattias Sj?gren (VB MVP)

http://www.msjogren.net/dotnet/



Wed, 01 Dec 2004 06:21:04 GMT  
 Excel Automation with VB.Net
  If I do a message box on oBooks.count I get zero (0) as a count so the
workbooks object is there and is not nothing.  And I can display the
applicaiton.startupPath in a message box and verified that that is correct.
I even moved my file to c:\temp\nd.txt and hard coded the path to the file
with no success.

I personally think it works differently because something is registered
differently on the Office 2000 machines or the machines with VS .Net
installed.  Like I said it works fine on my Office XP machines.

Any other ideas?

-J


Quote:

> >The failure is on
> >the "Open" call to the Workbooks object.

> >The error I am getting is "Object Reference not set to an instance of an
> >object."

> Do you know if it's the actual Open call that throws the exception, or
> if it could be Application.StartupPath that does it? Have you verified
> that obooks isn't Nothing?

> Mattias

> ===
> Mattias Sj?gren (VB MVP)

> http://www.msjogren.net/dotnet/



Wed, 01 Dec 2004 06:26:15 GMT  
 Excel Automation with VB.Net
J,

Quote:
>I personally think it works differently because something is registered
>differently on the Office 2000 machines or the machines with VS .Net
>installed.  Like I said it works fine on my Office XP machines.

It's possible. But if something was missing or not registered, I'd
expect a different kind of error. Unfortunately I don't have the same
setup here so I can't test it.

Quote:
>Any other ideas?

Wrap the Open call in a Try/Catch block so you can trap the exception
and print out more detailed information about it, inclusing stack
trace and any inner messages. That could perhaps provide some clue.

If you can, try to do the same thing in for example a VB6 app and see
if you that gives you an error too. That could help you isolate the
problem to either Office or (VB).NET.

Mattias

===
Mattias Sj?gren (VB MVP)



Wed, 01 Dec 2004 07:19:51 GMT  
 Excel Automation with VB.Net
Hi,

I've encountered the same thing myself.  The problem is caused by a
change to the type libraries between Excel 9 and 10.  In VB6 this is
not a problem because it uses only the Dispatch interface to get at
functions (i.e., it looks up functions at run-time as needed).  If you
early bind in C# (or VB.NET), you don't use the Dispatch interface,
but rather go straight for the function.  This can lead to
compile-time or run-time errors.  Thus, since Excel 10 has more
arguments to its Open function than Excel 9 does, you cannot make the
two functions accessible from the same executable if you early bind
your function calls (which is, of course, the only way to get any
context completion or other programming assistance while traversing
the labyrinth of Excel's object model).

See the Microsoft article at
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q302084 for
some helpful info. on automating Excel in C#.NET.  Notice that they
use different code for Excel 9 than for Excel 10.  The only way around
this is to do late binding.  I have not tried late binding in C#, but
the sample code I've read looks kinda ugly (especially since I
switched from C to VB about 5 years ago).

I suggest you keep poking around the Microsoft sites for C# and Office
Automation to see if you can find anything else in their "knowledge
base" that is worthwhile.

I have also found it extremely helpful to code in parallel between VB6
and C# for Excel Automation development.  I essentially write the code
twice; first in VB6 where I have integrated help and (IMHO) better
context info., then "translate" to C# code for product development.

No matter what you do it's still messy trying to automate Office
stuff, but at least it works once you get it running.

Good luck & HAVE FUN!

   -- John

P.S.  I've heard a rumor about Microsoft creating wrapper classes in
.NET for Office automation.  I don't know if it's true or what the
timeline would be, but that sure would help.

Quote:

> J,

> >I personally think it works differently because something is registered
> >differently on the Office 2000 machines or the machines with VS .Net
> >installed.  Like I said it works fine on my Office XP machines.

> It's possible. But if something was missing or not registered, I'd
> expect a different kind of error. Unfortunately I don't have the same
> setup here so I can't test it.

> >Any other ideas?

> Wrap the Open call in a Try/Catch block so you can trap the exception
> and print out more detailed information about it, inclusing stack
> trace and any inner messages. That could perhaps provide some clue.

> If you can, try to do the same thing in for example a VB6 app and see
> if you that gives you an error too. That could help you isolate the
> problem to either Office or (VB).NET.

> Mattias

> ===
> Mattias Sj?gren (VB MVP)



Sat, 04 Dec 2004 02:46:21 GMT  
 Excel Automation with VB.Net

Paul,
Thanks for responding...I'm not sure exactly what you mean.  Here's what I
tried:

Created a VB .Net windows form app Added the Excel Object Lib reference,
which also adds the Office.dll reference and VBIDE.dll reference
automatically.

Put in the code to open the excel sheet and compiled.  It workes fine on the
machines I a developing on (both have office XP), but when I take the .EXE
and the DLL's to a machine with Office 2000, the .Net app executes, but
blows up on the .Open Method call.

The machines with Office 2000 do not have VB .Net installed, the machines
with Office XP do have VB .Net installed.  Maybe something is registered
with the installation of VB .Net, is that what you are thinking?

I thought as long as I copied the .DLL's with the compiled .exe to a client
machine with .Net Framework and Office installed I did not have to worry
about registering any DLL manually.

If I do not call the Open method, Excel is displayed and is visible, but of
course has no open document.

Do I need to do something special on my clients to ensure the Excel COM
objects are registered correctly?  What do you mean by "Another automation
client"?

In this instance the .NET app is your automation client. What I was trying to do
is eliminate the .NET app from the equation. If you can run the code (via
automation) from a different client, such as VB 6.0 or Microsoft Access, we
might be able to narrow down the problem to either .NET or Excel (object
library, object registration, etc.).


Microsoft MVP (Visual Basic)



Sat, 04 Dec 2004 23:22:40 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Need help doing a Subtotal through Excel automation in vb.net

2. VBA Excel Automation in VB.Net

3. Excel Addin using vb.net Automation

4. Excel Ole automation with VB.NET

5. Excel Automation via .NET

6. Accessing VBIDE.VBE.VBProjects via Excel in VB .NET Causes Excel To Crash When App Terminates

7. Accessing VBIDE.VBE.VBProjects via Excel in VB .NET Causes Excel To Crash When App Terminates

8. Accessing VBIDE.VBE.VBProjects via Excel in VB .NET Causes Excel To Crash When Closed

9. Access to Excel Automation - Release Excel Object

10. Excel Automation Client using VB

11. VB - EXCEL automation error.... Help Needed

12. VB, Automation and Excel

 

 
Powered by phpBB® Forum Software