Error with VBA to copy data from txt file to excel

[uhd04]

Hi,

i have a report created in txt using a powershell script and i'm trying to import data from report to excel using QueryTable. The report is similar to this but have much more rows


[TABLE="width: 600"]
<tbody>[TR]
[TD]Cluster[/TD]
[TD]Name[/TD]
[TD]Node[/TD]
[TD]State[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-------[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]-----[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Server1[/TD]
[TD]Cluster Disk[/TD]
[TD]Server1-nodeA[/TD]
[TD]Online[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Server1[/TD]
[TD]Cluster IP Address[/TD]
[TD]Server1-nodeA[/TD]
[TD]Online[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cluster[/TD]
[TD]Name[/TD]
[TD][/TD]
[TD]Node[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]-------[/TD]
[TD]----[/TD]
[TD][/TD]
[TD]----[/TD]
[TD]-----[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Disk D:[/TD]
[TD][/TD]
[TD]Server2-nodeA[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Disk E:[/TD]
[TD][/TD]
[TD]Server2-nodeA[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]DiskF:[/TD]
[TD][/TD]
[TD]Server2-nodeA[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]IP Address[/TD]
[TD][/TD]
[TD]Server2-nodeA[/TD]
[TD]Offline[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Server Services[/TD]
[TD][/TD]
[TD]Server2-nodeA[/TD]
[TD]Offline[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Network Name[/TD]
[TD][/TD]
[TD]Server2-nodeA[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Spooler[/TD]
[TD][/TD]
[TD]Server2-nodeA[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD="colspan: 2"]Communiquez ABCDEF Service[/TD]
[TD]Server2-nodeA[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD="colspan: 2"]Nettoyant Analyse Notice Look-up Service[/TD]
[TD]Server2-nodeA[/TD]
[TD]Online[/TD]
[/TR]
</tbody>[/TABLE]


And when i import it using vba to excel it comes up like this (Bold are the errors occurred while importing data)

[TABLE="class: grid, width: 650, align: left"]
<tbody>[TR]
[TD]Cluster[/TD]
[TD]Name[/TD]
[TD]Node[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]-------[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]-----[/TD]
[/TR]
[TR]
[TD]Server1[/TD]
[TD]Cluster Disk[/TD]
[TD]Server1-nodeA[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]Server1[/TD]
[TD]Cluster IP Address[/TD]
[TD]Server1-nodeA[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cluster[/TD]
[TD]Name[/TD]
[TD]Node State[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-------[/TD]
[TD]----[/TD]
[TD]---- -----[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Disk D:[/TD]
[TD]Server2-nodeA O[/TD]
[TD]nline[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Disk E:[/TD]
[TD]Server2-nodeA O[/TD]
[TD]nline[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Disk F:[/TD]
[TD]Server2-nodeA O[/TD]
[TD]nline[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]IP Address[/TD]
[TD]Server2-nodeA O[/TD]
[TD]ffline[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Server Services[/TD]
[TD]Server2-nodeA O[/TD]
[TD]ffline[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Network Name[/TD]
[TD]Server2-nodeA O[/TD]
[TD]nline[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Spooler[/TD]
[TD]Server2-nodeA O[/TD]
[TD]nline[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Communiquez ABCDEF Ser[/TD]
[TD]vice Server2-no[/TD]
[TD]deA Online[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Nettoyant Analyse Notice L[/TD]
[TD]ook-up Service S[/TD]
[TD]erver2-nodeA Online[/TD]
[/TR]
</tbody>[/TABLE]


























the QueryTable i'm using is

Sub ImportReport()
    
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;c:\CluterReport.txt", Destination:=Range( _
        "$A$1"))
        .Name = "CluRes_Log"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(14, 15, 19, 24)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

So could someone please advise on what i should change/update in my code or if there is another efficient way that i get the report like the below, i mean all columns are imported properly. Thanks

[TABLE="class: grid, width: 650, align: left"]
<tbody>[TR]
[TD]Cluster[/TD]
[TD]Name[/TD]
[TD]Node[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]-------[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]-----[/TD]
[/TR]
[TR]
[TD]Server1[/TD]
[TD]Cluster Disk[/TD]
[TD]Server1-nodeA[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]Server1[/TD]
[TD]Cluster IP Address[/TD]
[TD]Server1-nodeA[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cluster[/TD]
[TD]Name[/TD]
[TD]Node[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]-------[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]-----[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Disk D:[/TD]
[TD]Server2-nodeA[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Disk E:[/TD]
[TD]Server2-nodeA[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]DiskF:[/TD]
[TD]Server2-nodeA[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Virtual IP Address[/TD]
[TD]Server2-nodeA[/TD]
[TD]Offline[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Virtual Server Name[/TD]
[TD]Server2-nodeA[/TD]
[TD]Offline[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Network Name[/TD]
[TD]Server2-nodeA[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Spooler[/TD]
[TD]Server2-nodeA[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Communiquez ABCDEF Service[/TD]
[TD]Server2-nodeA[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]Nettoyant Analyse Notice Look-up Service[/TD]
[TD]Server2-nodeA[/TD]
[TD]Online[/TD]
[/TR]
</tbody>[/TABLE]