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
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)