I've created a rudimentary program that takes multiple .csv files and creates one merged .csv file. What I'm looking to do is after merging the file into one, I want to convert it to an .xls file, however, when I do this the converting is not correct.
Here is an example. Let's say csv file 1 contains this: (A1)Moe (B1)Larry (C1)Curly, and csv file 2 contains this: (A1)Joe (B1)Sally, when I merge these two files I get the following:
(A1)Moe (B1)Larry (C1)Curly
This works as intended. The following is an example of what I get if I try to merge and convert to .xls.
(A1)Moe, Larry, Curly
Not quite what I wanted, but close. I need it to look like the first example but be in .xls format. I've done some research on the the net on various routes to take and have tried different ones with no success. I'm still fairly new to vb and would appreciate any direction given. See below for what I have working so far.......
Public Class frmMain Private Sub btnAppendData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAppendText.Click 'Declare dialog boxes Dim ResultOFD As DialogResult Dim ResultSFD As DialogResult ' Allow the user to select multiple files. ofdMain.Multiselect = True ofdMain.Title = "Please Select Multiple Files" sfdMain.Title = "Please Choose New File Name" ' Set the file dialog to filter for text files. ofdMain.Filter = "All Files (*.*)|*.*|Text Files (*.txt)|*.txt|CSV Files (*.csv)|*.csv|Excel Files (*.xls)|*.xls|Excel Files (*.xlsx)|*.xlsx" sfdMain.Filter = "All Files (*.*)|*.*|Text Files (*.txt)|*.txt|CSV Files (*.csv)|*.csv|Excel Files (*.xls)|*.xls|Excel Files (*.xlsx)|*.xlsx" Dim FileNameWrite, File, FileContents As String Dim sw As StreamWriter ' Display the Open File dialog box with a null value for default file ofdMain.FileName = "" ResultOFD = ofdMain.ShowDialog() 'If Open and Save file dialog boxes Ok then....... If ResultOFD = Windows.Forms.DialogResult.OK Then ResultSFD = sfdMain.ShowDialog() ' Display the dialog box. FileNameWrite = sfdMain.FileName If ResultSFD = Windows.Forms.DialogResult.OK Then 'For Each loop For Each File In ofdMain.FileNames Dim sr As New System.IO.StreamReader(File) 'Sets up StreamReader to read new "File" for each run File = ofdMain.FileName 'Holds file name FileContents = sr.ReadToEnd 'Reads content to end of file and stores data in FileContents sr.Close() 'Close StreamReader sw = New StreamWriter(FileNameWrite, True) 'Open streamwriter to new file name created, True indicates data is appended , not overwritten sw.Write(FileContents) 'Writes contents of variable to file sw.Close() 'Close StreamWriter Next File MessageBox.Show(FileNameWrite + " was written successfully!") 'File status End If End If End Sub