?
Solved

System.NullReferenceException for Excel.Workbooks.Open (visual Studio

Posted on 2010-01-11
7
Medium Priority
?
1,350 Views
Last Modified: 2013-11-26
Visual Studio 2003 error message.
Error message prompted "System.NullReferenceException: Object reference not set to an instance of an object.  at Microsoft.Office.Interop.Excel.Workbooks.Open" while open excel at user's computer (MS XP Service Pack 3, Office 2000).

It work fine at my development computer (NS XP Service Pack 2, Office 2007).

Part of coding:

Imports Microsoft.Office.Interop

Dim App As New Excel.Application
App.Visible = False
App.Application.SheetsInNewWorkbook = 1
Dim Destination As Excel.Workbook = App.Workbooks.Open("C:\Test.xls")

Any help is very much appreciated...
0
Comment
Question by:chwong67
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 26290900
Try this:

Dim Destination As NEW Excel.Workbook = App.Workbooks.Open("C:\Test.xls")

Example below with error-handling
Private Sub OpenExcelFileForEditing(ByVal strFilePath As String)
        Dim App As New Excel.Application
        Dim Destination As New Excel.Workbook 
        App.Visible = False
        App.Application.SheetsInNewWorkbook = 1

        'Open the workbook
        Try
            Destination = App.Workbooks.Open("C:\Test.xls")
        Catch ex As Exception
            MsgBox("Exception:  Model import, could not open model Design Table file: C:\Test.xls")
            Return
        End Try
    End Sub

Open in new window

0
 
LVL 36

Expert Comment

by:Miguel Oz
ID: 26291074
Please check that your client's Office has all the service packs.

By the way if your code is compiled for Office 2007 (version 12.0) then it will not work for Office 2000. The PIA (or com interfaces are different and not compatible)

If you need to support Office 2000, you need to compile your program with the office 2000 object library (version 9.0) meaning changing the excel references to point to the excel 2000 com components.


0
 
LVL 9

Author Comment

by:chwong67
ID: 26300013
Thanks for your prompt respond.
I re-install Miscoroft Office 2000 for Development computer and also intalled Office XP PIAs.
I faced the error message "QueryInterface for interface Excel._Application failed" for 'App.Visible = False' line.






0
Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

 
LVL 36

Expert Comment

by:Miguel Oz
ID: 26300042
Office 2000 (v9) do not use Office xp pia (v10).
Please point to the right version of com references.

Note:
By the way it is not a good idea to install /reinstall Office versions without clearing the registry from the previous Office entries as it may meess up your system. You are better off using a virtual machine (VMware) to do this stuff.
0
 
LVL 9

Author Comment

by:chwong67
ID: 26300102
Kindly advice on the right version of PIA that I can select for Excel.application. Thanks.
0
 
LVL 36

Accepted Solution

by:
Miguel Oz earned 2000 total points
ID: 26300903
For Office 2000 you need version 9 of the office object library. available in the com tab.
If you do not have it, use tlbimp to generate it.
Check:
http://msdn.microsoft.com/en-us/library/aa679806(office.11).aspx#officeinteroperabilitych2_part1_97and2000
Quoting from the link:
"In either case, the general pattern is to right-click the project in Solution Explorer and select Add Reference. Click the COM tab. Then select the appropriate type libraryfor example, for Excel you would select the following:
Office 2000: Microsoft Excel 9.0 Object Library

For Office 97 and Office 2000, this triggers TlbImp to generate a new IA based on the selected type library. You should also find that references have been added for the ancillary Office core and Visual Basic extensibility PIAs. Note that the minimum requirement is Office 97 with Service Release 1 (SR-1). The general behavior of the tools listed (as they are used from within Visual Studio .NET) is described in Table 2-2.
"

Note: Only Office XP onwards have precompiled PIA.
I know dealing with this version mess is a pain, but M$ did a poor job with office integration. The better versions to integrate are Office 2003 and 2007 - in my case we only support those two versions in our product. it is very risky to support and test multiple office versions + service packs + operative systems.
0
 
LVL 9

Author Closing Comment

by:chwong67
ID: 31675959
Thanks for your time. It's working now.
0

Featured Post

Create CentOS 7 Newton Packstack Running Keystone

A bug was filed against RDO for the installation of Keystone v3. This guide is designed to walk you through the configuration for using Keystone v3 with Packstack. You will accomplish this using various repos and the Answers file.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question