How to combine multiple csv files into an existing excel sheet

Question

I've programmed a vb.net application. This is a matching applcation generates 3 lisboxes. I have written these to a csv files.

Private Sub Button3_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles Button3.MouseClick, Button4.MouseClick, Button5.MouseClick 
        ListBox3.Items.Add(TimeString) '(ElapsedTime.Milliseconds).ToString)
        If sender Is Button3 Then ListBox2.Items.Add("1")
        If sender Is Button4 Then ListBox2.Items.Add("2")
        If sender Is Button5 Then ListBox2.Items.Add("3")
        chooseImg() 
    End Sub


    Private Sub SaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveButton.Click
        Using sw As StreamWriter = New StreamWriter("C:\Documents and Settings\Desktop\Poo\Poofile.csv") 
            For i As Integer = 0 To ListBox1.Items.Count - 1
                sw.WriteLine(ListBox1.Items(i) & "," & ListBox2.Items(i) & "," & ListBox3.Items(i))
            Next
            sw.Close()
        End Using
        SaveButton.Enabled = False
    End Sub

This produces my csv files and I have the contents of each listbox nicely written to individual columns...no commas yeahyyy!!!

My application stores login information to excel. How do I pull in all the csv files into the existing excel file? I don't have any column names in the csv file just three lists of numbers. Is it better to send each file into separate worksheets and perhaps call the the worksheet the same name as the csv file? Or is there a way of combining all the data into one excel sheet ...perjaps pull the csv file into the same sheet?

Answer 1

Renuv8,

I don't really see several CSV files : I see only the C:\Documents and Settings\Desktop\Poo\Poofile.csv

What are the other files ?

The way you have written your program, you can view directly a .CSV file in Excel without any additional coding: Excel reads .CSV files directly. So if you have several .CSV files, they will be read as different files.

If you want to gather various .CSV files into the same workbook, you will have to code that, using the Excel interop interface.

Answer 2

So you have three CSV files but the columns are not separated by commas? What is the column delimiter?

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 3

Hi Renuv,

Welcome to the MSDN Forum.

Add to Sygrien's suggestion: this is the code of save several CSV file in one excel file:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Exl As New Excel.Application()

        'make the application visible when processing
        Exl.Visible = True

        Dim wb As Excel.Workbook = Exl.Application.Workbooks.Add 'create a new Workbook
        Dim csvFile As String = Dir("D:\*.csv")

        Do While csvFile <> ""
            Dim wb2 As Excel.Workbook = Exl.Workbooks.Open("d:\" & csvFile)
            wb.Sheets.Add() ' add a new worksheet
            wb2.Sheets(1).cells.copy()
            wb.Sheets(1).paste()
            wb.Sheets(1).name = csvFile 'rename the worksheet
            csvFile = Dir()
        Loop

        'The line below will save only the active workbook with .xls extention that Exel Viewer can open
        'Exl.DisplayAlerts = vbFalse
        Exl.AlertBeforeOverwriting = vbFalse
        wb.SaveAs("D:\test.xls", -4143)
        wb.Close()

        'EXIT Application
        Exl.Quit() 'when you exit your application, the process will exit         
        System.Runtime.InteropServices.Marshal.ReleaseComObject(Exl)
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Exl)
        Exl = Nothing
        GC.Collect()
        GC.WaitForPendingFinalizers()

    End Sub

Best regards,

---

Mike Feng
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.