VBA code to convert text file to Excel 2010

Do you want to choose you TXT file manually? A window can be opened asking to select the file.
Are those fields the only ones you want to retreive?
Try this macro:

Public Sub Extract_Data()
Dim Open_File As String, Str As String, R_ow As Long
Open_File = Application.GetOpenFilename()
Open Open_File For Input As #1
On Error GoTo Err_or 'Just in case of an error so the TXT file is closed properly
R_ow = Cells(Rows.Count, 1).End(xlUp).Row
'---Search data through the file
Do While Not EOF(1)
   Line Input #1, Str
   If InStr(1, Str, "Supplier Name:", vbTextCompare) <> 0 Then
      Cells(R_ow, 1) = Trim(Mid(Str, 16, 42)) 'Supplier name
      Cells(R_ow, 3) = Trim(Mid(Str, 72, 30)) 'Taxpayer ID
      Line Input #1, Str
      If InStr(1, Str, "Supplier num:", vbTextCompare) <> 0 Then
        Cells(R_ow, 2) = Trim(Mid(Str, 16, 30)) 'Supplier Number
      End If
   End If
   If InStr(1, Str, "Site Name", vbTextCompare) <> 0 Then
      Line Input #1, Str 'This one reads the dashes
      Line Input #1, Str 'This one reads the site name and address1
      Cells(R_ow, 4) = Trim(Mid(Str, 9, 15)) 'Site name
      Cells(R_ow, 5) = Trim(Mid(Str, 25, 35)) 'Address1
      Line Input #1, Str 'This one reads the City, State and Zip code
      With CreateObject("VBScript.RegExp")
         .IgnoreCase = True
         .MultiLine = True
         .Global = True
         'Pattern is most important as it returns us the different value we are looking for
         .Pattern = "(\w{1,20}).(\w{2}).(\w{5,7})"
         Str = Trim(Str)
         If .test(Str) Then
            Cells(R_ow, 6) = .Replace(Str, "$1") 'City
            Cells(R_ow, 7) = .Replace(Str, "$2") 'State
            Cells(R_ow, 8) = .Replace(Str, "$3") 'Zip code
         End If
      End With
   End If
   'Here, we look for the word "Page:" that marks the separation of 2 suppliers in the report
   'we increase the row by one to write on the next row
   If InStr(1, Str, "Page:", vbTextCompare) <> 0 Then R_ow = R_ow + 1
Loop
Err_or:
Close #1
End Sub

Please note that it does not erase data before iimporting the TXT file. It appends data to the existing one.