Bulk Convert CSV to XLSX Files While Retaining the Leading Zero

[joyfu1]

Hello everyone,

I was wondering if it is possible to bulk convert CSV to XLSX files while retaining the leading zero? I am currently using a similar code shared by user John_w, and I added an additional line where I convert all of the columns to "TEXT". However, I have noticed that the Macro opens the CSV in Excel (instead of NotePad++, where the leading zero is found), where the leading zeroes are omitted. Hence, when the file is converted to XLSX, the leading zero is never there, to begin with. Is there a way where I can open the file in NotePad when the file is being converted?

Code Shared by User John_W

Public Sub Convert_CSVs_To_XLSX()

    Dim csvFolder As String
    Dim fileName As String
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select folder containing CSV files"
        .InitialFileName = ActiveWorkbook.Path
        If .Show Then
            csvFolder = .SelectedItems(1) & "\"
        Else
            Exit Sub
        End If
    End With
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False    'suppress warning message if .xlsx file already exists
    
    fileName = Dir(csvFolder & "*.csv")
    Do While fileName <> vbNullString
        Workbooks.Open csvFolder & fileName
        ActiveWorkbook.SaveAs fileName:=csvFolder & Replace(fileName, ".csv", ".xlsx", Compare:=vbTextCompare), FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close False
        fileName = Dir
    Loop

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    MsgBox "Done"

End Sub

The line of code I have added:

ActiveSheet.Range(lastRow & ":" lastColumn).NumberFormat = "@"

Thank you so much! I really appreciate any help I could get

 

[SpillerBD]

If you wish to VBA, I would record the Import File method and make use of setting the data type to TEXT. It will give you a good pattern to work with. Then Save the result as the source filename as an XLSX.
However, bulk importing TEXT files of the same layout is way easier using PowerQuery, the modern GET DATA. You can do some from a folder and base the files on appropriate criteria.

Another option is to create a template File with the base Legacy Text File Import already set up. Using the Save Query Definition. When refreshing the connection, you will be asked for the Import Text File and select the next/new file. That's the shortest-long way for 1 file at-a-time.

 

[joyfu1]

If you wish to VBA, I would record the Import File method and make use of setting the data type to TEXT. It will give you a good pattern to work with. Then Save the result as the source filename as an XLSX.
However, bulk importing TEXT files of the same layout is way easier using PowerQuery, the modern GET DATA. You can do some from a folder and base the files on appropriate criteria.

Another option is to create a template File with the base Legacy Text File Import already set up. Using the Save Query Definition. When refreshing the connection, you will be asked for the Import Text File and select the next/new file. That's the shortest-long way for 1 file at-a-time.

Thanks! This is really helpful. Unfortunately, all of the files do not have the same layout, so it seems I have to use VBA.

I took the code above shared by user John_w, and added the QueryTables VBA code right below the DoWhile loop.

However, I am struggling to create a new workbook, setting the import file method's Destination to the new workbook and saving it with the same name as the original CSV file. It seems the workbooks are created, but they are empty as well. I was wondering if there was an existing thread or resource I could refer to? Thanks again!

 

[joyfu1]

Thanks! This is really helpful. Unfortunately, all of the files do not have the same layout, so it seems I have to use VBA.

I took the code above shared by user John_w, and added the QueryTables VBA code right below the DoWhile loop.

However, I am struggling to create a new workbook, setting the import file method's Destination to the new workbook and saving it with the same name as the original CSV file. It seems the workbooks are created, but they are empty as well. I was wondering if there was an existing thread or resource I could refer to? Thanks again!

Sorry quick edit, as I couldn't figure out how to import the data into a new workbook. For now, I tried importing the data into a worksheet. The worksheets are blank. Thanks!

 

[joyfu1]

^^ Just realized I was using the wrong method. Thank you everyone! This has been solved