Make Excel Defined Names within a worksheet to be global

Question

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()  

Answer 1

I have this problem when copying worksheets with named ranges on them from directly in excel itself. I had this problem just last week. I couldn't find out why, and I suspect PowerShell really doesn't have anything to do about it. My only recourse seemed to be to drop and recreate the named ranges on the secondary sheets.