Good Day!
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
(A2)Joe (B2)Sally
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
(A2)Joe, Sally
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