Convert Multiple CSV file to xls or xlsx

[Syedali]

Hi Friends,

I am having more than 100 csv files in a folder. I want to convert into xls or xlsx . How can i do. I need any macro or batch file to do this. Kindly anyone help on this.

Thanks in Advance.....

 

[Deepak]

I think fastest possible solution is loop.

http://stackoverflow.com/questions/10803395/is-it-possible-to-batch-convert-csv-to-xls-using-a-macro

& this might take much time depending on the size/array of CSV.

 

[YasserKhalil]

Hello Syed
Put your CSV files in folder named "Test" and put the following code in the same path of the folder

Sub CSVToXLS()
    Dim fPath  As String, fPathDONE As String, fCOUNT As Long
    Dim fName  As String, fType    As String
    Dim fAfter As String, NwName    As String
   
    fPath = ThisWorkbook.Path & "\Test\"
    If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
   
    fPathDONE = fPath & "\Converted\"
    MakeFolders fPathDONE
   
    fName = Dir(fPath & "*.CSV")
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    Do While Len(fName) > 0
        NwName = Left(fName, InStrRev(fName, ".") - 1)
        Workbooks.Open fPath & fName
        ActiveSheet.Name = NwName
        ActiveWorkbook.SaveAs fPath & NwName & ".xls", FileFormat:=xlNormal
        ActiveWorkbook.Close
       
        Name fPath & fName As fPathDONE & fName
       
        fCOUNT = fCOUNT + 1
        fName = Dir()
    Loop
   
    MsgBox "A Total Of " & fCOUNT & " Files Were Processed"
    Application.ScreenUpdating = True
End Sub

Function MakeFolders(MyStr As String)
    Dim MyArr  As Variant
    Dim pNum    As Long
    Dim pBuf    As String
   
    On Error Resume Next
   
    MyArr = Split(MyStr, "\")
    pBuf = MyArr(LBound(MyArr)) & "\"
    For pNum = LBound(MyArr) + 1 To UBound(MyArr)
        pBuf = pBuf & MyArr(pNum) & "\"
        MkDir pBuf
    Next pNum
End Function