CODEWITHSUNDEEP
excelpowershell
Andy
Andy

Reputation: 217

How to select Excel range in powershell

I have the following code in a function:

function Excel2PPT{


param ($xslx, $targetsheet, $targetrange, $pptx, $targetslide, $npath)
    $Excel = New-Object -ComObject Excel.Application
    $workbook = $Excel.workbooks.open($xslx)
    $Excel.Visible= $true
    $worksheet = $workbook.worksheets.Item($targetsheet)

$range = $worksheet.Range($targetrange)
$range.select()
$range.copy()

I am using it in another function which passes to it "A1:O12" as $targetrange

This worked in previous testing but now yields an error and unexpected behavior.

I reference it as:

Excel2PPT $IncomingABCExcel 1 'A1-O12' $testPPT2 $Targetslide $testPPT3

The error is:

Exception from HRESULT: 0x800A03EC
At C:\...\Excel2PPT.ps1:16 char:1
+ $range = $worksheet.Range($targetrange)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

You cannot call a method on a null-valued expression.
At C:\...\Excel2PPT.ps1:17 char:1
+ $range.select()
+ ~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\...\Excel2PPT.ps1:18 char:1
+ $range.copy()
+ ~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

What can I do to fix it? It should be copying and pasting $targetrange to the intended PPT slide but it appears to be copying and pasting "$IncomingABCExcel" (not the variable, the variable name) onto the slide.

Upvotes: 1

Views: 3010

Answers (1)

Owain Esau
Owain Esau

Reputation: 1922

The issue you are having is that you are using 'A1-012' As the range. This should be 'A1:012':

function Excel2PPT {
    param ($xslx, $targetsheet, $targetrange, $pptx, $targetslide, $npath)

    $Excel = New-Object -ComObject Excel.Application
    $workbook = $Excel.workbooks.open($xslx)
    $Excel.Visible= $true
    $worksheet = $workbook.worksheets.Item($targetsheet)
    $range = $worksheet.Range($targetrange)
    $range.select()
    $range.copy()
}

Excel2PPT $IncomingABCExcel 1 'A1:O12' $testPPT2 $Targetslide $testPPT3

Upvotes: 1

Related Questions