We help IT Professionals succeed at work.
Get Started

PowerShell / Excel SplitRow Code Debug

1,481 Views
Last Modified: 2012-05-09
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
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE