PowerShell to Rename Excel Sheet
<p>I've multiple excel file (xls) on a folder. Each workbook contain only one excel sheet. I need to rename each of the excel sheet name to 'Result'. How could I do that by PowerShell ? I'm able to get the sheet name from particular workbook by below command. But How could I write a PowerShell script that can read multiple workbooks, and rename each of the excel sheet to 'Result'. </p>

<pre><code>$xldoc = new-object -comobject Excel.application
$workbook = $xldoc.Workbooks.Open("sample.xls")
$worksheet = $workbook.worksheets.item(1)
$worksheet.name
</code></pre>

<p>Here the script that can rename excel sheet tab, but it will prompt me to confirm save the file or not. How could I modified the code so it auto saved without prompt message box ?</p>

<pre><code>$xlspath = "D:\Testing\RenameSheet\Testing2.xls"
$xldoc = new-object -comobject Excel.application
$workbook = $xldoc.Workbooks.Open($xlspath )
$worksheet = $workbook.worksheets.item(1)
$worksheet.name = "Result"
$worksheet.SaveAS = ($xlspath)
$worksheet.Close()
$xldoc.Quit()
</code></pre>

<p>I able to get the result here the function that did the trick. Please closed this topic. Thanks</p>

<pre><code>Function RenameTab ($ExcelFileName, $OutLoc)
{
$excelFile = "D:\Testing\RenameSheetAdvance\" + $excelFileName + ".xls"
$xldoc =  New-Object -ComObject "Excel.Application"
$xldoc.Visible = $false
$xldoc.DisplayAlerts = $false

$workbook = $xldoc.Workbooks.Open($excelFile)
foreach ($worksheet in $workbook.Worksheets)
{
    $n = $ExcelFileName ##+ "_" + $worksheet.Name
    $worksheet = $workbook.worksheets.item(1)
    $worksheet.name = "Sheet1"
    $workbook.SaveAs($OutLoc + $n + ".xls")
    $workbook.Close()
}
$xldoc.Quit()
}
</code></pre>