Selecting multiple ranges simultaneously in Excel using Powershell

Question

I'm trying to use powershell to create pie charts in excel. I can highlight ranges with .Select() or .Activate() but I can't simultaneously select two separate ranges. I need to select them simultaneously so that the pie chart has the correct labels. Can't find any guidance online so any help would be greatly appreciated.

$headers = $ws.Cells.Item(1,1).EntireRow
$headers.Activate()
$ws.Range("A3:ZZ3").Activate()

I'm not sure if it answers your question directly, but this might help: learn-powershell.net/2012/12/24/… — Mark Wragg
– Mark Wragg, Commented Feb 28, 2017 at 11:22
It's a good source for general chart syntax but unfortunately as with most charts, he includes the whole table and therefore doesn't have to create a dynamic range of non-consecutive cells. — TLOwater
– TLOwater, Commented Feb 28, 2017 at 11:30
Would a quicker fix just be to put the headers next to your pie chart so that you can select them within a single range? — Mark Wragg
– Mark Wragg, Commented Feb 28, 2017 at 11:32
I had considered it as a last result, fortunately TechSpud's solution works to select the non-consecutive ranges so I don't have to deal with that! — TLOwater
– TLOwater, Commented Feb 28, 2017 at 11:40

TechSpud · Accepted Answer · 2017-02-28 11:34:22Z

3

This article suggest a way to select multiple ranges. Using your code above, try this...

[object[]]$args1 = ($headers, $ws.Range("A3:ZZ3")) 
$union=$xl.GetType().InvokeMember("Union", [System.Reflection.BindingFlags]::InvokeMethod, $null, $xl, $args1) 
$union.Select()

...where $xl is defined as $xl = New-Object -ComObject Excel.Application

answered Feb 28, 2017 at 11:34

TechSpud

3,5581 gold badge30 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

Selecting multiple ranges simultaneously in Excel using 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