Excel SplitColumn and SplitRow(

[OldDog]

Hi,

If I set up Excel like so:

$xl = New-Object -comobject excel.application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)

and then do this: $xl.windows | gm

I see that there is a property called SplitRow and another called
SplitColumn.

SplitColumn Property int SplitColumn () {get} {set}
SplitRow Property int SplitRow () {get} {set}

So the question is how do I set these in my script?

And while we are at it, how about

Panes Property Panes Panes () {get}

I am told that this works in vbScript:

'Split Active Worksheet Window
With XL.ActiveWindow
.SplitColumn = 13
.SplitRow = 10
End With

TIA;

OldDog

 

[Marco Shaw [MVP]]

Like this:

$xl.activewindow.splitcolumn=2
$xl.activewindow.splitrow=2
$xl.activewindow.panes

Panes only has a "getter" method, so you can only read the property, while
the others have a getter and setter so you can actually modify the property.

I can't explain why you see those members with $xl.windows... I just
followed the VBScript code you provided.

Marco

"OldDog" <[email protected]> wrote in message
news:[email protected]

> Hi,
>
> If I set up Excel like so:
>
> $xl = New-Object -comobject excel.application
> $xl.Visible = $true
> $xl.DisplayAlerts = $False
> $wb = $xl.Workbooks.Add()
> $ws = $wb.Worksheets.Item(1)
>
> and then do this: $xl.windows | gm
>
> I see that there is a property called SplitRow and another called
> SplitColumn.
>
> SplitColumn Property int SplitColumn () {get} {set}
> SplitRow Property int SplitRow () {get} {set}
>
> So the question is how do I set these in my script?
>
> And while we are at it, how about
>
> Panes Property Panes Panes () {get}
>
> I am told that this works in vbScript:
>
> 'Split Active Worksheet Window
> With XL.ActiveWindow
> .SplitColumn = 13
> .SplitRow = 10
> End With
>
> TIA;
>
> OldDog
>
>
>

 

[Wolfgang Kais]

Hello old Dog.

"OldDog" wrote:

> Hi,
>
> If I set up Excel like so:
>
> $xl = New-Object -comobject excel.application
> $xl.Visible = $true
> $xl.DisplayAlerts = $False
> $wb = $xl.Workbooks.Add()
> $ws = $wb.Worksheets.Item(1)
>
> and then do this: $xl.windows | gm
>
> I see that there is a property called SplitRow and another called
> SplitColumn.
>
> SplitColumn Property int SplitColumn () {get} {set}
> SplitRow Property int SplitRow () {get} {set}
>
> So the question is how do I set these in my script?
>
> And while we are at it, how about
>
> Panes Property Panes Panes () {get}
>
> I am told that this works in vbScript:
>
> 'Split Active Worksheet Window
> With XL.ActiveWindow
> .SplitColumn = 13
> .SplitRow = 10
> End With

How about this:

$aw = $xl.ActiveWindow
$aw.SplitColumn = 5
$aw.SplitRow = 10
1..$aw.Panes.Count | % {
"The visible range of pane {0} has the address {1}." -f
$_, $aw.Panes.Item($_).VisibleRange.Address()
}

--
Regards,
Wolfgang