I have tried everything I could think of, and other people's suggestions, as seen here to specify the sheet names when an SSRS report is exported to Excel by the report runner. So far nothing has worked.
Is there some event that I can tap into and write code (VBScript) for? Data values can be assigned or tweaked using VBScript, but is there a way, in SSRS, to write code for some event such as "OnGenerate" or such?
I have never used SSRS, so no idea how it exports. However, you could attempt to set the application new_Workbook event to trap any new workbook that is opened and check it for some clue/feature that it is a product of the SSRS report tool.
Eg. You could check the workbook name(if there is a clear indication that it's a product of that tool.If you save upon export as something like "mySSRS_Report20160628.xlsx", then you could check for the "mySSRS_" string.
In order to do this you would have to have the 'pagename' appearing consistently in the same cell of the generated sheets, like $A$2 in my example.
You will need an add-in , so open a brand new workbook and make a new class module called cAppEvents and put this in it:
Option Explicit
Private WithEvents app As Application
Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
Dim i As Long
If InStr(1, Wb.Name, "mySSRS_", vbTextCompare) Then
For i = 1 To Wb.Worksheets.Count
Wb.Worksheets(i).Name = Wb.Worksheets(i).Range("A2").Value
Next i
Wb.Save
End If
End Sub
Private Sub Class_Initialize()
Set app = Application
End Sub
In the ThisWorkbook module of that workbook put this code in:
Private newApp As cAppEvents
Private Sub Workbook_Open()
Set newApp = New cAppEvents
End Sub
Save it off as an Excel add-in. Go to File-->Options-->Addins to manage your add-ins, and check the box with the filename
Test with opening up workbooks with the name having the string "mySSRS_" in them, and with some relevant data in the cell $A$2. You get the idea....
