Results 1 to 3 of 3

Thread: VB.net Saving Excel File

  1. Aug 25th, 2010, 06:01 PM #1
    caolong
    caolong is offline

    Thread Starter
    Member
    Join Date
    Jul 2010
    Posts
    43

    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
    Reply With Quote Reply With Quote
  2. Aug 25th, 2010, 06:35 PM #2
    .paul.
    .paul. is offline
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    26,423

    Re: VB.net Saving Excel File

    try this:

    vb Code:
    1. Private Sub ResultsExcelbgw_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles ResultsExcelbgw.DoWork

    2.     Dim Resultsdata As ResultsDataDef = DirectCast(e.Argument, ResultsDataDef)

    3.     Dim xlApp As Excel.Application

    4.     Dim xlWorkBook As Excel.Workbook

    5.     Dim xlWorkSheet As Excel.Worksheet

    6.     Try

    7.         xlApp = New Excel.ApplicationClass

    8.         xlWorkBook = xlApp.Workbooks.Open("C:\program files\Noxious Weeds Report\Reports\nw_budget_Summary.xlsx")

    9.         xlWorkSheet = xlWorkBook.Worksheets("sheet1")

    10.  
    11.     Catch ex As Exception

    12.         MessageBox.Show("Excel connection error: Please contact your System Administrator.", "Communication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

    13.         Exit Sub

    14.     End Try

    15.     'Use message box below to check current value in excel if needed

    16.     'MsgBox(xlWorkSheet.Cells(1, 1).value)

    17.     'edit the cell with new value

    18.     Try

    19.         xlWorkSheet.Cells(1, 1) = Resultsgrpbox.Text

    20.         xlWorkSheet.Cells(18, 1) = "Report Generated: " & System.DateTime.Now

    21.         xlWorkSheet.Cells(4, 3) = Resultsdata.CounttextBox1

    22.         xlWorkSheet.Cells(5, 3) = Resultsdata.CounttextBox2

    23.         xlWorkSheet.Cells(6, 3) = Resultsdata.CounttextBox3

    24.         xlWorkSheet.Cells(7, 3) = Resultsdata.CounttextBox4

    25.         xlWorkSheet.Cells(8, 3) = Resultsdata.CounttextBox5

    26.         xlWorkSheet.Cells(9, 3) = Resultsdata.CounttextBox6

    27.         xlWorkSheet.Cells(10, 3) = Resultsdata.CounttextBox7

    28.         xlWorkSheet.Cells(11, 3) = Resultsdata.CounttextBox8

    29.         xlWorkSheet.Cells(12, 3) = Resultsdata.CounttextBox9

    30.         xlWorkSheet.Cells(13, 3) = Resultsdata.CounttextBox10

    31.         xlWorkSheet.Cells(4, 4) = Resultsdata.acrestextbox1

    32.         xlWorkSheet.Cells(5, 4) = Resultsdata.acrestextbox2

    33.         xlWorkSheet.Cells(6, 4) = Resultsdata.acrestextbox3

    34.         xlWorkSheet.Cells(7, 4) = Resultsdata.acrestextbox4

    35.         xlWorkSheet.Cells(8, 4) = Resultsdata.acrestextbox5

    36.         xlWorkSheet.Cells(9, 4) = Resultsdata.acrestextbox6

    37.         xlWorkSheet.Cells(10, 4) = Resultsdata.acrestextbox7

    38.         xlWorkSheet.Cells(11, 4) = Resultsdata.acrestextbox8

    39.         xlWorkSheet.Cells(12, 4) = Resultsdata.acrestextbox9

    40.         xlWorkSheet.Cells(13, 4) = Resultsdata.acrestextbox10

    41.         xlWorkSheet.Cells(4, 5) = Resultsdata.revenuetextbox1

    42.         xlWorkSheet.Cells(5, 5) = Resultsdata.revenuetextbox2

    43.         xlWorkSheet.Cells(6, 5) = Resultsdata.revenuetextbox3

    44.         xlWorkSheet.Cells(7, 5) = Resultsdata.revenuetextbox4

    45.         xlWorkSheet.Cells(8, 5) = Resultsdata.revenuetextbox5

    46.         xlWorkSheet.Cells(9, 5) = Resultsdata.revenuetextbox6

    47.         xlWorkSheet.Cells(10, 5) = Resultsdata.revenuetextbox7

    48.         xlWorkSheet.Cells(11, 5) = Resultsdata.revenuetextbox8

    49.         xlWorkSheet.Cells(12, 5) = Resultsdata.revenuetextbox9

    50.         xlWorkSheet.Cells(13, 5) = Resultsdata.revenuetextbox10

    51.         xlWorkSheet.Cells(15, 3) = Resultsdata.TotalCounttextBox

    52.         xlWorkSheet.Cells(15, 4) = Resultsdata.TotalAcrestextBox

    53.         xlWorkSheet.Cells(15, 5) = Resultsdata.TotalRevtextBox

    54.     Catch ex As Exception

    55.         MessageBox.Show(Me, "Excel export error: Please contact your System Administrator.", "Communication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

    56.     Finally

    57.         Dim sfd As New SaveFileDialog

    58.         sfd.InitialDirectory = IO.Path.GetDirectoryName(("C:\program files\Noxious Weeds Report\Reports\nw_budget_Summary.xlsx")

    59.         sfd.FileName = IO.Path.GetFileName(("C:\program files\Noxious Weeds Report\Reports\nw_budget_Summary.xlsx")

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

    61.             xlWorkBook.SaveAs(sfd.FileName)

    62.         End If

    63.         xlWorkBook.Close()

    64.         xlApp.Quit()

    65.         releaseObject(xlApp)

    66.         releaseObject(xlWorkBook)

    67.         releaseObject(xlWorkSheet)

    68.     End Try

    69. End Sub


    Reply With Quote Reply With Quote
  3. Aug 26th, 2010, 06:09 PM #3
    caolong
    caolong is offline

    Thread Starter
    Member
    Join Date
    Jul 2010
    Posts
    43

    Re: VB.net Saving Excel File

    Hi,

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

    If sfd.ShowDialog = Windows.Forms.DialogResult.OK
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules


Click Here to Expand Forum to Full Width