We help IT Professionals succeed at work.

PowerShell / Excel SplitRow Code Debug

gerhardub
gerhardub asked
on
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
Comment
Watch Question

View Solution Only

Commented:
You just need to activate each tab to set the split. 

# 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.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

gerhardub

Author

Commented:
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

jwarnken

Commented:
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