Question SaveFileDialog and export Excel

[solfinker]

Hello!
I want to combine the SaveFileDialog with saving an Excel file.
On the one hand I have the code for creating an specific excel file:

Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer


        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")




        For i = 0 To Formdgv2.dgv1.RowCount - 2
            For j = 0 To Formdgv2.dgv1.ColumnCount - 1
                For k As Integer = 1 To Formdgv2.dgv1.Columns.Count
                    xlWorkSheet.Cells(1, k) = Formdgv2.dgv1.Columns(k - 1).HeaderText
                    xlWorkSheet.Cells(i + 2, j + 1) = Formdgv2.dgv1(j, i).Value.ToString()
                Next
            Next
        Next
        xlWorkSheet.SaveAs("C:\Users\Administrador\Documents\dinasat.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()


        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)


        Dim res As MsgBoxResult
        res = MsgBox("Ready, want to open the file?", MsgBoxStyle.YesNo)
        If (res = MsgBoxResult.Yes) Then
            Process.Start("C:\Users\Administrador\Documents\dinasat.xlsx")
        End If
    End Sub
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

and on the other hand I have the general code for the SaveFileDialog:

[COLOR=#00008B]Dim[/COLOR] myStream [COLOR=#00008B]As[/COLOR] Stream
    [COLOR=#00008B]Dim[/COLOR] saveFileDialog1 [COLOR=#00008B]As[/COLOR] [COLOR=#00008B]New[/COLOR] SaveFileDialog()

    saveFileDialog1.Filter = [COLOR=#800000]"excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"[/COLOR]
    saveFileDialog1.FilterIndex = [COLOR=#800000]2[/COLOR]
    saveFileDialog1.RestoreDirectory = [COLOR=#800000]True[/COLOR] 

    [COLOR=#00008B]If[/COLOR] saveFileDialog1.ShowDialog() = DialogResult.OK [COLOR=#00008B]Then[/COLOR]
        myStream = saveFileDialog1.OpenFile()
        [COLOR=#00008B]If[/COLOR] (myStream IsNot [COLOR=#800000]Nothing[/COLOR]) [COLOR=#00008B]Then[/COLOR] 
            [COLOR=#808080]' Here is where I had tried to use part of the other code[/COLOR]
            myStream.Close()
        [COLOR=#00008B]End[/COLOR] [COLOR=#00008B]If[/COLOR] 
    [COLOR=#00008B]End[/COLOR] [COLOR=#00008B]If[/COLOR]

And, by the way, why does it take hours to save the excel file from a not very long datagridview? Would it be the same if I try to do the same from an array?

 

[JohnH]

Writing cell by cell is slow, using an array to set a range of values is about as fast as setting a single cell, see here about how to do that: How to transfer data to an Excel workbook by using Visual Basic .NET

SaveFileDialog has FileName property that you can use to save excel there.

 

[solfinker]

Very helpful, as always.
Thank you.