Import text files in excel within selected cells using VBA

Hi Josuec52 and welcome to the Forum

If I understand you correctly, you want to import text files but not have to import values into separate cells (your sample PID file has data separated by tabs so the Tab=True part of your macro forces data into separate cells).

Furthermore, to avoid the macro stopping at the first blank line, I suggest you use VBA's Open statement instead (Microsoft guideline is here: Open statement).

In the first attached file, you'll see a new lilac button "Import text to a cell" on the worksheet "Dust Raw Data". That has the following macro assigned and allows you to pick a sheet and cell to import any text file (with each line of text in the same column). It will identify the file imported and include blank lines.

Sub ImportText2()

    Dim UWPID As Variant
    Dim fileFilterPattern As String
    Dim Dest As Range, n As Long

    ' Just look at text files
    fileFilterPattern = "Text Files *.txt,*.txt"

    UWPID = Application.GetOpenFilename(fileFilterPattern)

    If UWPID = False Then
        MsgBox "No file selected."
    Else
        ' Get user to pick a single cell (and loop until they do)
        Do
            Set Dest = Application.InputBox("Pick a SINGLE cell to start the text input" & Chr(13) & "(Note: needs clear cells below)", Type:=8)
        Loop Until Dest.Count = 1

        ' Show the sheet containing that cell
        Dest.Parent.Activate
        ' Write source in chosen file
        Dest.Value = "Imported from file: " & UWPID
        Dest.Font.Bold = True

        Application.ScreenUpdating = False

        Open UWPID For Input As #1
        ' Look at each line of the file
        Do Until EOF(1)
            Line Input #1, UWPID
            n = n + 1
            Dest.Offset(n, 0).Value = UWPID
        Loop

        ' Done so close the file for input
        Close #1
        'expand column to show data
        Columns(Dest.Column).AutoFit
    End If
    Application.ScreenUpdating = True

End Sub

It should work for both of your file types.

Revision 22 April 2023:

Further to your discussion point preferring to paste from the active cell and to separate cells by tab or comma, the second attached sample import text file to active cell v0_b.xlsm has the lilac button caption "Import text to active cell" on the worksheet "Dust Raw Data" and has the following revised macro assigned to it (with comments to guide you). Note that the variable UWPID above is replaced by ThisLine (since it works with your UWPID and UW DP files):

Sub ImportText3()

    Dim ThisLine As Variant
    Dim fileFilterPattern As String
    Dim n As Long

    ' Just look at text files
    fileFilterPattern = "Text Files *.txt,*.txt"

    ThisLine = Application.GetOpenFilename(fileFilterPattern)

    If ThisLine = False Then
        MsgBox "No file selected."
        Exit Sub
    End If

    Application.ScreenUpdating = False

    Open ThisLine For Input As #1
    ' Look at each line of the file
    Do Until EOF(1)
        Line Input #1, ThisLine
        ' If the line contains something
        If Len(ThisLine) > 0 Then
            ' Replace any commas with tabs (ASCII character 9)
            ThisLine = Replace(ThisLine, ",", Chr(9))
            ' Divide string into an array by tab
            ThisLine = Split(ThisLine, Chr(9))
            ' Copy array to a resized range
            ActiveCell.Offset(n, 0).Resize(1, UBound(ThisLine) + 1).Value = ThisLine
        End If
        ' Point to next cell below
        n = n + 1
    Loop

    ' Done so close the file for input
    Close #1

    Application.ScreenUpdating = True

End Sub

The key changes are in bold above and mean files with tab or comma separations have the string converted to an array so they are copied to 1 or more cells (if there's a tab or comma in the line)

Hope this works for you.