CSV to XLSX Not Working Anymore

Greetings,

I wrote this code to convert csv files to xlsx which worked well for the first set of about 100 files. I just got a second batch from our vendor and I tried converting them but am now getting the "Excel cannot open the file 'SomeName.xlsx' because the file format or file extension is not valid. Verify that the files has not been corrupted and the file extension matches the format of the file." Not sure what broke. Please help. I would also be open to code that can convert all the csv files in the folder to xlsx. But that is not a priority.

Sub Set_Default_Folder_Click()
  'This sets the 'Data Folder' name using 'Folder Picker'
    Dim DefaultFolder As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then
            'Ok Clicked
            DefaultFolder = .SelectedItems(1)
            Range("E6").Value = DefaultFolder
        Else
        'Cancel
            MsgBox ("New Default Folder was not selected. Same folder will be used to store the new data")
            Exit Sub
            
        End If
        
    End With
     
 
End Sub

Sub Convert_to_XLSX_Click()
    Dim MyFile As String
    Dim XLSName As String
    Dim SheetName As String
    Dim CurrentFolder As String
       
    With Application.FileDialog(msoFileDialogFilePicker)
        If .Show = -1 Then
            MyFile = .SelectedItems(1)
            Workbooks.Open (MyFile)
        Else
        'Cancel
            MsgBox ("No File was selected.")
            Exit Sub
                
        End If
    End With
    'Get name of the sheet from the CSV file
    SheetName = ActiveSheet.Name
    
    Sheets(SheetName).Columns("A:A").EntireColumn.AutoFit
    Sheets(SheetName).Columns("B:B").EntireColumn.AutoFit
    Sheets(SheetName).Columns("C:C").EntireColumn.AutoFit
    Sheets(SheetName).Columns("D:D").EntireColumn.AutoFit
    Sheets(SheetName).Columns("E:E").EntireColumn.AutoFit
    Sheets(SheetName).Columns("F:F").EntireColumn.AutoFit
    Sheets(SheetName).Columns("G:G").EntireColumn.AutoFit
    Sheets(SheetName).Columns("J:J").EntireColumn.AutoFit
    Sheets(SheetName).Range("K:K").Select
    Selection.NumberFormat = "0"
    Sheets(SheetName).Columns("L:L").ColumnWidth = 60
    Sheets(SheetName).Range("B:B").Select
    Selection.NumberFormat = "0"
    Sheets(SheetName).Range("C:C").Select
    Selection.NumberFormat = "0"
    CurrentFolder = Range("E6").Value
    ChDir (CurrentFolder)
    XLSName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
    ActiveWorkbook.SaveAs Filename:=(CurrentFolder) & "\" & XLSName & ".xlsx"
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
End Sub

Can you upload a sample of a csv file that was converted, a sample of a csv file from the new batch, and a copy of the workbook which contains the code. Thanks

I am sorry I can't upload the original csv files as they contain data only sent to us by the vendor upon a subpoena. I am attaching a sample csv file that I saved as csv and converted it to xlsx. All three attached here. Thank you for your help.

Hello Holger,

Thank you. That did it. Yes, I am new to VBA learning it by recording macros and from experts like you.

I have one more posting on the forum for copying data, can you help with that please.

Regards,

Kas

Am not allowed to post on multiple forums. Are they all connected.

Sub Convert_to_XLSX_Click()
    Dim MyFile As String
    Dim XLSName As String
    Dim SheetName As String
    Dim CurrentFolder As String
    
    Application.ScreenUpdating = False
    
    With Application.FileDialog(msoFileDialogFilePicker)
        If .Show = -1 Then
            MyFile = .SelectedItems(1)
            Workbooks.Open (MyFile)
        Else
        'Cancel
            MsgBox ("No File was selected.")
            Exit Sub
        End If
    End With
    
    Stop
    
    'Get name of the sheet from the CSV file
    SheetName = ActiveSheet.Name
    
    With Sheets(SheetName)
        .Columns("A:G").EntireColumn.AutoFit
        .Columns("J:J").EntireColumn.AutoFit
        .Range("B:B").NumberFormat = "0"
        .Range("C:C").NumberFormat = "0"
        .Range("K:K").NumberFormat = "0"
        .Columns("L:L").ColumnWidth = 60
    End With
    
    CurrentFolder = Range("E6").Value
    XLSName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs _
        Filename:=(CurrentFolder) & "\" & XLSName & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook, _
        CreateBackup:=False
    ActiveWorkbook.Close
    
    MsgBox "done"
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

Awesome, thank you.

You are allowed to post on multiple forums. However, you are expected to tell everyone where you have posted so they don't waste time working on a problem that has been solved elsewhere.

And be sure you will be found out as most of the people who answer questions here also answer questions on ALL the other forums.