Importing Text File into specific cell range using VBA

[jaymcgary]

Hi all, appreciative for any help.

I am using the following code to import a text file created via PowerShell. Except, I cannot seem to select a specific range of cells for the import to happen at. It gives me a 1004 error.

Here is the code!

Sub PasteTextFileContent () 
    Dim wbExcel As Workbook, wbText As Workbook
    Dim wsExcel As Worksheet
    Set wbExcel = ThisWorkbook 'specify here which Excel file the text file’s content is to be pasted into
    Set wsExcel = wbExcel.Sheets(1) 'specify here which worksheet to use
    Set wbText = Workbooks.Open("C:\Test\TestFile.txt") 'add here the path of your text file

    wbText.Sheets(1).Cells.Copy wsExcel.Cells

    wbText.Close SaveChanges:=False

End Sub

[#2]

This line is wrong:
wbText.Sheets(1).Cells.Copy wsExcel.Cells
it should read something like:
wbText.Sheets(1).UsedRange.Cells.Copy wsExcel.Cells(1,1)
(note I've added the UsedRange object to the left side to limit what's copied to the actual data)