save listview data to same excel file but different sheet

[sigridish]

hi everybody! i already know how to save in excel using openfiledialog. but what i want to happen is that when i save another file, i want it to be saved in my existing file but in a different sheet. can you guys help me?

this is my code for saving

Public Sub saveExcelFile(ByVal FileName As String)
        Dim xls As New Excel.Application
        Dim sheet As Excel.Worksheet
        Dim i As Integer
        xls.Workbooks.Add()
        sheet = xls.ActiveWorkbook.ActiveSheet
        Dim row As Integer = 1
        Dim col As Integer = 1
        For Each item As ListViewItem In ListView1.Items
            For i = 0 To item.SubItems.Count - 1
                sheet.Cells(row, col) = item.SubItems(i).Text
                col = col + 1
            Next
            row += 1
            col = 1
        Next
        xls.ActiveWorkbook.SaveAs(FileName)
        xls.Workbooks.Close()
        xls.Quit()
    End Sub

Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
        Dim saveFileDialog1 As New SaveFileDialog
        saveFileDialog1.Filter = "Excel File|*.xlsx"
        saveFileDialog1.Title = "Save an Excel File"
        saveFileDialog1.ShowDialog()
        If saveFileDialog1.FileName <> "" Then
            saveExcelFile(saveFileDialog1.FileName)
        End If
        MessageBox.Show("Record Saved!")
    End Sub

[Reverend Jim]

By default when you create a new Excel WorkBook it has three WorkSheets. You can refer to them as

sheet = xls.ActiveWorkbook.WorkSheet(1)    'or (2) or (3)

[Phasma]

Hi sigridish,
As Reverend Jim said you get 3 sheets for free when you create a new workbook. If you are going to use one of the sheets you must refer to it by index.

Then when you need a different you select the sheet you want the data to go to i.e. ‘Worksheet2.Select’. Once you have used the 3 initial sheets and need another you will need to use the workbooks Add method and also give that sheet a name and select it before you can add data to it.

This Workbook opens with one sheet. I set it up as a template.

Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    xlApp = New Excel.ApplicationClass
    xlWorkBook = xlApp.Workbooks.Open(Friends.workingFolder & _
                                      "\Excel\Sales_Report_Daily.xlsx")

    xlWorkSheet = CType(xlWorkBook.Worksheets("Query_Sales_Report_Daily"), Worksheet)


    'Add A New Sheet
    Dim xlWorkSheet1 As Excel.Worksheet
    xlWorkSheet1 = CType(xlWorkBook.Worksheets.Add(), Worksheet)
    xlWorkSheet1.Name = "TEST"
    xlWorkSheet1.Move(After:=xlWorkSheet) 'Move the new sheet after the original
    xlWorkSheet1.Select() 'Select the sheet and enter data

Just a little heads up. By looking at your code it seems like you are running your code with 'Option Strict Off'. I know it is easier to get the results you want, but I have tried that on an app I created on my work computer and installed the app on the users and it failed disastrously.

Enjoy
Ken

[Reverend Jim]

Just a note - to add a worksheet you have to use the Add method of the WorkSheets property, not the WorkBooks property. You don't have to name it before you add data.

[lolafuertes]

Just my cent.
Be aware, that when creating a new workbook, the default configuration to create 3 sheets can be changed by the user to any number between 1 and 255 so is a good practice to verify the current number of sheets before referencing a sheet by index.

Hope this helps

[sigridish]

hi all! how can i save the file on an existing file? im using savefiledialog.

[Reverend Jim]

Then use Save instead of SaveAs

[sigridish]

hi all!

would i still use savefiledialog?
i have attached a picture of my form. what im thinking is that, i would add a textbox that would copy the name from combo box2. then in my code, i will activate the sheet that is named from my textbox. is that possible?

[Reverend Jim]

If you created a new Excel WorkBook (instead of opening an existing one) then you will have to use SaveAs, even if the user selects an existing file in the FileBrowser dialog.

[sigridish]

hi jim!

i tried this one

Dim xls As New Excel.Application
        Dim sheet As Excel.Worksheet
        Dim i As Integer
        xls.Workbooks.Open("C:\Documents and Settings\t-rpalberto\Desktop\summary.xlsx")
        sheet = xls.ActiveWorkbook.Sheets(1)
        Dim row As Integer = 1
        Dim col As Integer = 1
        For Each item As ListViewItem In ListView1.Items
            For i = 0 To item.SubItems.Count - 1
                sheet.Cells(row, col) = item.SubItems(i).Text
                col = col + 1
            Next
            row += 1
            col = 1
        Next
        xls.ActiveWorkbook.Save("C:\Documents and Settings\t-rpalberto\Desktop\summary.xlsx")
        xls.Workbooks.Close()
        xls.Quit()

