VBA Convert .CSV to .Xlsx

[rplohocky]

Hello,
I found a snippet of code that will loop through a folder and do a specific function (i.e. insert a formula). I am looking for one of the functions to be changing the extension from CSV to xlsx without creating a new workbook. Can any one share the line of code that will perform this action?

Thank you.

 

[Joe4]

I am looking for one of the functions to be changing the extension from CSV to xlsx without creating a new workbook.

Note that changing the extension on a file does NOT actually convert the file.
So the only time I could understand doing something like this is if the files were already actually Excel files, and were incorrectly assigned a CSV extension.
If you want to actually convert the file from a CSV file to an Excel file, you would need to open it as do a SaveAs and change the file type.
Otherwise, if you just change the extension, you will probably get errors when you try to open the file, saying the format or extension is not valid.

 

[rplohocky]

Note that changing the extension on a file does NOT actually convert the file.
So the only time I could understand doing something like this is if the files were already actually Excel files, and were incorrectly assigned a CSV extension.
If you want to actually convert the file from a CSV file to an Excel file, you would need to open it as do a SaveAs and change the file type.
Otherwise, if you just change the extension, you will probably get errors when you try to open the file, saying the format or extension is not valid.

Hello Joe4,
You are right. I have a folder full of CSV workbooks that need to be converted to XLSX. I know you need to open the workbook then do a savas but then the folder has duplicated workbooks. I'm trying to figure out a way for my macro to take the original workbook and save it as xlsx without keeping the csv version. Your right changing the extension does cause a warning when opened. Any way to accomplish this or do I need to make the macro save it to a new folder?

 

[Joe4]

This VBA code will convert all CSV files in a folder to XLSX files and delete the original CSV:

Sub ConvertCSVToXlsx()
    
    Dim myfile As String
    Dim oldfname As String, newfname As String
    Dim workfile
    Dim folderName As String
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
'   Capture name of current file
    myfile = ActiveWorkbook.Name
    
'   Set folder name to work through
    folderName = "C:\Test\"
    
'   Loop through all CSV filres in folder
    workfile = Dir(folderName & "*.CSV")
    Do While workfile <> ""
'       Open CSV file
        Workbooks.Open Filename:=folderName & workfile
'       Capture name of old CSV file
        oldfname = ActiveWorkbook.FullName
'       Convert to XLSX
        newfname = folderName & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & ".xlsx"
        ActiveWorkbook.SaveAs Filename:=newfname, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWorkbook.Close
'       Delete old CSV file
        Kill oldfname
        Windows(myfile).Activate
        workfile = Dir()
    Loop
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

 

[rplohocky]

This VBA code will convert all CSV files in a folder to XLSX files and delete the original CSV:

Sub ConvertCSVToXlsx()
    
    Dim myfile As String
    Dim oldfname As String, newfname As String
    Dim workfile
    Dim folderName As String
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
'   Capture name of current file
    myfile = ActiveWorkbook.Name
    
'   Set folder name to work through
    folderName = "C:\Test\"
    
'   Loop through all CSV filres in folder
    workfile = Dir(folderName & "*.CSV")
    Do While workfile <> ""
'       Open CSV file
        Workbooks.Open Filename:=folderName & workfile
'       Capture name of old CSV file
        oldfname = ActiveWorkbook.FullName
'       Convert to XLSX
        newfname = folderName & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & ".xlsx"
        ActiveWorkbook.SaveAs Filename:=newfname, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWorkbook.Close
'       Delete old CSV file
        Kill oldfname
        Windows(myfile).Activate
        workfile = Dir()
    Loop
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

Thanks Joe4 this works great! I really appreciate it!

 

[Joe4]

You are welcome.