CSV to XLSX Not Working Anymore

[Samrari]

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

 

[Joe4]

Re: SOLVED: Re: CSV to XLSX Not Working Anymore

Since you are opening a CSV, and then saving as an Excel file, you may need to explicitly tell it the file type you are saving to (because it may be defaulting to the file type of the file you just opened).

Try changing this line:

ActiveWorkbook.SaveAs Filename:=(CurrentFolder) & "\" & XLSName & ".xlsx"

to this:

ActiveWorkbook.SaveAs Filename:=(CurrentFolder) & "\" & XLSName & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

and see if that makes a difference.

 

[Samrari]

Re: SOLVED: Re: CSV to XLSX Not Working Anymore

Hi Joe,

I changed it to my code as follows based on answer from excelforum.com. Now I see the value of not cross posting and appologize for being an idiot. Here's what I changed and it is working again.

ActiveWorkbook.SaveAs Filename:=(CurrentFolder) & "\" & XLSName & ".xlsx", FileFormat:=51, CreateBackup:=False

Again, thank you for your help.

Regards,

Kas