Multiple .dat file import into Excel - VBA

Hi and welcome to the Forum.

You declared datfilesToOpen as Variant so the file dialog will create an array (of file path strings) if one or more files are picked. If not (i.e. Cancel is pressed), the array is not initialized so this test will work:

If IsArray(datfilesToOpen) = False Then

        MsgBox "No file selected"

Else

On your second question, you did not include an embedded sample data file with your file above but this should work. In the revised file attached (and code extract below), I've changed the items in bold so that the file name only (from each datfile) is added to the file (with "¦¦¦" before it). That gets split by the .TextToColumns command (to fixed widths) so another loop reconstitues the file name (from all possibe slpit columns) then puts it  (e.g. "From :<<file name>>") in what becomes column A and colours the cell font red .

Sub ImportText_bckp()

    Dim fso As Object
    Dim qt As QueryTable
    Dim cn As Variant
    Dim datfilesToOpen As Variant, datfile As Variant


    Application.ScreenUpdating = False
    Set fso = CreateObject("Scripting.FileSystemObject")

    ' Opens folder to select files
    datfilesToOpen = Application.GetOpenFilename _
                (FileFilter:="Text Files (*.dat), *.dat", _
                MultiSelect:=True)

    If IsArray(datfilesToOpen) = False Then
        MsgBox "No file selected"
    Else

        ' Clears current entries
        If IsEmpty(Range("C4").Value) = False Then
            Range("C4").Select
            Range(Selection, Selection.End(xlToRight)).Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.ClearContents
        End If

        ' Inserts column for raw data entry
        Columns("A:A").Select
        Range("A3").Activate
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

            With ActiveSheet

                For Each datfile In datfilesToOpen


                    importrow = 1 + .Cells(.Rows.Count, 1).End(xlUp).Row
                    '' add  ¦¦¦ plus just the files name
                    Cells(importrow, 1).Value = "¦¦¦  From: " & Right(datfile, Len(datfile) - InStrRev(datfile, "\"))

                     With ActiveSheet.QueryTables.Add(Connection:= _
                    "TEXT;" & datfile, Destination:=.Cells(importrow + 1, 1))
                        .TextFileStartRow = 16
                        .TextFileParseType = xlFixedWidth
                        .TextFileTabDelimiter = True
                        .Refresh BackgroundQuery:=False
                        .AdjustColumnWidth = False
                     End With

                Next datfile

                For Each qt In ActiveSheet.QueryTables
                qt.Delete
                Next qt

                For Each cn In ThisWorkbook.Connections
                cn.Delete
                Next cn

            End With

            Range("A2").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.TextToColumns Destination:=Range("D4"), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(35, 1), Array(48, 1), Array(59, 1), _
            Array(65, 1), Array(76, 1)), TrailingMinusNumbers:=True

            ' find the ¦¦¦ files names, remove that can make text red
            For n = 1 To Cells(Rows.Count, 4).End(xlUp).Row
                If Cells(n, 4) = "¦¦¦" Then
                    'reform the file name
                    Cells(n, 2) = Cells(n, 5) & Cells(n, 6) & Cells(n, 7) & Cells(n, 8) & Cells(n, 9)
                    Range(Cells(n, 4), Cells(n, 9)).Value = ""
                    Cells(n, 2).Font.Color = vbRed
                End If
            Next n

            Columns("A:A").Delete Shift:=xlToLeft

            Columns("B:J").AutoFit
            Range("A4").Select

            MsgBox "Successfully imported .dat files", vbInformation, "SUCCESSFUL IMPORT"
    End If
Application.ScreenUpdating = True
End Sub

Further to your discussion below, the code above has been modified by changing this section (which finds "¦¦¦") to create/replace the MSN for the cells that follow and also then delete that particular line:

Dim MSN as string
.
.
.
    ' find the ¦¦¦ files names (count=Ubound), remove them then add MSN
    For n = 4 To Cells(Rows.Count, 4).End(xlUp).Row - UBound(datfilesToOpen)
        If Cells(n, 4) = "¦¦¦" Then
            'reform the file name
            Cells(n, 2) = Cells(n, 5) & Cells(n, 6) & Cells(n, 7) & Cells(n, 8) & Cells(n, 9)
            'Extract MSN
            MSN = Chr(39) & Mid(Cells(n, 2), 2, 4) 'assumes format "M*****.dat", add leading '
            Cells(n, 2).EntireRow.Delete 'delete row
            n = n - 1 'rewind counter for deletion
            Else 'write MSN
            Cells(n, 2) = MSN
        End If
        
    Next n

New file atttached.

Hope this works for you.