VBA help to open .txt files and copy into its respective sheet

[ItalianPlatinum]

Looking for some code to help speed up and simplify my process. Currently I have 11 TXT files that I copy into 11 sheets. Each txt file is named something like this Data_LogsXT.txt, where only the last 2 characters will change (so 10 others files). I created each sheet to be named those last 2 characters. An example, sheet name "XT." Looking for a way to open its txt file, copy the data, and paste in cell A1 of its respective sheet then go to the next file and do the same for each sheet/file combo.

 

[ItalianPlatinum]

@John_w Sorry I was away for a bit. If I hover over it shows 0. How would I go about to determine the file line endings of my text file?

 

[Alex Blakenburg]

It might be quicker just to try it. Try replacing the vbCrLf with just vbLf.

PS: If you have Notepad++ and you use it to open the file then it should show you.

 

[John_w]

Yes, just try vbLf or vbCr instead of vbCrLf.

 

[ItalianPlatinum]

Ok cool - easy enough. So when I changed to vbLf it seemed to be working. But towards the end it popped this error

When debugging it displays "out of memory" at this spot. A few files are large is my volume of data causing us problems?

 

[John_w]

Here's another approach. Read each .txt file line by line and write the lines to the sheet in blocks of 100,000 lines (defined by MAX_ARRAY_SIZE).

Public Sub Import_Data_Logs_To_Sheets3()

    Const MAX_ARRAY_SIZE = 100000
    
    Dim textFilesFolder As String
    Dim textFile As String
    Dim FSO As Object 'Scripting.FileSystemObject
    Dim FSts As Object 'Scripting.TextStream
    Dim r As Long, n As Long
    ReDim lines(1 To MAX_ARRAY_SIZE, 1 To 1) As String
  
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select folder containing .txt files"
        If .Show Then
            textFilesFolder = .SelectedItems(1) & "\"
        Else
            Exit Sub
        End If
    End With
    
    Set FSO = CreateObject("Scripting.FileSystemObject") 'New Scripting.FileSystemObject
    
    textFile = Dir(textFilesFolder & "Data_Logs_*.txt")  'line endings are LF characters
    While textFile <> vbNullString
        With ActiveWorkbook.Worksheets(Mid(textFile, 12, 2))
            .Cells.Delete
            Set FSts = FSO.OpenTextFile(textFilesFolder & textFile, 1)  'ForReading
            r = 1
            n = 0
            Do Until FSts.AtEndOfStream
                n = n + 1
                lines(n, 1) = FSts.ReadLine
                If n = MAX_ARRAY_SIZE Then
                    .Cells(r, 1).Resize(n).Value = lines
                    r = r + n
                    n = 0
                End If
            Loop
            FSts.Close
            If n > 0 Then
                .Cells(r, 1).Resize(n).Value = lines
            End If
        End With
        textFile = Dir
    Wend
    
    Set FSts = Nothing
    Set FSO = Nothing

    MsgBox "Done"

End Sub

 

[ItalianPlatinum]

That looks to have worked!