Copying from text file to excel

[S10th]

I have to copy data from a text file to excel

I tried recording it but all I managed to get was this:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
End Sub

I was wondering how to open the text file and copy all the contents into A1 (but without wrap text) so each line on the text file is a new one on the excel file - blanks included.

 

[alansidman]

On the data tab of the ribbon, select Get & Transform. From Text/CSV. Bring into PQ Editor and then Close and Load on the Home Tab

 

[S10th]

Thanks for the quick response! Is there no way to automate? Only reason is I need to run this about 100 times in today every month - the report that is generated by another program (it spits out the same file regardless of the parameters). If it's not possible, I'll take the Get and Transform.