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]

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'

If you are converting from CSV to XLSX, why would you be opening a file with an XLSX extension?
Shouldn't you only be opening files with CSV extensions?

 

[Samrari]

I use the filepicker to open the csv file. Not sure what you mean. If the code ought to look different for opening a csv file, please help me change the code.

 

[Samrari]

I'm sorry I know understand what you mean by opening the xlsx file. I try to open the file once I have used to code to convert the file. That is when I get the error.

 

[shg]

Cross-posted at CSV to XLSX Not Working Anymore

 

[Samrari]

Are all these forums connected. Are you saying I should not be posting my questions on multiple forums.

 

[Michael M]

No, we're not connected as forums, but many of the posters solve issues on multiple sites.

Have a read here, as to why you shouldn't cross post

Excelguru Help Site - A message to forum cross posters

 

[Joe4]

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

I'm sorry I know understand what you mean by opening the xlsx file. I try to open the file once I have used to code to convert the file. That is when I get the error.

What I meant is from your description, it sounds like you should be opening CSV files and saving them as XLSX files.
And from the error description you left, it sounds like you are trying to opening XLSX files. Why would you be opening XLSX files in a program designed to open CSV files?
Are you selecting the wrong files?

 

[Samrari]

Sorry about cross posting. I get an error when I try to open the xlsx file after I have already done the conversion.

 

[Samrari]

SOLVED: Re: CSV to XLSX Not Working Anymore

Thank you all for helping.