How to save a file data using the savefiledialog?

[mitkram]

Hi Friends!

I need your help in VB 2008 Express Ed. I was trying to export a data from the listview control into an excel format and I was able to make it successfully using a command button directly. I want to use a SaveFileDialog to export and save the content of the listview. I searched from MSDN website and got a sample code which was originally intended for saving an image file. I tried to used it and edit the code and combine my code to export the data content. It ran successfully and attempt to export the data in an excel format but when you open the file in the path as to where you save the data, it gives you a message "Excel cannot open the file 'Filename.xlsx' because the file format or extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file." and it just leaves an empty excel file. I read from the MSDN site that if you are using an MS Office 2007, the file extension for excel would be '.xlsx' and not 'xls'. Let me give you the two codes I tried to use. First, I used command button and was able to successfully export and save the content in an excel format but using the SaveFileDialog was not successful. I hope someone could help me. I suspected that there's something wrong within this line 'oBook.SaveAs("saveFileDialog1.FileName.GetType()")' and 'Dim fs As System.IO.FileStream = CType _(saveFileDialog1.OpenFile(), System.IO.FileStream)'.

Here are the two diffrent codes I used:

'Using the command button-----successful
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim lview As ListViewItem
Dim lview2 As ListViewItem.ListViewSubItem

Dim row, col As Integer

row = 3
col = 3

'Start a new workbook in Excel
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)


For Each lview In ListView1.Items

oSheet.Cells(row, col) = lview.Text

For Each lview2 In lview.SubItems

oSheet.Cells(row, col) = lview2.Text
col = col + 1

Next
col = 3
row = row + 1
Next

'Save the Workbook and Quit Excel
oBook.SaveAs("D:\Links\Book1.xlsx")
oExcel.Quit()

MsgBox("Data has been successfully exported", MsgBoxStyle.Information)
End Sub

'Using the SaveFileDialog--------unsuccessful
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
' Displays a SaveFileDialog so the user can save the Image
' assigned to Button2.
Dim saveFileDialog1 As New SaveFileDialog()
saveFileDialog1.Filter = "Excel File|*.xlsx|Word File|*.doc|Notepad File|*.txt"
saveFileDialog1.Title = "Save In"
saveFileDialog1.ShowDialog()

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim lview As ListViewItem
Dim lview2 As ListViewItem.ListViewSubItem

Dim row, col As Integer

row = 3
col = 3

'Start a new workbook in Excel
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)

' If the file name is not an empty string open it for saving.
If saveFileDialog1.FileName <> "" Then
' Saves the Image via a FileStream created by the OpenFile method.
Dim fs As System.IO.FileStream = CType _
(saveFileDialog1.OpenFile(), System.IO.FileStream)
' Saves the Image in the appropriate ImageFormat based upon the
' file type selected in the dialog box.
' NOTE that the FilterIndex property is one-based.
Select Case saveFileDialog1.FilterIndex
Case 1
For Each lview In ListView1.Items

oSheet.Cells(row, col) = lview.Text

For Each lview2 In lview.SubItems

oSheet.Cells(row, col) = lview2.Text
col = col + 1

Next
col = 3
row = row + 1
Next

'Save the Workbook and Quit Excel
oBook.SaveAs("saveFileDialog1.FileName.GetType()")
oExcel.Quit()

MsgBox("Data has been successfully exported", MsgBoxStyle.Information)

Case 2
Me.Button4.Image.Save(fs, _
System.Drawing.Imaging.ImageFormat.Bmp)

Case 3
Me.Button4.Image.Save(fs, _
System.Drawing.Imaging.ImageFormat.Gif)
End Select

fs.Close()
End If

End Sub

[kareninstructor]

The attached working project done in VS2008 should give you the information to create your solution. It is a generic "show how to" do write operations to sheets and save a file to disk.

[mitkram]

Your vb.project doesn't demonstrate the use of savefiledialog.

[kareninstructor]

Your vb.project doesn't demonstrate the use of savefiledialog.

No but it would not take much on your part to figure this out. Here is what you could have done which works to save an Excel file to disk in the location selected in a SaveDialog selection.

Module ExcelDoneRight
    Private ExcelFile As String = ""
    Sub CreateExcelFile()
        Try
            Dim ExcelApplication As New Microsoft.Office _
                .Interop.Excel.Application
            Dim Workbook As Microsoft.Office _
                .Interop.Excel.Workbook

            Workbook = ExcelApplication.Workbooks.Add
            Dim Dialog As New SaveFileDialog
            Dialog.Filter = "Excel|*.xls"
            If Dialog.ShowDialog = DialogResult.OK Then
                ExcelFile = Dialog.FileName
                Workbook.SaveAs(ExcelFile)
                System.Runtime.InteropServices _
                    .Marshal.FinalReleaseComObject(Workbook)
                Workbook = Nothing
                ExcelApplication.Quit()
                ExcelApplication = Nothing
                GC.Collect()
                GC.WaitForPendingFinalizers()
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            Application.DoEvents()
        End Try
    End Sub

[mitkram]

Thanks for your reply Kevin.

I tried your codes above and it gives me an empty excel file when you open it from path you saved the file
so I tried to modify it and combine with some codes but it is still not writing the contents of the listview
to the excel application. I hope you can correct my codes below. Thank you in advance. I greatly appreciate your help.

Here is the codes I used:

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
'Module ExcelDoneRight
' Private ExcelFile As String = ""
'Sub CreateExcelFile()
Dim ExcelFile As String
Dim lview As ListViewItem
Dim lview2 As ListViewItem.ListViewSubItem
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)

Dim row, col As Integer

row = 3
col = 3

Try
Dim ExcelApplication As New Microsoft.Office _
.Interop.Excel.Application
Dim Workbook As Microsoft.Office _
.Interop.Excel.Workbook

Workbook = ExcelApplication.Workbooks.Add
Dim Dialog As New SaveFileDialog
Dialog.Filter = "Excel|*.xlsx"
If Dialog.ShowDialog = DialogResult.OK Then
ExcelFile = Dialog.FileName

For Each lview In ListView1.Items

oSheet.Cells(row, col) = lview.Text

For Each lview2 In lview.SubItems

oSheet.Cells(row, col) = lview2.Text
col = col + 1

Next
col = 3
row = row + 1
Next

Workbook.SaveAs(ExcelFile)
System.Runtime.InteropServices _
.Marshal.FinalReleaseComObject(Workbook)
Workbook = Nothing
ExcelApplication.Quit()
ExcelApplication = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()
End If
Catch ex As Exception
MsgBox(ex.ToString)
Finally
Application.DoEvents()
End Try
End Sub

[kareninstructor]

Thanks for your reply Kevin.

I tried your codes above and it gives me an empty excel file when you open it from path you saved the file
so I tried to modify it and combine with some codes but it is still not writing the contents of the listview
to the excel application. I hope you can correct my codes below. Thank you in advance. I greatly appreciate your help.

Your welcome in regards to the code. If you follow my example on how I wrote to Excel you will have data written. The current method being used I would not even begin to try figuring it out as it is not proper form.