(Solved) – Save .xlsx files in a folder to .csv files

  • by

I tried this script to convert xlsx files to csv.

I want the old files to be in the folder and the name on csv file to be exact as xlsx file.

I am getting . extra on the csv extension like filename..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 & "*.xlsx")
    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) & ".CSV"
        ActiveWorkbook.SaveAs Filename:=newfname, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
'       Delete old CSV file
        Kill oldfname
        workfile = Dir()

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *