CODEWITHSUNDEEP
excelpowershell
MoriteUK
MoriteUK

Reputation: 27

renaming multiple excel worksheets with powershell

I am trying to use powershell to remane the worksheets in a workbook but the script I have only renames the same worksheet.

$xl = New-Object -comobject Excel.Application     
$xl.visible = $true
$xl.DisplayAlerts = $false
$xl.sheetsInNewWorkbook = $batches 
$xl.workbooks.add()

for($i=1;$i -lt $batches;$i++){
$ws1 = $ws.sheets.item($i) 
$ws1.activate()     
$ws1.name = "Batch$i" 
}

Batches = 12

What am I doing wrong?

Tia

Andy

Upvotes: 0

Views: 1163

Answers (1)

Fabian
Fabian

Reputation: 2086

You didn't add the code that defines the content of the variable: $ws

I retrieved the created workbook in the variable $workbook and replaced $ws by $workbook.Sheets.Item($i) and it seems to be working fine (your code is looping only 11 times by the way). If it doesn't answer your question please add more details.

   $batches = 12;
$xl = New-Object -comobject Excel.Application     
$xl.visible = $true
$xl.DisplayAlerts = $false
$xl.sheetsInNewWorkbook = $batches 
$workbook = $xl.Workbooks.add()

for($i=1;$i -lt $batches;$i++){

$ws1 = $workbook.Sheets.Item($i) 
$ws1.activate()     
$ws1.name = "Batch$i" 
}

Upvotes: 1

Related Questions