We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Medium Priority
1,472 Views
Last Modified: 2012-05-11
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.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
>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

Author

Commented:
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?
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

Author

Commented:
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?
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
>>>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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
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.

Author

Commented:
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.
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Brilliant! Many thanks.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.