Learn how to a build a cloud-first strategyRegister Now

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

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

0
rolltide0
Asked:
rolltide0
  • 7
  • 6
  • 4
2 Solutions
 
David L. HansenProgrammer AnalystCommented:
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.
0
 
rolltide0Author Commented:
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.
0
 
jpaulinoCommented:


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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
David L. HansenProgrammer AnalystCommented:
Were you able to create the new duplicate excel document at all?
0
 
jpaulinoCommented:
Here you have more information: http://support.microsoft.com/kb/320369
0
 
rolltide0Author Commented:
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.
0
 
jpaulinoCommented:
What version of excel file are you trying to open ? Can you show the code after the changes ?
0
 
David L. HansenProgrammer AnalystCommented:
Try this:
http://www.experts-exchange.com/Programming/Languages/.NET/Q_22110536.html

If you still can't get it to work, I'll be happy to crunch through the code with you line by line.
0
 
rolltide0Author Commented:
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

0
 
rolltide0Author Commented:
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.
0
 
David L. HansenProgrammer AnalystCommented:
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.
0
 
rolltide0Author Commented:
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.
0
 
David L. HansenProgrammer AnalystCommented:
Tell me...what is your imports statements and references relative to MS Office in your app.
0
 
rolltide0Author Commented:
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).
0
 
David L. HansenProgrammer AnalystCommented:
Remove 5.0 ...just use 12.0
0
 
jpaulinoCommented:
Ok, you're messing two concepts: early and late binding.
Try to use only one by changing the declarations to object and remove the references to Excel.
        Dim xlApp As Object
        Dim xlBook As Object
        Dim xlSheet As Object
0
 
rolltide0Author Commented:
Wow that did it. Removing the 5.0 reference and changing the way the declarations did the trick. Thanks to both!!!!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now