Problem with saving Excel from VB.NET

[kothaisaravan]

Hi,

I cant able to save an excel file from VB.NET.

I used the code below:

SaveFileDialog1.Title = "Save Excel File"
SaveFileDialog1.Filter = "Excel files (*.xls)|*.xls|Excel Files (*.xlsx)|*.xslx"
SaveFileDialog1.ShowDialog()
If (SaveFileDialog1.FileName <> "") Then
xlWorksheet.SaveAs(SaveFileDialog1.FileName)
End If

Error occurred in the highlighted line as "Exception from HRESULT: 0x800401A8".
I cannot analyse the cause for this error. because in before case it works fine for the same code i used.

Can anyone help?:confused:

[Phasma]

Hi kothaisaravan,

1. I'm sure that the file name you return from your dialog is not empty/nothing. So check that it isn't the short path. You cannot use the short path for a file say in your StartUp folder with Excel ex. 'workbook.xlsx'. Excel requires the full path to the file ex. 'D:\MyWorkBooks\workbook.xlsx'.

2. I have always used the xlWorkBook.SaveAs method. I do see in Intellisense that there is a SaveAs method for the WorkSheet, but I have never given it any consideration.

You could try the folowing:

xlWorkBook.SaveAs(SaveFileDialog1.FileName, _
                        FileFormat:=Excel.XlFileFormat.xlOpenXMLWorkbook, _
                        CreateBackup:=False)

Good Luck
Ken

[Reverend Jim]

You don't use SaveAs (or even Save) on a WorkSheet. You use it on a WorkBook. For example, the following code copies items from a listview into a new Excel WorkBook/WorkSheet then saves it to a file specified by the SaveFileDialog.

'browse for a file to save the data into

SaveFileDialog1.Title = "Save Excel File"
SaveFileDialog1.Filter = "Excel files (*.xls)|*.xls|Excel Files (*.xlsx)|*.xslx"
SaveFileDialog1.ShowDialog()

If SaveFileDialog1.FileName = "" Then
    Exit Sub
End If

'create an Excel WorkBook

Dim xls As New Excel.Application
Dim sheet As Excel.Worksheet

xls.Workbooks.Add()
sheet = xls.ActiveWorkbook.ActiveSheet

'save the listview items to the Excel WorkSheet

Dim row As Integer = 1
Dim col As Integer = 1

For Each item As ListViewItem In lvwToExport.Items
    For i As Integer = 0 To item.SubItems.Count - 1
	sheet.Cells(row, col) = item.SubItems(i).Text
	col += 1
    Next
    row += 1
    col = 1
Next

'save the WorkBook to a file and exit Excel

xls.ActiveWorkbook.SaveAs(SaveFileDialog1.FileName)
xls.Workbooks.Close()
xls.Quit()

[kothaisaravan]

Yes exactly i have to use workbook. save As().

Thank you Reverend Jim and Phasma. :-)