How to select Excel range in powershell

Question

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.

Where are you defining $targetrange? and have you checked if it is NULL? — Owain Esau
– Owain Esau, Commented Nov 28, 2019 at 3:33
Can you print the value of $targetRange prior to line 16 $worksheet = $workbook.worksheets.Item($targetsheet) ? — Owain Esau
– Owain Esau, Commented Nov 28, 2019 at 3:35
I tried and it appears to be null, which matches the behavior but that's so odd. Am I not passing it in correctly? — Andy
– Andy, Commented Nov 28, 2019 at 3:39
It could be that you are not passing to the function correctly or that you have not assigned a value to the variable beforehand, just print the value of it before the function call. If it prints then you know it is in the function call. — Owain Esau
– Owain Esau, Commented Nov 28, 2019 at 3:40

Owain Esau · Accepted Answer · 2019-11-28 04:31:33Z

1

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

edited Nov 28, 2019 at 4:31

answered Nov 28, 2019 at 4:15

Owain Esau

1,9222 gold badges23 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to select Excel range in powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related