• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1500
  • Last Modified:

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

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
chwong67
Asked:
chwong67
  • 3
  • 3
1 Solution
 
DhaestCommented:
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
 
Miguel OzSoftware EngineerCommented:
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
 
chwong67Author Commented:
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Miguel OzSoftware EngineerCommented:
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
 
chwong67Author Commented:
Kindly advice on the right version of PIA that I can select for Excel.application. Thanks.
0
 
Miguel OzSoftware EngineerCommented:
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
 
chwong67Author Commented:
Thanks for your time. It's working now.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now