0

I am using the Powershell script to load data from about 5 Excel sheets (as a sample) from column A to AL. In reality, I have 100 Excel Sheets with 25 Tabs in each file.

I have used the link How to use powershell to copy several excel worksheets and make a new one?

And it has been of great help. However, data from the first sheet is copied and entered into the new sheet but from the 2nd File onwards it starts giving the errors as below.

The 2nd loop should go to Last Used Row of the New Excel Sheet (row 151) and paste data from the 2nd Sheet. However I run into the error "Unable to get the Select property of the Range class". Then the data from the 2nd Sheet never gets pasted.

So out of the 5 sheets data from 2 sheets gets copied while the remaining data from the other 3 sheets never gets pasted onto the new Excel Sheet mainly due to the error "Unable to get the Select property of the Range class"

Error As Received

2nd Loop
151
Unable to get the Select property of the Range class
At C:\Users\Desktop\ExcelCopy_ETC 2.ps1:84 char:51
+ ... t.Range("A$(($objDestExcel.ActiveSheet.UsedRange.Rows|Select -Last 1) ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

2nd Loop
151
2nd Loop
Destination UsedRange Row: - 311
Unable to get the Select property of the Range class
At C:\Users\Desktop\ExcelCopy_ETC 2.ps1:84 char:51
+ ... t.Range("A$(($objDestExcel.ActiveSheet.UsedRange.Rows|Select -Last 1) ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

2nd Loop
Destination UsedRange Row: - 311
Unable to get the Select property of the Range class
At C:\Users\Desktop\ExcelCopy_ETC 2.ps1:84 char:51
+ ... t.Range("A$(($objDestExcel.ActiveSheet.UsedRange.Rows|Select -Last 1) ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))
At C:\Users\Desktop\ExcelCopy_ETC 2.ps1:70 char:5
+     $SourceWorksheet = $SourceWorkBook.WorkSheets.item("3. Automation ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

2nd Loop
Destination UsedRange Row: - 311
0
Maldred
  • 1,074
  • 4
  • 11
  • 33
NottyHead
  • 181
  • 4
  • 18
  • I'd suggest looking into the [Import Excel](https://blogs.technet.microsoft.com/heyscriptingguy/2015/11/25/introducing-the-powershell-excel-module-2/) package so you don't have the headache of working with the Excel com object since it has such generic error messages. – Maximilian Burszley Nov 01 '17 at 15:09
  • But ImportExcel cannot export data from a xlsm file... I have 100 Excels in xlsm format. – NottyHead Nov 03 '17 at 10:12

0 Answers0