VBA to import TXT as text into Excel

[Dampa88]

Dears,

I'm trying to build a macro to import in Excel a TXT file as text.

In the TXT file I normally have numbers with more that 16 digits so if I copy and paste them in Excel I need to make sure that the cell format is "text".
If not, those big numbers are loosing the last digits.

I tried a couple of macros, but when the TXT is opened automatically the numbers get converted. When they are pasted into the final sheet the result is already corrupted.

Below a TXT example:
9990600331568537
TEST1
54008520005491689
TEST2

In Excel I get:
9.9906E+15
TEST1
5.40085E+16
TEST2

Macros tested are:

Sub Macro1()

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\MyFile.txt", Destination:=Range("$H$5") _
        )
        .Name = "Sample"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
End Sub

Sub Macro2()

    Dim wbI As Workbook, wbO As Workbook
    Dim wsI As Worksheet
    
    Set wbI = ThisWorkbook
    Set wsI = wbI.Sheets("Test")
    Set wbO = Workbooks.Open("C:\MyFile.txt")
    wbO.Sheets(1).Cells.Copy wsI.Cells
    wbO.Close SaveChanges:=False
End Sub

Do you have any advice to solve this problem?

Thanks a lot!

 

[Dampa88]

Hi Joe,

Below last data is fine. This is working super fast, I couldn't think it was possible.

Last thing, I promise
Is there a way to have in column B also the filename of each file? This was the extra step I was talking about, but at this point is much more efficient to have all in one run.

Thanks a lot!

 

[Joe4]

Hi Joe,

Below last data is fine. This is working super fast, I couldn't think it was possible.

Last thing, I promise
Is there a way to have in column B also the filename of each file? This was the extra step I was talking about, but at this point is much more efficient to have all in one run.

Thanks a lot!

Do you perhaps post to the wrong thread?
This comment seems to make no sense in relation to your question.
(And I thought you did not go with my code anyway).

If this indeed the same project, this particular question is totally different from the original one, and should be therefore posted to its own, new thread.

 

[JEC]

I thought he meant JEC

 

[JEC]

Sub jec()
 Dim c00 As String, fl As Variant, a As Variant
 c00 = "C:\Users\xx\Downloads\"

  With CreateObject("scripting.filesystemobject")
     For Each fl In .getfolder(c00).Files
       If .GetExtensionName(fl) = "txt" Then
           a = Split(.opentextfile(fl).readall, vbCrLf)
           With ThisWorkbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(2).Resize(UBound(a))
             .NumberFormat = "@"
             .Value = Application.Transpose(a)
             .Offset(, 1) = fl.Name
           End With
        End If
      Next
  End With
End Sub

 

[Dampa88]

Yes sorry I made a bit of confusion, I meant JEC and I know that my question evolved a bit over time as I discovered possible improvements of my very basic ideas.
For this specific project I'm going with JEC's code as it's super fast and efficient, thanks a lot also for the last addition of the file name!

However, I'm also using the data type = 2 for another project where I need a query table with one TXT file only.

Thanks all for your precious help!

 

[JEC]

You’re welcome!