question

MarkHanna-0100 avatar image
0 Votes"
MarkHanna-0100 asked RichMatheisen-8856 commented

Unable to get the paste property of the worksheet class error in powershell

I have the below script that will split and save an excel file based on the values of the first column via a PowerShell script. Here is how the excel file is built up (app 700k-1M rows depending on which file, as there are multiple files I need to split)

´´´ Column1 # Column2 # Column3 ´´´
´´´ AA # data # data # data ´´´
´´´ AA # data # data # data ´´´
´´´ AB # data # data # data ´´´
´´´ AC # data # data # data ´´´
´´´ AC # data # data # data ´´´
The result should be multiple files with filenames AA.xlxs, AB.xlxs, AC.xlxs and of course the according rows data.

The script is working but a small issue I am having is out of about 4000 files, a handful of them contain no data.

I receive an error when this occurs;

     Unable to get the Paste property of the Worksheet class
 At line:43 char:5
 +   $wksheet.Paste($wksheet.Range("A1"))
 +   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     +   CategoryInfo          : OperationStopped: (:) [], COMException
     +   FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

The file is still created with the correct naming convention and saves fine but it is missing the data related to the unique value. This does not happen each time I run the script either. Sometimes it will not throw an error, sometimes it could be 1 file or it could be 3 or 4. It's never the same file name either.

Is there any reason as to why this occurs and how do I go about fixing it?

