Are there any events I can tap into with VBScript for an SSRS report when it is exported to Excel?

Question

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?

Answer 1

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....