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.

 

[John_w]

Try this macro. It uses Application.Transpose which has a maximum array size of 65,535 elements, so if the .txt files have more than 65,535 lines a replacement function would be needed

Public Sub Import_Data_Logs_To_Sheets()

    Dim textFilesFolder As String
    Dim textFile As String
    Dim allText As String
    Dim lines As Variant
        
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select folder containing .txt files"
        If .Show Then
            textFilesFolder = .SelectedItems(1) & "\"
        Else
            Exit Sub
        End If
    End With
   
    textFile = Dir(textFilesFolder & "Data_Logs*.txt")
    While textFile <> vbNullString
        Open textFilesFolder & textFile For Binary As #1
        allText = Space(LOF(1))
        Get #1, , allText
        Close #1
        lines = Split(allText, vbCrLf)
        With ActiveWorkbook.Worksheets(Mid(textFile, 10, 2))
            .Cells.Delete
            .Range("A1").Resize(UBound(lines) + 1).Value = Application.Transpose(lines)
        End With
        textFile = Dir
    Wend

    MsgBox "Done"

End Sub

 

[ItalianPlatinum]

Hi John. Sorry for the bad news but I opened each file to check and looks like most are over that limit

 

[John_w]

This solves the problem by copying the lines to a two-dimensional array and putting that in the cells.

Public Sub Import_Data_Logs_To_Sheets()

    Dim textFilesFolder As String
    Dim textFile As String
    Dim allText As String
    Dim lines As Variant
    Dim output() As String, i As Long
       
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select folder containing .txt files"
        If .Show Then
            textFilesFolder = .SelectedItems(1) & "\"
        Else
            Exit Sub
        End If
    End With
   
    textFile = Dir(textFilesFolder & "Data_Logs*.txt")
    While textFile <> vbNullString
        Debug.Print textFile, Mid(textFile, 10, 2)
        Open textFilesFolder & textFile For Binary As #1
        allText = Space(LOF(1))
        Get #1, , allText
        Close #1
        lines = Split(allText, vbCrLf)
        ReDim output(UBound(lines), 0)
        For i = 0 To UBound(lines)
            output(i, 0) = lines(i)
        Next
        With ActiveWorkbook.Worksheets(Mid(textFile, 10, 2))
            .Cells.Delete
            .Range("A1").Resize(UBound(output) + 1).Value = output
        End With
        textFile = Dir
    Wend

    MsgBox "Done"

End Sub

 

[ItalianPlatinum]

I am getting subscript out of range on this section. Anything you can see? I had a typo in my file names above, its actually Data1_Logs_XT. Feel like that could be the issue. Apologies I just noticed I wrote it wrong.

 

[John_w]

its actually Data1_Logs_XT.

Two more characters in the file name, therefore change 2 lines:

textFile = Dir(textFilesFolder & "Data1_Logs_*.txt")

        With ActiveWorkbook.Worksheets(Mid(textFile, 12, 2))

 

[ItalianPlatinum]

Thanks! Seems to be stopping here now.

Do I need to change this line too?

Debug.Print textFile, Mid(textFile, 10, 2)

 

[John_w]

Delete the Debug.Print line.

For the error - does the file contain more than 1,048,576 lines (maximum number of sheet rows)?

 

[ItalianPlatinum]

Ok I commented it out of the VBA. No, there is one file that gets close but not the limit with 987,623. All them combined do breach thought.

 

[John_w]

Click Debug on the error message. Hover the cursor over UBound(output) - what value is displayed?

If 0 is displayed, what are the file line endings? The code expects CR LF, so if they are different change lines = Split(allText, vbCrLf) as appropriate.