VB.net Saving Excel File

Hi,

I am exporting my values in my textboxes to an excel spreadsheet (user clicks "Export" button on form). After it is done, it asks me if i want to overwrite my current excel spreadsheet. Is there a way that I can open a save as dialog box so I can choose what name and where to save it?

Thanks

Code:

---

Private Sub ResultsExcelbgw_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles ResultsExcelbgw.DoWork
        Dim Resultsdata As ResultsDataDef = DirectCast(e.Argument, ResultsDataDef)
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Try
            xlApp = New Excel.ApplicationClass
            xlWorkBook = xlApp.Workbooks.Open("C:\program files\Noxious Weeds Report\Reports\nw_budget_Summary.xlsx")
            xlWorkSheet = xlWorkBook.Worksheets("sheet1")

        Catch ex As Exception
            MessageBox.Show("Excel connection error: Please contact your System Administrator.", "Communication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try
        'Use message box below to check current value in excel if needed
        'MsgBox(xlWorkSheet.Cells(1, 1).value)
        'edit the cell with new value
        Try
            xlWorkSheet.Cells(1, 1) = Resultsgrpbox.Text
            xlWorkSheet.Cells(18, 1) = "Report Generated: " & System.DateTime.Now
            xlWorkSheet.Cells(4, 3) = Resultsdata.CounttextBox1
            xlWorkSheet.Cells(5, 3) = Resultsdata.CounttextBox2
            xlWorkSheet.Cells(6, 3) = Resultsdata.CounttextBox3
            xlWorkSheet.Cells(7, 3) = Resultsdata.CounttextBox4
            xlWorkSheet.Cells(8, 3) = Resultsdata.CounttextBox5
            xlWorkSheet.Cells(9, 3) = Resultsdata.CounttextBox6
            xlWorkSheet.Cells(10, 3) = Resultsdata.CounttextBox7
            xlWorkSheet.Cells(11, 3) = Resultsdata.CounttextBox8
            xlWorkSheet.Cells(12, 3) = Resultsdata.CounttextBox9
            xlWorkSheet.Cells(13, 3) = Resultsdata.CounttextBox10
            xlWorkSheet.Cells(4, 4) = Resultsdata.acrestextbox1
            xlWorkSheet.Cells(5, 4) = Resultsdata.acrestextbox2
            xlWorkSheet.Cells(6, 4) = Resultsdata.acrestextbox3
            xlWorkSheet.Cells(7, 4) = Resultsdata.acrestextbox4
            xlWorkSheet.Cells(8, 4) = Resultsdata.acrestextbox5
            xlWorkSheet.Cells(9, 4) = Resultsdata.acrestextbox6
            xlWorkSheet.Cells(10, 4) = Resultsdata.acrestextbox7
            xlWorkSheet.Cells(11, 4) = Resultsdata.acrestextbox8
            xlWorkSheet.Cells(12, 4) = Resultsdata.acrestextbox9
            xlWorkSheet.Cells(13, 4) = Resultsdata.acrestextbox10
            xlWorkSheet.Cells(4, 5) = Resultsdata.revenuetextbox1
            xlWorkSheet.Cells(5, 5) = Resultsdata.revenuetextbox2
            xlWorkSheet.Cells(6, 5) = Resultsdata.revenuetextbox3
            xlWorkSheet.Cells(7, 5) = Resultsdata.revenuetextbox4
            xlWorkSheet.Cells(8, 5) = Resultsdata.revenuetextbox5
            xlWorkSheet.Cells(9, 5) = Resultsdata.revenuetextbox6
            xlWorkSheet.Cells(10, 5) = Resultsdata.revenuetextbox7
            xlWorkSheet.Cells(11, 5) = Resultsdata.revenuetextbox8
            xlWorkSheet.Cells(12, 5) = Resultsdata.revenuetextbox9
            xlWorkSheet.Cells(13, 5) = Resultsdata.revenuetextbox10
            xlWorkSheet.Cells(15, 3) = Resultsdata.TotalCounttextBox
            xlWorkSheet.Cells(15, 4) = Resultsdata.TotalAcrestextBox
            xlWorkSheet.Cells(15, 5) = Resultsdata.TotalRevtextBox
        Catch ex As Exception
            MessageBox.Show(Me, "Excel export error: Please contact your System Administrator.", "Communication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            xlWorkBook.Close()
            xlApp.Quit()
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
        End Try

    End Sub

---

try this:

vb Code:

Private Sub ResultsExcelbgw_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles ResultsExcelbgw.DoWork
    Dim Resultsdata As ResultsDataDef = DirectCast(e.Argument, ResultsDataDef)
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Try
        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open("C:\program files\Noxious Weeds Report\Reports\nw_budget_Summary.xlsx")
        xlWorkSheet = xlWorkBook.Worksheets("sheet1")
 
    Catch ex As Exception
        MessageBox.Show("Excel connection error: Please contact your System Administrator.", "Communication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Exit Sub
    End Try
    'Use message box below to check current value in excel if needed
    'MsgBox(xlWorkSheet.Cells(1, 1).value)
    'edit the cell with new value
    Try
        xlWorkSheet.Cells(1, 1) = Resultsgrpbox.Text
        xlWorkSheet.Cells(18, 1) = "Report Generated: " & System.DateTime.Now
        xlWorkSheet.Cells(4, 3) = Resultsdata.CounttextBox1
        xlWorkSheet.Cells(5, 3) = Resultsdata.CounttextBox2
        xlWorkSheet.Cells(6, 3) = Resultsdata.CounttextBox3
        xlWorkSheet.Cells(7, 3) = Resultsdata.CounttextBox4
        xlWorkSheet.Cells(8, 3) = Resultsdata.CounttextBox5
        xlWorkSheet.Cells(9, 3) = Resultsdata.CounttextBox6
        xlWorkSheet.Cells(10, 3) = Resultsdata.CounttextBox7
        xlWorkSheet.Cells(11, 3) = Resultsdata.CounttextBox8
        xlWorkSheet.Cells(12, 3) = Resultsdata.CounttextBox9
        xlWorkSheet.Cells(13, 3) = Resultsdata.CounttextBox10
        xlWorkSheet.Cells(4, 4) = Resultsdata.acrestextbox1
        xlWorkSheet.Cells(5, 4) = Resultsdata.acrestextbox2
        xlWorkSheet.Cells(6, 4) = Resultsdata.acrestextbox3
        xlWorkSheet.Cells(7, 4) = Resultsdata.acrestextbox4
        xlWorkSheet.Cells(8, 4) = Resultsdata.acrestextbox5
        xlWorkSheet.Cells(9, 4) = Resultsdata.acrestextbox6
        xlWorkSheet.Cells(10, 4) = Resultsdata.acrestextbox7
        xlWorkSheet.Cells(11, 4) = Resultsdata.acrestextbox8
        xlWorkSheet.Cells(12, 4) = Resultsdata.acrestextbox9
        xlWorkSheet.Cells(13, 4) = Resultsdata.acrestextbox10
        xlWorkSheet.Cells(4, 5) = Resultsdata.revenuetextbox1
        xlWorkSheet.Cells(5, 5) = Resultsdata.revenuetextbox2
        xlWorkSheet.Cells(6, 5) = Resultsdata.revenuetextbox3
        xlWorkSheet.Cells(7, 5) = Resultsdata.revenuetextbox4
        xlWorkSheet.Cells(8, 5) = Resultsdata.revenuetextbox5
        xlWorkSheet.Cells(9, 5) = Resultsdata.revenuetextbox6
        xlWorkSheet.Cells(10, 5) = Resultsdata.revenuetextbox7
        xlWorkSheet.Cells(11, 5) = Resultsdata.revenuetextbox8
        xlWorkSheet.Cells(12, 5) = Resultsdata.revenuetextbox9
        xlWorkSheet.Cells(13, 5) = Resultsdata.revenuetextbox10
        xlWorkSheet.Cells(15, 3) = Resultsdata.TotalCounttextBox
        xlWorkSheet.Cells(15, 4) = Resultsdata.TotalAcrestextBox
        xlWorkSheet.Cells(15, 5) = Resultsdata.TotalRevtextBox
    Catch ex As Exception
        MessageBox.Show(Me, "Excel export error: Please contact your System Administrator.", "Communication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Finally
        Dim sfd As New SaveFileDialog
        sfd.InitialDirectory = IO.Path.GetDirectoryName(("C:\program files\Noxious Weeds Report\Reports\nw_budget_Summary.xlsx")
        sfd.FileName = IO.Path.GetFileName(("C:\program files\Noxious Weeds Report\Reports\nw_budget_Summary.xlsx")
        If sfd.ShowDialog = Windows.Forms.DialogResult.OK Then
            xlWorkBook.SaveAs(sfd.FileName)
        End If
        xlWorkBook.Close()
        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
    End Try
End Sub

Hi,

Thanks for your help. I am now getting a "Cross Threading error" on this line:

If sfd.ShowDialog = Windows.Forms.DialogResult.OK