GAMEFORME riski NoeNoe GAMEFORME riski NoeNoe - 2 months ago 34
Vb.net Question

vb.net how to Save excel Workbook to Specific Folder using Save Dialog Box

i want to save my datagridview into excel but i found code in some web is no use specific folder , i want to Save excel Workbook to Specific Folder using Save Dialog Box.

and this my code but this no save with specific folder.

Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value

Dim i As Int16, j As Int16

'xlApp = New Excel.ApplicationClass
xlApp = New Excel.Application()
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")

For c As Integer = 0 To dgv1.Columns.Count - 1
xlWorkSheet.Cells(1, c + 1).Value = dgv1.Columns(c).HeaderText
Next

For i = 0 To dgv1.RowCount - 2
For j = 0 To dgv1.ColumnCount - 1
xlWorkSheet.Cells(i + 2, j + 1) = dgv1(j, i).Value.ToString()
Next
Next

xlWorkBook.SaveAs("d:\kehadiran.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
xlWorkBook.Close(True, misValue, misValue)
xlApp.Quit()

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

MessageBox.Show("Your File is successfully saved d:\kehadiran.xls")

Answer

If you want to let the user pick where they save the file, use the save file dialog. Right now, you're passing a hard-coded path to Workbook.SaveAs(). Use a SaveFileDialog to get a path, and replace the "d:\kehadiran.xls"'s with "savePath":

    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value

    Dim i As Int16, j As Int16

    'xlApp = New Excel.ApplicationClass
    xlApp = New Excel.Application()
    xlWorkBook = xlApp.Workbooks.Add(misValue)
    xlWorkSheet = xlWorkBook.Sheets("sheet1")

    For c As Integer = 0 To dgv1.Columns.Count - 1
        xlWorkSheet.Cells(1, c + 1).Value = dgv1.Columns(c).HeaderText
    Next

    For i = 0 To dgv1.RowCount - 2
        For j = 0 To dgv1.ColumnCount - 1
            xlWorkSheet.Cells(i + 2, j + 1) = dgv1(j, i).Value.ToString()
        Next
    Next

    Dim savePath As String = Nothing
    Using sd As New SaveFileDialog
        With sd
            .RestoreDirectory = True
            .Filter = "Excel XLS Files(*.xls)|*.xls|Excel Macro Embedded Files(*.xlsm)|*.xlsm|Excel XLSX Files(*.xlsx)|*.xlsx"
            .FilterIndex = 3
            If .ShowDialog = DialogResult.OK Then
                savePath = .FileName
            End If
        End With
    End Using

    If savePath IsNot Nothing AndAlso savePath.Trim <> "" Then
        xlWorkBook.SaveAs(savePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
    End If
    xlWorkBook.Close(True, misValue, misValue)
    xlApp.Quit()

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

    MessageBox.Show("Your File is successfully saved " & savePath)
Comments