there is an error, too many arguments to public sub save

[Reverend Jim]

That's because you can't specify a filename with Save, only with SaveAs. When you open an existing Excel WorkBook, the filename is associated with the Excel object so you can use Save and it will save back intoo the same file. When you create a new WorkBook there is no association. That's when you use SaveAs and specify a filename.

[sigridish]

how about this one

Dim xls As New Excel.Application
        Dim sheet As Excel.Worksheet
        Dim i As Integer
        xls.Workbooks.Open("C:\Documents and Settings\t-rpalberto\Desktop\summary.xlsx")
        sheet = xls.ActiveWorkbook.ActiveSheet
        Dim row As Integer = 1
        Dim col As Integer = 1
        For Each item As ListViewItem In ListView1.Items
            For i = 0 To item.SubItems.Count - 1
                sheet.Cells(row, col) = item.SubItems(i).Text
                col = col + 1
            Next
            row += 1
            col = 1
        Next
        xls.ActiveWorkbook.SaveAs("C:\Documents and Settings\t-rpalberto\Desktop\summary.xlsx")
        xls.Workbooks.Close()
        xls.Quit()

error

'C:\Documents and Settings\t-rpalberto\Desktop\summary.xlsx' could not be found. Check the spelling of the file name, and verify that the file location is correct. If you are trying to open the file from your list of most recently used files, make sure that the file has not been renamed, moved, or deleted.

[Reverend Jim]

Reread my last post. If you opened an existing Excel file (rather than creating a new one from scratch) you can use Save with no filename and it will save the WorkBook back into the original file. Change line 16 to

xls.ActiveWorkBook.Save()

Also, I suggest if you are going to have a literal string for the filename, put it into a const or string variable rather than typing in the name twice. It prevents errors due to a typo.

[sigridish]

i already changed it but i still get the same error in line 4

[Reverend Jim]

If you are getting the error on line 4 it means that the file you are trying to open doesn't exist. Can you find it in Windows Explorer and open it in Excel?

[sigridish]

hi again. i have another question. how will i code this process

when i hit view button, an excel file would be open. what i want to happen is that when the view button is clicked, the data in my database would be placed in an excel file then the excel would open showing me my exported data from excel

[Reverend Jim]

What you are asking is will I please write your entire application for you. That's not what I do. I will help you with certain techniques like how to interface with Excel from VB, but you will have to put everything together into an application.

[sigridish]

i know jim. im not asking for you to write the entire code. i just want to know the code that when i hit a certain button, an excel file would show up.

[Reverend Jim]

My aploogies. I misunderstood. I wrote the following code to show how to export a listview to an Excel spreadsheet. You can use the same code except instead of copying data from a listview you will copy it from either a SQL DataStream or RecordSet.

Private Sub btnExport_Click(sender As System.Object, e As System.EventArgs) Handles btnExport.Click

    SaveFileDialog1.Title = "Save Excel File"
    SaveFileDialog1.Filter = "Excel files (*.xls)|*.xls|Excel Files (*.xlsx)|*.xslx"
    SaveFileDialog1.ShowDialog()

    If SaveFileDialog1.FileName = "" Then
        Exit Sub
    End If

    Dim xls As New Excel.Application
    Dim sheet As Excel.Worksheet

    xls.Workbooks.Add()
    sheet = xls.ActiveWorkbook.ActiveSheet

    Dim row As Integer = 1
    Dim col As Integer = 1

    For Each item As ListViewItem In lvwToExport.Items
        For i As Integer = 0 To item.SubItems.Count - 1
            sheet.Cells(row, col) = item.SubItems(i).Text
            col = col + 1
        Next
        row += 1
        col = 1
    Next

    xls.ActiveWorkbook.SaveAs(SaveFileDialog1.FileName)
    xls.Workbooks.Close()
    xls.Quit()

End Sub

If, instead, you want to open an existing Excel file, use the xls.WorkBooks.Open method. Hope this helps.

[sigridish]

hi jim. thanks for that. sorry too! :)

[Alex Ocampo]

Please, what is the reference to add in the project so the excel.application and excel.woksheet objects are recognized?

[sigridish]

microsoft.office.interop.excel

microsoft excel object library

[Alex Ocampo]

Thanks!!!!

[sigridish]

no prob!