How to import .CSV preserving leading zeros numbers into Excel using VBA

Question

I have a database which make the extract in .CSV and using VBA I import the data into Excel but when imported, there are some leading zeros that are missing in the IDs

Even when I open the .CSV file in Excel, those zeros are not there. This is the current code I'm using:

sub import()
Dim File As String

MsgBox "Please select the Extract File", vbInformation

With Application.FileDialog(msoFileDialogFilePicker)
    .Filters.Clear
    .InitialFileName = "\\route"
    .AllowMultiSelect = False
    .Filters.Add "csv", "*.csv"
    If .Show = -1 Then
        File = .SelectedItems(1)
    Else
        MsgBox "Please, select the file then try again", vbExclamation
        Exit Sub
    End If
End With

With Worksheets("Data Paste").QueryTables.Add(Connection:= _
    "TEXT;" & File _
    , Destination:=Worksheets("Data Paste").Range("$A$1"))
    .Name = "FileName"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 65001
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileTrailingMinusNumbers = True
    .TextFileColumnDataTypes = Array(xlTextFormat)
    .Refresh BackgroundQuery:=False
End With
Exit Sub

I tried changing the format of the worksheet to TEXT beforehand, but those zeros are still missing even though that data is in text format.

Edit 1: I opened the .CSV with NotePad and those leading zeros are there. But not when I open it with Excel

Answer 1

.TextFileColumnDataTypes = Array(xlTextFormat) 

will only apply text format to the first column. You need to add a value corresponding to each column which needs to be imported as text format.

https://learn.microsoft.com/en-us/office/vba/api/excel.querytable.textfilecolumndatatypes

Returns or sets an ordered array of constants that specify the data types applied to the corresponding columns in the text file that you are importing into a query table. The default constant for each column is xlGeneral. Read/write Variant.