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

Question

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

   }

Answer 1

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
}

Answer 2

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

Answer 3

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
        }