This is where it get’s fun. Let’s imagine if your workbook has several named ranges that use the same name on different worksheets. Excel does allow you to do this, and even though it’s something that can cause confusion, it can be a good thing if used “wisely”.
The first name will be workbook-scoped by default, all subsequent names will be worksheet-scoped. If you export (copy) worksheets with worksheet-scoped named ranges, they will not magically change over to being workbook-scoped. They will remain worksheet-scoped, and what’s worse, you won’t be able to (manually) change them to be workbook-scoped. Perhaps this would not bother you normally, but if you were planning to use the named ranges of these new workbooks with Excel Services, you will run into trouble. This is because Excel Web Access web parts only work with workbook-scoped range names.
What to do? The simplest solution is to delete the names and replace them. This is what the code below does. First it exports each worksheet, then it loops though the named ranges. (Don’t use this for dynamic ranges – they won’t work with Excel Services anyway). While looping, the named range is selected, the name is deleted, then a new name is set for the selected range that is workbook-scoped. (It does not matter if the original named range happened to be workbook-scoped)
Finally the new workbook is published (saved) to a document library in SharePoint.
Dim n As Name
Dim ws As Worksheet
Dim wbMain As Workbook
Dim wbReport As Workbook
Dim sRangeAddress As String
Dim sName As String
'Note: You can watch the macro progress in the Status Bar (at the bottom of Excel)
.ScreenUpdating = False
.StatusBar = "Started..."
Set wbMain = ActiveWorkbook
For Each ws In wbMain.Worksheets
Application.StatusBar = "Processing " & .Name & "..."
Set wbReport = ActiveWorkbook
For Each n In .Names
sRangeAddress = WorksheetFunction.Substitute(n.RefersToLocal, ActiveSheet.Name, "")
sRangeAddress = WorksheetFunction.Substitute(sRangeAddress, "=", "")
sRangeAddress = WorksheetFunction.Substitute(sRangeAddress, "!", "")
sRangeAddress = WorksheetFunction.Substitute(sRangeAddress, "'", "")
sName = WorksheetFunction.Substitute(n.Name, ActiveSheet.Name, "")
sName = WorksheetFunction.Substitute(sName, "'", "")
sName = WorksheetFunction.Substitute(sName, "!", "")
Selection.Name = sName
.SaveAs "Your Document Library path/" & ws.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook
Set wbMain = Nothing
Set wbReport = Nothing
.StatusBar = "Finished!"
.Wait (Now + TimeValue("0:00:01"))
.StatusBar = False
.ScreenUpdating = True
Now you have each worksheet published as a workbook with all of the named ranges ready to be accessed by Excel Services. With versioning set up the right way in the document libraries, you can make changes to your main workbook, then run the code to update each “reporting” workbook. Notice how I use
Selection.Name = sName
to set the name again. I'm sure you will find that easier than using
ActiveWorkbook.Names.Add Name:="myName", RefersToR1C1:="=Sheet1!R1C1"