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:\a hr\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.
error: Member not found. (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND))
line : xlBook = xlApp.Workbooks.open("c:\a
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
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.
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.CurrentCultu
CurrentThread.CurrentCultu
And in the end of your code:
CurrentThread.CurrentCultu
Were you able to create the new duplicate excel document at all?
Here you have more information: http://support.microsoft.com/kb/320369
ASKER
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.
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.
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.
ASKER
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
ASKER
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.
ASKER
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.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow that did it. Removing the 5.0 reference and changing the way the declarations did the trick. Thanks to both!!!!