Reformatting Excel Files and sending output to notepad

[richie9648]

Hi All

Been attempting my spreadsheet conversion to notepad all morning without any luck and now very frustrated

Could some of you good people in here help out if possible

It seems very simple but I cant get my head round it. I have 56 large files in a directory which I need to reformat etc into a csv/notepad format. All 56 files that are in excel format need to be in this one humongous csv file

I have also attached input and output files below in the zip file

Any helpers?

Thanks

Rich

[richie9648]

ok getting somewhere slowly......how do i loop through within a selected directory to look at all files within a directory?

[HTML]Sub WriteCSV()
Const Delimiter = ";"
Const Delimiter2 = "#"
Const Delimiter3 = " "
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\Users\xxxx\Desktop\" 'Put you path of where you want it saved here
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")
WriteFileName = ThisWorkbook.Name & ".csv"

'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 1 To LastRow
For colcount = 1 To 14
If colcount = 1 Then
outputline = Cells(RowCount, colcount)
ElseIf colcount = 2 Then
outputline = outputline & Delimiter2 & Cells(RowCount, colcount)
ElseIf colcount = 3 Then
outputline = outputline & Delimiter2 & Cells(RowCount, colcount)
ElseIf colcount = 5 Then
outputline = outputline & Delimiter2 & Format(Cells(RowCount, colcount), shortdate)
ElseIf colcount = 6 Then
outputline = outputline & Delimiter3 & Format(Cells(RowCount, colcount), longtime)
ElseIf colcount = 9 Then
outputline = outputline & Delimiter & Cells(RowCount, colcount)
Else
outputline = outputline
End If
Next colcount
tswrite.writeline outputline
Next RowCount
tswrite.Close
End Sub[/HTML]