[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1209
  • Last Modified:

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.
0
LCMSdev
Asked:
LCMSdev
1 Solution
 
CodeCruiserCommented:
>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
0
 
LCMSdevAuthor 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?
0
 
SiddharthRoutCommented:
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LCMSdevAuthor 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?
0
 
miketonnyCommented:
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
0
 
SiddharthRoutCommented:
>>>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
0
 
Rory ArchibaldCommented:
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.
0
 
LCMSdevAuthor 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.
0
 
SiddharthRoutCommented:
Can you show me the complete code?

This is TRIED and TESTED.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles Button1.Click
        Dim xcApp As Excel.Application
        Dim xcWB As Excel.Workbook
        Dim xcWS As Excel.Worksheet

        xcApp = New Excel.ApplicationClass
        xcWB = xcApp.Workbooks.Open("C:\TestFile.xlsx")
        xcWS = xcWB.Worksheets("sheet1")

        '~~> Type Something in Cel A1
        xcWS.Cells(1, 1).value = "Hello World"
        xcWB.Close(True)
        xcApp.Quit()

        releaseObject(xcApp)
        releaseObject(xcWB)
        releaseObject(xcWS)
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

Open in new window


Sid
0
 
LCMSdevAuthor Commented:
Brilliant! Many thanks.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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