Renaming Excel Sheets Exported from Reporting Services 2005 via an Excel Add-In

Intro

When a Reporting Services 2005 report with multiple pages is exported to excel to creates an excel document with multiple sheets where the first sheet is an index sheet or document map and the remaining sheets are report pages. Reporting Services 2005 gives these sheets generic names Sheetxy. Unlike the situation with Reporting Services 2008 [R2] there's no straight forward way of telling Reporting Services 2005 how name the report pages. One way to rename this sheets in excel to match the entries in the document map (which are more meaningful than sheetXY) is through an excel add-in. Below is an Excel add-in written in VB to rename sheets based on the document map. The code is tested on Windows XP/7 with Office 2003 Professional/Basic. If you decide to use the code below, make sure it has no unwanted side-effects in your environment and make sure you test it.

The basic idea of the code is:

• Each document map entry points to a named range (which is stored in the built-in array Names
• Using the range name we can find out the sheet that's being pointed to by an entry in the document map
• Once the sheet is found, its name is changed to that of the hyperlink's DisplayText property of the pointing document map entry.
  

Steps to Create Excel Add-In using VBA

1. Open a new excel workbook and go to the Visual Basic Editor
2. In the left pane, right-click on VBAProjects (Book1) and Insert -> module. This module will have the code that will rename the sheets. The code in this module will be called when the custom menu item is clicked.
3. This code does the renaming (you can paste in the newly created module
   

   Private Const INVALID_CHARS As String = "[]*/\?:"
   Private Const SHEET_NAME_MAX_LENGTH As Integer = 31
   Private Const DOCMAP_SHEET_NAME As String = "document map"
   Private Const DOCMAP_SHEET_NEW_NAME As String = "__INDEX__"
   Public Sub RunRenameSheetsCode()
       If Not Application.ActiveWorkbook Is Nothing Then
           Call RenameSheets(Application.ActiveWorkbook)
       End If
   End Sub
   Public Sub RenameSheets(ByRef wb As Workbook)
       On Error GoTo Error
       Dim sh As Worksheet
       Dim row As Integer
       Dim cell As Range
       Dim newName As String
       row = 1
       If Not IsFirstSheetDocMap(wb) Then
           Exit Sub
       End If
       Do While True
           Set cell = wb.Sheets(1).Range("$A$" & CStr(row))
           If Len(cell.Value) <= 0 Then
               Exit Do
           End If
           If cell.Hyperlinks.Count > 0 Then
               Set sh = GetSheetByRangeName(wb, cell.Hyperlinks(1).SubAddress)
               If Not sh Is Nothing Then
                   newName = CleanSheetName(cell.Value)
                   sh.name = newName
               End If
           End If
           row = row + 1
       Loop
       wb.Worksheets(1).name = DOCMAP_SHEET_NEW_NAME
       Exit Sub
   Error:
        MsgBox ("An error occurred while trying to rename sheets")
   End Sub
   Public Function CleanSheetName(ByVal name As String)
       Dim newName As String
       newName = name
       For i = 1 To Len(INVALID_CHARS)
          newName = Replace(newName, Mid(INVALID_CHARS, i, 1), "")
       Next
       newName = Left(newName, SHEET_NAME_MAX_LENGTH)
       CleanSheetName = newName
   End Function
   Public Function GetSheetByRangeName(ByRef wb As Workbook, rangeName As String) As Worksheet
       Dim i As Integer
       Dim sheetName As String
       Dim charIndex As Integer
       For i = 1 To wb.Names.Count
       If wb.Names(i).name = rangeName Then
           sheetName = wb.Names(i).RefersTo
           charIndex = InStr(1, sheetName, "!")
           If charIndex > 0 Then
               sheetName = Left(sheetName, charIndex - 1)
               sheetName = Right(sheetName, Len(sheetName) - 1)
               Exit For
           End If
       End If
       Next
       charIndex = InStr(1, sheetName, "'")
       If charIndex > 0 Then
           sheetName = Right(Left(sheetName, Len(sheetName) - 1), Len(sheetName) - 2)
       End If
       If Len(sheetName) > 0 Then
           Set GetSheetByRangeName = wb.Sheets(sheetName)
       End If
   End Function
   Public Function IsFirstSheetDocMap(ByRef wb As Workbook) As Boolean
       If wb.Worksheets.Count > 0 Then
           If LCase(wb.Worksheets(1).name) = DOCMAP_SHEET_NAME Then
               IsFirstSheetDocMap = True
               Exit Function
           End If
       End If
       IsFirstSheetDocMap = False
   End Function
   
   
     

4. Go back to the left pane and expand VBAProjects and also expand Microsoft Excel Objects and double click the ThisWorkBook node . The ThisWorkBook will host the code responsible for creating the custom menu and hooking the OnAction event to the code responsible for renaming the sheets.
   

   Private Const MENU_CAPTION As String = "&Custom Menu"
   Private Const MENU_ITEM_CAPTION As String = "&Rename Sheets"
   Private Sub AddMenus()
   'ResetMenuBar
    On Error Resume Next
    Me.Application.CommandBars("Worksheet Menu Bar").Controls(MENU_CAPTION).Delete
    Dim mainMenu As CommandBar
    Dim customMenu As CommandBarControl
    Dim customMenuItem As CommandBarButton
    Dim helpMenuIndex As Integer
    Set mainMenu = Me.Application.CommandBars("Worksheet Menu Bar")
    helpMenuIndex = mainMenu.Controls("Help").Index
    Set customMenu = mainMenu.Controls.Add(Type:=msoControlPopup, Before:=helpMenuIndex)
    customMenu.Caption = MENU_CAPTION
    Set customMenuItem = customMenu.Controls.Add(Type:=msoControlButton)
    customMenuItem.Caption = MENU_ITEM_CAPTION
    customMenuItem.OnAction = "RunRenameSheetsCode"
   End Sub
   Private Sub ResetMenuBar()
       Application.CommandBars("Worksheet Menu Bar").Reset
   End Sub
   Private Sub Workbook_Open()
       Call AddMenus
   End Sub
   

5. Save the current workbook as an excel add-in. From File menu select save as and in the save-as dialog in the "Save as type" list find and select Microsoft Office Excel Add-in (*.xla).
   By default excel saves new addins in the add-ins folder designated for the current user which is %USERPROFILE%\Application Data\Microsoft\AddIns. You can also place the add-in in C:\Program Files\Microsoft Office\OFFICE11\XLSTART causes excel to load your add-in when excel is started regardless of the user who started excel.
   
6. Every time you start excel now you'll see your custom menu. If you the first sheet is name "document map" and the user clicks on the menu item which we created to rename sheets, the add-in will attempt renaming sheets based on what's in the document map.