Link to home
Start Free TrialLog in
Avatar of rolltide0
rolltide0

asked on

Error opening an Excel file from VB 2005, Member not found. (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND))

I am trying to write to an excel file, then save it as a different name (preserving the initial file as a template). However I am getting the following error:

error: Member not found. (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND))
line : xlBook = xlApp.Workbooks.open("c:\ahr\ahr_tmp.xls")

Here is what I am trying to do
1) open c:\ahr\ahr_tmp.xls
2) add data from a datagridView (called resultsGrid)
3) save it as a different excel file with the date stamp.
4) I have considered using ado.net to access the file, but I cannot find any documentation on how to save the file under a different filename, just updating an existing one.

I am sure there are other problems that could be with this code, but I cannot get past that one line to find them. I have imported the excel resources needed for this project. As you might have guessed, I have never tried this before. The code for the entire sub is listed below. I have also tried using CType in creating the objects but that resulted in the same error. Any help would be appreciated.

Thanks in advance.

Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim rowLoop As Integer = 0
        Dim colLoop As Integer = 0
        xlApp = CreateObject("Excel.Application")
        xlBook = xlApp.Workbooks.open("c:\ahr\ahr_tmp.xls")
        xlSheet = xlBook.Worksheets(1)
 
        With xlSheet.Application
            Do While rowLoop < resultsNumber
                colLoop = 1
                Do While colLoop < 40
                    .Cells(rowLoop + 1, colLoop) = resultsGrid.Rows(rowLoop).Cells(colLoop).Value
                    colLoop = colLoop + 1
                Loop
                rowLoop = rowLoop + 1
            Loop
            Dim dateStamp As Date = Date.Now
            Dim fName As String = "c:\ahr\ahr_rpt_" & dateStamp & ".xls"
            xlBook.SaveAs(fName)
            .quit()
        End With

Open in new window

Avatar of David L. Hansen
David L. Hansen
Flag of United States of America image

Try copying the existing excel file to a new excel file first (you'll then have two identical files).  Then apply the new data to the newest file and save it.
Avatar of rolltide0
rolltide0

ASKER

Nope didn't work. Keep in mine this isnt vba inside excel, rather than a vb app trying to write data to an excel file.


Try this way ...
 

Add this imports:

Imports System.Threading.Thread
Imports System.Globalization  


Then use this before the CreateObject:

Dim oldCI As CultureInfo = CurrentThread.CurrentCulture
CurrentThread.CurrentCulture = New CultureInfo("en-US")  
 

And in the end of your code:

CurrentThread.CurrentCulture = oldCI
Were you able to create the new duplicate excel document at all?
Here you have more information: http://support.microsoft.com/kb/320369
My apologies sl8, I just can't figure out what you are trying to get me to do. I use the SaveAs command to save the initial file as a different one, but the code isn't getting that far.

JP, I added those lines to the code, but the error still persists.
What version of excel file are you trying to open ? Can you show the code after the changes ?
Try this:
https://www.experts-exchange.com/questions/22110536/Create-an-excel-sheet-programmatically-using-VB-NET.html

If you still can't get it to work, I'll be happy to crunch through the code with you line by line.
Here is the modified code that i am working with now. The excel file I believe was created in excel 2003, and the version of Excel I have on my workstation is 2007 . Sl8, thanks for the link, I will play with that for a bit and post my results.
     Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim rowLoop As Integer = 0
        Dim colLoop As Integer = 0
        Dim oldCI As CultureInfo = CurrentThread.CurrentCulture
        CurrentThread.CurrentCulture = New CultureInfo("en-US")
 
        xlApp = CreateObject("Excel.Application")
        xlBook = xlApp.Workbooks.open("c:\ahr\ahr_tmp.xls")
        xlSheet = xlBook.Worksheets(1)
 
        With xlSheet.Application
            Do While rowLoop < resultsNumber
                colLoop = 1
                Do While colLoop < 40
                    .Cells(rowLoop + 1, colLoop) = resultsGrid.Rows(rowLoop).Cells(colLoop).Value
                    colLoop = colLoop + 1
                Loop
                rowLoop = rowLoop + 1
            Loop
            Dim dateStamp As Date = Date.Now
            Dim fName As String = "c:\ahr\ahr_rpt_" & dateStamp & ".xls"
            xlBook.SaveAs(fName)
            .quit()
        End With
        CurrentThread.CurrentCulture = oldCI
    End Sub

Open in new window

Still having the same issues. I looked at the code in the link, and it appears that they are opening the excel workbook the same way I am, by using the workbooks.open(filename) method. This is where my program is throwing an error. I didn't look at the rest of his code because it was pointless if I cannot make it past that part. It is amazing how little documentation is on this error.
I think it might be a problem with your file.  The code worked fine for me.  I only tried to get to the point of the "Workbooks.Open" line mind you, but it did work.  Note: I opened my excel file first manually and saved it as a plain-vanilla "97-2003 excel document" then ran the code...stepping through one line at a time.
wow that really is frustrating. I have saved and re-saved that file, tried different locations. I even created a new file, saved it as an excel 97-2003 file and still didn't work. I should have been a lawyer, less frustrating.
Tell me...what is your imports statements and references relative to MS Office in your app.
No specific import statements. The web site I got the instructions told me to go to Project, add reference, under COM tab add Microsoft Excel Object Library (I added 12.0 and 5.0).
ASKER CERTIFIED SOLUTION
Avatar of David L. Hansen
David L. Hansen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Wow that did it. Removing the 5.0 reference and changing the way the declarations did the trick. Thanks to both!!!!