VBA CSV import: long numbers are not written out

Question

I have a CSV File, which I want to display in my Spreadsheet using the QueryTable.

Because I have long numbers, I want to format the cells as text (because the last digits are turned to a "0").

I tried formatting the cells beforehand and setting the "PreserveFormatting" to True, but this leaves me still with a "E+16"-number and if I click on it, the last 2 digits are replaced with "0".

Do you know a way, to get the full number written as a Text into the cell?

This is my CSV:


    1,Name1,Surname1,26778942223654789,,,,,,,,,,,
    2,Name2,Surname2,31678678797467889,,,,,,,,,,,
    3,Name3,Surname3,31657894475133527,,,,,,,,,,,
    4,Name4,Surname4,35448512368896137,,,,,,,,,,,

And this is my Code:


    Sub Personen_importieren()

    import_path = Range("A2").Text

    If import_path <> False Then
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & import_path,_ Destination:=ActiveSheet.Range("A9"))
             .TextFileParseType = xlDelimited
             .TextFileCommaDelimiter = True
             .PreserveFormatting = True
             .AdjustColumnWidth = False
             .Refresh
        End With
    End If

    End Sub

Maybe look at: https://learn.microsoft.com/en-us/office/vba/api/excel.querytable.textfilecolumndatatypes — Tim Williams, Jan 06 '22 at 18:12
You need to specify the column types at the time you do the query, not after. I suggest you record a macro while using the Legacy Import Wizard, and be sure to set the data type to Text for the column(s) with the long numbers. Then adapt that recording to replace the file path with the contents of your Range. — Ron Rosenfeld, Jan 06 '22 at 23:28

score 1 · Answer 1 · answered Jan 07 '22 at 08:09

Thanks for your answers

Following both tips, I recorded a macro where I imported the CSV file as needed.

This showed me the correct property ".TextFileColumnDataTypes"

After that, I had to delete all connections, or else the sheet had an error while opening.

This is my Code now:

Sub Personen_importieren()

    import_path = Range("A2").Text

    If import_path <> False Then
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & import_path, Destination:=ActiveSheet.Range("A9"))
             .TextFileParseType = xlDelimited
             .TextFileCommaDelimiter = True
             .PreserveFormatting = True
             .RefreshOnFileOpen = False
             .AdjustColumnWidth = False
             .TextFileColumnDataTypes = Array(2, 2, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
             .Refresh
        End With
        
        Dim cn
        For Each cn In ThisWorkbook.Connections
            cn.Delete
        Next cn
        For Each cn In ActiveSheet.QueryTables
            cn.Delete
        Next cn
    
    End If

    End Sub

VBA CSV import: long numbers are not written out

1 Answers1

Heading