Macro that imports data from a text file into an excel table

[Stacked295]

Hello,

I'm pretty new to messing around with VBA macros. I have a text file located at C:\Test\test.exd that looks like this:

Those numbers translate as the information in this screen shot:

What I'm looking to do is using a macro import the raw data from the first screen shot's text file and import it into a premade table like the one in the screen shot below. The system number and the miscellaneous are throw away data that wouldn't need to move over.

Any help that count be provided would be much appreciated.
Thank you.

 

[Herakles]

Hello,

I'm pretty new to messing around with VBA macros. I have a text file located at C:\Test\test.exd that looks like this:

View attachment 99011

Those numbers translate as the information in this screen shot:

View attachment 99014

What I'm looking to do is using a macro import the raw data from the first screen shot's text file and import it into a premade table like the one in the screen shot below. The system number and the miscellaneous are throw away data that wouldn't need to move over.
View attachment 99015
Any help that count be provided would be much appreciated.
Thank you.

Try this on a copy of your workbook.

Change this line substituting the word 'Tables' for the name of the worksheet containing the table.
Set WsTable = Worksheets("Tables")

Change this line substituting the word 'Table1' for the name of the table.
Call subInsertDataIntoTable(WsTable.ListObjects("Table1"), arr)

Public Sub ImportTextComma()
Dim arr() As Variant
Dim WsTable As Worksheet
Dim WsData As Worksheet
Dim strFilename As String

    ActiveWorkbook.Save
    
    Set WsTable = Worksheets("Tables")
    
    Worksheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = Format(Date, "ddmmyy") & Format(Time, "hhmmss")
    Set WsData = ActiveSheet
    
    strFilename = "C:\Test\test.ecd"
    
    Call subImportCommaDelimitedFile(strFilename, 1, WsData.Range("A1"))
    
    With WsData
        .Columns("A:B").Delete
        .Columns("O:R").Delete
        .Columns("A:F").EntireColumn.Insert
        .Columns("O").EntireColumn.Insert
        .Cells.EntireColumn.ColumnWidth = 6
        arr = .Range("A1:U" & .UsedRange.Rows.Count)
    End With
    
    Call subInsertDataIntoTable(WsTable.ListObjects("Table1"), arr)
    
    On Error Resume Next
    Application.DisplayAlerts = False
    ' WsData.Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

End Sub

Public Sub subImportCommaDelimitedFile(ByVal ACSV_FullName As String, ByVal AStartrow As Long, ByVal ADest As Range)
    
    With ADest.Parent.QueryTables.Add(Connection:="TEXT;" & ACSV_FullName, Destination:=ADest)
        .TextFileStartRow = AStartrow
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
        .Delete
    End With
    
End Sub

Private Sub subInsertDataIntoTable(tbl As ListObject, ar)
Dim newrows As Long: newrows = 1 + UBound(ar, 1) - LBound(ar, 1)
  
    If Not tbl.DataBodyRange Is Nothing Then
        tbl.DataBodyRange.EntireRow.Delete
    End If
  
    If newrows > 1 Then
        tbl.HeaderRowRange.Resize(newrows - 1).Offset(2).EntireRow.Insert
        tbl.HeaderRowRange.Resize(newrows, 1 + UBound(ar, 2) - LBound(ar, 2)).Offset(1).Value = ar
    End If
    
End Sub