Named Ranges and Excel Services

You can export (copy) a worksheet to make a new workbook. Right-click the sheet tab, select "Move or Copy...", then select "(new book)" from the dropdown at the top. Tick "Create a copy" if you want to retain the original sheet in the source workbook.



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.

Sub ExportWorksheetsToSharePoint()
    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)
    
    With Application
        .ScreenUpdating = False
        .StatusBar = "Started..."
    End With
    
    Set wbMain = ActiveWorkbook
    
    For Each ws In wbMain.Worksheets
        
        With ws
            
            .Select
            .Copy
            
            Application.StatusBar = "Processing " & .Name & "..."
            
        End With
        
        Set wbReport = ActiveWorkbook
        
        With wbReport
            
            For Each n In .Names
                
                sRangeAddress = WorksheetFunction.Substitute(n.RefersToLocal, ActiveSheet.Name, "")
                sRangeAddress = WorksheetFunction.Substitute(sRangeAddress, "=", "")
                sRangeAddress = WorksheetFunction.Substitute(sRangeAddress, "!", "")
                sRangeAddress = WorksheetFunction.Substitute(sRangeAddress, "'", "")
                
                Range(sRangeAddress).Select
                
                sName = WorksheetFunction.Substitute(n.Name, ActiveSheet.Name, "")
                sName = WorksheetFunction.Substitute(sName, "'", "")
                sName = WorksheetFunction.Substitute(sName, "!", "")
                
                n.Delete
                
                Selection.Name = sName
                
            Next n
            
            .SaveAs "Your Document Library path/" & ws.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook
            
            .Close False
            
        End With
        
    Next ws
    
    Set wbMain = Nothing
    
    Set wbReport = Nothing
    
    With Application
        .StatusBar = "Finished!"
        .Wait (Now + TimeValue("0:00:01"))
        .StatusBar = False
        .ScreenUpdating = True
    End With
    
End Sub
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"