Make Excel Defined Names within a worksheet to be global
- by idazuwaika
Hi,
I wrote Powershell script to copy a worksheet from a workbook A to another workbook B. The worksheet contains define names for ranges within that sheet. Originally, the defined names are global in workbook A, ie. can be referenced from any worksheets within workbook A.
But now, after copy to worksheet B, the defined names are limited to that worksheet only. How to I programmatically (via Powershell script preferably) make all those named range global i.e. can be referenced from all worksheets within workbook B.
Some codes for clarity.
#Script to update SOP from 5.1 to 5.2
$missing = [System.Type]::missing
#Open files
$excel = New-Object -Com Excel.Application
$excel.Visible = $False
$excel.DisplayAlerts = $False
$newTemplate = "C:\WorkbookA.xls"
$wbTemplate = $excel.Workbooks.Open($newTemplate)
$oldSop = "C:\WorkbookB.xls"
$wbOldSop = $excel.Workbooks.Open($oldSop)
#Delete 'DATA' worksheet from old file
$wsOldData = $wbOldSop.Worksheets.Item("DATA")
$wsOldData.Delete()
#Copy new 'DATA' worksheet to old file
$wbTemplate.Worksheets.Item("DATA").Copy($missing,$wbOldSop.Worksheets.Item("STATUS"))
#Save
$wbOldSop.Save()
$wbOldSop.Close()
#Quit Excel
$excel.Quit()
