Exporting to Excel and renaming sheets in 2005

In Reporting Services 2005 there was no functionality to rename sheets when your report was more than one page. You had to either use custom code to rename the sheets or use a macro.

We created a field in one of the cells in the report and then changed the font colour to white, then we created a macro to copy the value in the cell and rename the sheets. The macro is shown below:

Sub RenameSheets()
' Renames all the sheets based on the contents of the cell A1
' If the cell A1 is empty the sheet will be renamed and there is a limit of 31 characters if this is exceeded then only the first 31 are used
' If the following characters are used then an error will be returned:
' []?*/\:


Dim i As Integer

For i = 1 To Sheets.Count

If (Len(Worksheets(i).Range("A2").Value) < 32) Then
If (Len(Worksheets(i).Range("A2").Value) > 0) Then
Sheets(i).Name = Worksheets(i).Range("A2").Value
End If
Else
Sheets(i).Name = Left(Worksheets(i).Range("A2").Value, 31)
End If
Next
End Sub