My script is below;

 Function Create-Excel-Spreadsheet {
 Param($NameOfSpreadsheet)
    
 # open excel
 $objexcelnew = New-Object -ComObject Excel.application
 $objexcelnew.visible = $false
    
    
 # add a worksheet
 $workbook = $objexcelnew.Workbooks.Add()
 $xl_wksht= $workbook.Worksheets.Item(1)
 $xl_wksht.Name = $NameOfSpreadsheet
    
 return $workbook
 }
    
 $objexcelexis = New-Object -ComObject Excel.Application
 $wb = $objexcelexis.WorkBooks.Open(($path = "C:\Users\Desktop\test.xlsx")) # Change the path for the location of the excel file.
    
 $objexcelexis.Visible = $false
 $objexcelexis.DisplayAlerts = $false
 $ws = $wb.Worksheets.Item(1)
    
 $usedRange = $ws.UsedRange
 $usedRange.AutoFilter()
    
 $totalRows = $usedRange.Rows.Count
    
    
 $rangeForUnique = $usedRange.Offset(1, 0).Resize($UsedRange.Rows.Count-1) 
 [string[]]$UniqueListOfRowValues = $rangeForUnique.Columns.Item(1).Value2 | sort -Unique
    
 for ($i = 0; $i -lt $UniqueListOfRowValues.Count; $i++) {
     $newRange = $usedRange.AutoFilter(1, $UniqueListOfRowValues[$i])
    
     $workbook = Create-Excel-Spreadsheet $UniqueListOfRowValues[$i]
     $wksheet = $workbook.Worksheets.Item(1)
    
     $range = $ws.UsedRange.Cells
     $range.Copy()
    
     $wksheet.Paste($wksheet.Range("A1"))
     $xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
     $workbook.Activesheet.Cells.EntireColumn.Autofit();
     $wksheet = $Workbook.worksheets.Item(1)
     $wksheet.PageSetup.Orientation = 2
     $workbook.SaveAs("C:\Users\Desktop\" + $UniqueListOfRowValues[$i], $xlFixedFormat) # Change the save path for the xlsx files
     $workbook.Close($false)
    
     [System.Runtime.Interopservices.Marshal]::ReleaseComObject($rangeForUnique.Columns.Item(1))
     [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ws.UsedRange.Cells)
     [System.Runtime.Interopservices.Marshal]::ReleaseComObject($range)
     [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wksheet)
     [System.Windows.Forms.Clipboard]::Clear()
     [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
     [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objexcelexis)
     [System.GC]::Collect()
    
    }
windows-server-powershelloffice-scripts-excel-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered RichMatheisen-8856 commented

This should meet your objectives:

 $e = Import-Excel c:\Excel\JustATest.xlsx
 $p = ($e[0].psobject.properties.name)[0]                           # get the name of the 1st column
 $val = ""
 $oneshot = $true
 [array]$rows = @()
    
 $e | 
     Sort-Object -Property $p |
         ForEach-Object{
             if ($oneshot){
                 $oneshot = $false
                 $val = $_.$p                                     # prime the comparison
             }
             if ($val -eq $_.$p){
                 $rows += $_
             }
             else{
                 $WbName = "{0}{1}.xlsx" -f "c:\Excel\", $val   # create the name of the new file
                 $rows | Export-Excel -Path $WbName
                 $rows = @()
                 $rows += $_
                 $val = $_.$p                                     # next identifier
             }
         }
 if ($rows.count -gt 0){
     $WbName = "{0}{1}.xlsx" -f "c:\Excel\", $val   # create the name of the new file
     $rows | Export-Excel -Path $WbName
 }
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This is amazing - thank you so much! Works perfectly.

0 Votes 0 ·

Look, Ma . . . no COM!!! :-)

0 Votes 0 ·
RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered RichMatheisen-8856 commented

Not an Excel kinda guy, but don't you have to activate a worksheet before using it?

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Not 100% sure as I a new to this. Wouldn't this happen on each file rather than a few if this was the case? I added in to activate the sheet into the script but unfortunately still receive the error.

0 Votes 0 ·

Using COM is exhausting (and very often confusing). Have you looked into using the ImportExcel module?

This is copied from 549

 $excel = Open-ExcelPackage -Path "C:\temp\copiedfrom.xlsx"
 $ws = $excel.Workbook.Worksheets["Page 1"]
 $range = $ws.Cells["B2:C3"]
    
    
 $excel1 = Open-ExcelPackage -Path "C:\temp\pastedto.xlsx"
 $ws1 = $excel1.Workbook.Worksheets["Sheet1"]
 $ws1.Cells.Clear() # optional, I use this to make sure excel clears the contents and its cell formatting at the destination while reusing same excel
 $ws.Cells["B2:C3"].Copy($ws1.Cells["C2:D3"]) # Sourcerange.copy(DestinationRage or cell) #$range.Copy($ws1.Cells["C2"]) works as well
 Close-ExcelPackage $excel1 -show
 Close-ExcelPackage $excel

A benefit of using PowerShell-only code is that it runs anywhere, even if you don't have Excel installed on the machine (e.g. on servers).

0 Votes 0 ·
RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered MarkHanna-0100 commented

Using the ImportExcel module this will create a new xlsx file for each uniquely named row (using the contents of the 1st column).

 $e = Import-Excel c:\Excel\JustATest.xlsx
 $p = ($e[0]|Get-Member -MemberType NoteProperty)[0].Name    $ get the name of the 1st column
 $e | 
     Sort-Object -Property $p -Unique |
         ForEach-Object{
             $WbName = "{0}{1}.xlsx" -f "c:\Excel\", $_.$p   # create the name of the new file
             $_ | Export-Excel -Path $WbName
         }
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your efforts Rick - this is great and almost does what I need it to but did notice a couple of things when testing it;

  1. My sheet has multiple lines relating to a 'person' so the unique named row (unique identifier in column 1) could have more than 1. Your script picks the first row it finds and saves it and leaves the rest of the rows for that person behind.

  2. The file name is being saved based on the value in column C instead of the unique identifier in column 1.

I am not too familiar with the import-excel module but I will see if I can adjust your script.

0 Votes 0 ·

I managed to fix the issue with the filename being saved as column C value and it now saves as the unique value from column one but having trouble getting it to capture all the rows relating to one unique person.

0 Votes 0 ·

I was using the code you wrote to define "uniqueness":

 [string[]]$UniqueListOfRowValues = $rangeForUnique.Columns.Item(1).Value2 | sort -Unique

There was only your brief example of data to work with, and it happened to be that the column names in that sample sorted nicely into ascending values. I posted a new code sample that gets the column names in the order they appear in the data sample.

The new code sample should also handle the selection of all the rows for each user being written to individual files.

0 Votes 0 ·

Ah of course. Does it work differently within com-object vs import-excel? Although I am using sort -unique I also use a filter with a range and then do a count to get all the same values for the one person and then copy them into a new sheet.
Thanks again for the assistance.

0 Votes 0 ·