Link to home
Start Free TrialLog in
Avatar of gerhardub
gerhardub

asked on

PowerShell / Excel SplitRow Code Debug

Can anyone figure out why this code is not creating a split-row on EACH worksheet?

Here is what I've got:

# Open Excel document

$ExcelDocument = New-Object -comobject Excel.Application

# Name the Excel document using seconds for testing at this point

$ExcelPath = "C:\ServerAudit_"+([datetime]::Now).tostring("yyyyMMddhhmmss")+".xlsx"

#Show the Excel Documents?
$ExcelDocument.visible = $True

#Display those pesky alerts?
$ExcelDocument.DisplayAlerts = $False

#Create a new Excel Document (aka WorkBook)
$Workbook = $ExcelDocument.Workbooks.Add()

#Create worksheet tabs for each server, since Excel starts with three, add tabs if we have more than three servers.

If ($b.count -le 3) {for ($j=1; $j -le ($b.count-3); $j++) {$Sheet = $Workbook.WorkSheets.add()}}

# Determine Number of Servers

$limit = $b.Count

# Write the Excel sheet tab names
							
For ($j=1; $j -le $limit; $j++)
{
    Write-Host "Creating worksheet tab for ";$ServerList[$j]
    $CurrentSheet = $Workbook.WorkSheets.Item($j)
    $CurrentSheet.name = $ServerList[$j]
    $CurrentSheet.application.activewindow.splitcolumn = 0
    #Where we want to split the screen for scrolling in Excel
    $CurrentSheet.application.activewindow.splitrow = 16
    $CurrentSheet.application.activewindow.freezepanes = $true
}

Open in new window


Unfortunately, this works for the FIRST worksheet, and then all of the other worksheets do get the split-row functionality.... what am I missing?

Thanks All
ASKER CERTIFIED SOLUTION
Avatar of jwarnken
jwarnken
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gerhardub
gerhardub

ASKER

Any idea how a set a column to a specific width in this code?

PS... thanks that fixed it!


For ($j=1; $j -le $limit; $j++)
{
    Write-Host "Creating worksheet tab for ";$ServerList[$j]
    $CurrentSheet = $Workbook.WorkSheets.Item($j)
    $CurrentSheet.activate()
    $CurrentSheet.name = $ServerList[$j]
    $CurrentSheet.application.activewindow.splitcolumn = 0
    #Where we want to split the screen for scrolling in Excel
    $CurrentSheet.application.activewindow.splitrow = 16
    $CurrentSheet.application.activewindow.freezepanes = $true

Open in new window

sure take a look at this post http://www.eggheadcafe.com/software/aspnet/32708144/change-width-of-excel-col.aspx
$c.columns.item(1).columnWidth = 50
$c.columns.item('a').columnWidth = 50

$c.cells.item(1).columnWidth = 50
$c.range('a:a').columnwidth = 50

Open in new window