Convert all CSV files in a folder to excel files

snb · Apr 11th 2014

Re: Convert all CSV files in a folder to excel files

Code

Sub Command1_Click()
    c00 = "C:\Users\music\Downloads\exceltest\"
    sn = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & c00 & "*.txt /b").stdout.readall, vbCrLf), ".txt")
    
    For j = 0 To UBound(sn)
       Name c00 & sn(j) As c00 & Replace(sn(j), ".txt", ".csv")
    Next
    
    For j = 0 To UBound(sn)
       With GetObject(c00 & Replace(sn(j), ".txt", "csv"))
            .Sheets(1).Copy , 1
            With .Sheets(.Sheets.Count).Columns(4)
                For jj = 1 To 12
                  .Replace Choose(jj, "NO", "BA", "RG", "SO", "JA", "BE", "VE", "GR", "VI", "MA", "BJ", "OR"), Choose(jj, "B", "W", "R", "P", "Y", "L", "GY", "G", "V", "BR", "TA", "O")
                Next
               .Range("C1,E1,F1,I1,J1,N1,Q1,U1").EntireColumn.Delete
            End With
            .SaveAs replace(ThisWorkbook.Path & "\","\\","\") & Replace(.Name, ".csv", ""), 51
            .Close
        End With
    Next
End Sub

Display More

kiler40 · Apr 11th 2014

Re: Convert all CSV files in a folder to excel files

snb
this code renamed all files from *.txt to *.csv.

but for some reason it is not creating a new file - as i see it must do in the code

snb · Apr 11th 2014

Re: Convert all CSV files in a folder to excel files

Than the code needs some adaptation.

kiler40 · Apr 11th 2014

Re: Convert all CSV files in a folder to excel files

well with some play i finally got this as a working code...

Code

Private Sub CommandButton1_Click()


c00 = "C:\WhireList\"
   Dim i As Long, ii As Long, x, xx, ZZZ, s As String, f
     '\\ Find all txt files in folder and put them into an array
    f = Split(CreateObject("wscript.shell").exec("cmd /c Dir " & c00 & "*.txt /b").stdout.readall, vbCrLf)
     '\\ Loop through the array
    For i = LBound(f) To UBound(f) - 1
         '\\ Remove ".txt" characters(last 4 charactesr)
        ZZZ = Left(f(i), Len(f(i)) - 4)
         '\\ Read  the txt file
        s = CreateObject("scripting.filesystemobject").getfile("C:\WhireList\" & f(i)).openastextstream.readall
         '\\ Split the txt file by the Carriage return at end of each line
        x = Split(s, vbCrLf)
         '\\ Create new Workbook
        With CreateObject("Excel.Application").Workbooks.Add
            .Application.DisplayAlerts = False
             '\\ Loop through all lines and split data by "," delimiter
            For ii = LBound(x) To UBound(x) - 1
                xx = Split(x(ii), ",")
                 '\\ Put data into sheet
                .Sheets("Sheet1").Cells(ii + 1, 1).Resize(1, UBound(xx) + 1).Value = xx
            Next ii
             '\\ Rename sheets, Format, delete columns and Save Workbook
            .Sheets("Sheet1").Name = ZZZ
            .Sheets(ZZZ).Columns.AutoFit
            .Sheets(ZZZ).Cells.Copy .Sheets("Sheet2").Cells
             '\\ Replace characters
            With .Sheets("Sheet2").Columns(4)
                .Replace "NO", "B", xlPart
                .Replace "BA", "W", xlPart
                .Replace "RG", "R", xlPart
                .Replace "SO", "P", xlPart
                .Replace "JA", "Y", xlPart
                .Replace "BE", "L", xlPart
                .Replace "VE", "GY", xlPart
                .Replace "GR", "G", xlPart
                .Replace "VI", "V", xlPart
                .Replace "MA", "BR", xlPart
                .Replace "BJ", "TA", xlPart
                .Replace "OR", "O", xlPart
            End With
            '.Sheets("Sheet2").Range("C:C,E:E,F:F,I:I,J:J,N:N,Q:Q,U:U").Delete
            .Sheets("Sheet2").Name = Split(ZZZ, "_")(1) & "_-"
            .Sheets("Sheet3").Delete
            .SaveAs Filename:=ThisWorkbook.Path & "\" & ZZZ
            .Application.DisplayAlerts = True
            .Close
        End With
    Next i
    MsgBox "Job Done..!"
End Sub

Display More

I have bypassed the part of the code that delete columns... but it is not a big pain
after all the code is working

Thank you all for the help and responses

apo · Apr 12th 2014

Re: Convert all CSV files in a folder to excel files

That's great you got it working.. I thought we were never going to get there..

It's still a mystery why that line errors out for you.. but at least the rest o the code works now..

Convert all CSV files in a folder to excel files

Participate now!

Share