Segue código pronto para importar Arquivo TXT para o Excel automaticamente

Autor: Noorain Ansari <[email protected]>

Sub convert_txtfile()
On Error GoTo abc
Dim txtifile
txtifile = Application.GetOpenFilename("TXT (*.TXT),*.TXT", , "Please select Text file...")
Workbooks.OpenText Filename:=txtifile, Origin:=xlWindows, StartRow:=1
With ActiveWorkbook
ActiveSheet.Move After:=.Worksheets(.Worksheets.Count)
End With
abc:
Exit Sub
End Sub