Import CSV (UTF-8) to Excel without formatting

[mrwad]

I have code for pasting data from CSV file to Worksheet. Everyhting works fine but I am facing some problems with current solution.


Is there a way to paste data as it is without formatting? Now Excel is formatting for example -Name to =-Name so there #Name ? errors instead of Values. Also it is formatting 215067910018 to 2,15068E+11.


Here is my current code:

Sub GetCustomers()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add(After:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "Sheet1"


    Dim strText As String


    Dim file As String
    file = "L:\15\186507.CSV


    ' read utf-8 file to strText variable
   With CreateObject("ADODB.Stream")
        .Open
        .Type = 1  ' Private Const adTypeBinary = 1
        .LoadFromFile file
        .Type = 2  ' Private Const adTypeText = 2
        .Charset = "utf-8"
        strText = .ReadText(-1)  ' Private Const adReadAll = -1
    End With


    ' parse strText data to a sheet
    intRow = 1
    For Each strLine In Split(strText, Chr(10))
        If strLine <> "" Then
            With ws
                .Cells(intRow, 1) = strLine
                .Cells(intRow, 1).TextToColumns Destination:=Cells(intRow, 1), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                    Semicolon:=False, Comma:=True, Space:=False, Other:=False
            End With


            intRow = intRow + 1
        End If
    Next strLine

Asked also here: https://stackoverflow.com/questions/58426479/import-csv-utf-8-to-excel-without-formatting

 

[mrwad]

Well this works if run after import:

ThisWorkbook.Worksheets("Sheet1").Range("A1:K900").NumberFormat = "@"

but since during the import:

    ' parse strText data to a sheet
    intRow = 1
    For Each strLine In Split(strText, Chr(10))
        If strLine <> "" Then
            With ws
                .Cells(intRow, 1) = strLine
                .Cells(intRow, 1).TextToColumns Destination:=Cells(intRow, 1), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                    Semicolon:=False, Comma:=True, Space:=False, Other:=False
            End With


            intRow = intRow + 1
        End If
    Next strLine

Excel already added "=-Name" it does not change to "-Name" and still gives an error. I can't figure out how to set

ThisWorkbook.Worksheets("Finland").Range("A1:K900").NumberFormat = "@"

during import and not after everything has been done.