Make Excel Defined Names within a worksheet to be global

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

Answers


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.


Need Your Help

Macbook does not recognize Galaxy tab 7.7

android tabs transfer macbook

I have Macbook OS 10.7.2 and Galaxy Tab 7.7

Android - latest open source 3d libraries for android

android opengl-es 3d 3d-engine

Im currently looking at using min3d but came across a thread where the original creator said he was moving on and it wasnt going to be supported anymore. I dont think that would be to big of a prob...