asked on

VB.NET - Member Not Found error when opening .xlsx file

Using VB.NET in VS2008, I'm trying to read/write from an Excel 2007 .xlsx file. I've included COM references to the Microsoft Excel 12.0 and 5.0 Object Libraries.

Every time I try to run the program, though, I'm getting a Member Not Found error (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND)).

Here's the code that's throwing the error; it happens on the second line:

Dim xcApp As Excel.Application = CreateObject("Excel.Application")
Dim xcWB As Excel.Workbook = xcApp.Workbooks.Open("C:\TestFile.xlsx")
Dim xcWS As Excel.Worksheet = CType(xcWB.Worksheets(1), Excel.Worksheet)

The file C:\TestFile.xlsx does exist, so that's not what's causing it. Does anyone have any idea what I'm doing wrong? Thanks in advance for your help.

Nasir Razzaq

>Dim xcApp As Excel.Application = CreateObject("Excel.Application")

That's the VB/VBA way of doing it

Change it to

Dim xcApp As New Excel.Application

LCMSdev

ASKER

Heh...that's what I originally had, but it gave me the error: 'New' cannot be used on an interface.

When I looked up that error, I found advice to change it to what I have now, and that line no longer gives an error. Would that be the cause of the Member Not Found error?

SiddharthRout

Try this. I just typed it in notepad so please let me know if you get any errors.

Option Explicit On
Option Strict On
'Add a reference to MS Excel xx.xx Object Library
'Import the Excel Primary Interop Assembly
Imports Microsoft.Office.Interop

Public Class Form1
    Private xcApp As Excel.Application
    
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles Button1.Click
        '~~> Declare and create a Workbook object
        Dim xcWB As Excel.Workbook = xcApp.Workbooks.Open("C:\TestFile.xlsx")
        '~~> Make it Visible to the user now that we are going to use it
        xcApp.Visible = True
        Dim xcWS As Excel.Worksheet = DirectCast(xcWB.Worksheets(1), Excel.Worksheet)
        
        '~~> Rest of the code
    End Sub
End Class

Open in new window

Sid

LCMSdev

ASKER

SiddharthRout,

Thanks, I will test that. Just to clarify, though, I don't want the Excel file to be visible to the user...I'm just using it as a sort of back-end data storage so I don't have to mess with SQL for a quick app. Will your code still work if I delete the xcApp.Visible = True line?

miketonny

you can use a data connection to create excel, i use that to avoiding weird errors.

       Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & impStr & ";Extended Properties=Excel 8.0;")

        ExcelConnection.Open()

        Dim ImportCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Sheet1] FROM [Text;DATABASE=" & g_ExcelFilePath & "].[" & excelStr & ".xls]", ExcelConnection)

        ImportCommand.ExecuteNonQuery()
        ExcelConnection.Close()

Open in new window

hope that helps

SiddharthRout

>>>I don't want the Excel file to be visible to the user...

In that case change

xcApp.Visible = True

to

xcApp.Visible = False

Sid

Rory Archibald

As a matter of interest why/how do you have a reference to Excel 5.0 set? As far as I know, there is no PIA for Excel 5.

LCMSdev

ASKER

Sid — Tested your code; I'm getting a compile error "Option Strict On disallows late binding" on the line: Dim xcWB As Excel.Workbook = xcApp.Workbooks.Open("C:\TestFile.xlsx"). I'm also getting the same thing later on when I'm assigning values to ranges, e.g.:

Dim xcRange as Excel.Range
[some stuff]
xcRange = xcWS.Range("J1") //this line gives the Option Strict error

If I turn off Option Strict, I then get "Object reference not set to instance of an object" on that same Dim xcWB line at runtime.

rorya — I can probably remove that reference now; I added it because I started with old VB code where it was necessary. As is probably obvious, I'm pretty new to .NET.

ASKER CERTIFIED SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LCMSdev

ASKER

Brilliant! Many thanks.