Creating Excel reports with Hyperlinks to other Excel worksheets via SSRS 2016?

Question

I need to create excel reports with SSRS 2016. These reports have need to have a `sheet 1` that is an index to the other sheets. I can get the excel function inserted correctly using an expression. The expression is as follows: ="=HYPERLINK(" & chr(34) & "#" & "'" & "Sheet2" & "'" & "!A4" & chr(34) & "," & chr(34) & "TEST" & chr(34) & ")" This creates the correct hyperlink syntax in the excel cell. This syntax is as follows: =HYPERLINK("#'Sheet2'!A4","TEST") The problem is that I have to click the cell once before it will behave as a hyperlink and just show the word TEST. After I click it once it works as desired. However, I need it to render from the start as a hyperlink. Any ideas? I also tried doing it as just text but as soon as SSRS sees the "=" it interprets it as an expression. It is almost impossible to find any advanced excel formatting (via SSRS) on the web. Almost all the info is very rudimentary.

Answer 1

Step 1: Open your SSRS report in Report Builder or SQL Server Data Tools

Step 2: Select the textbox or cell in the report that you want to become a hyperlink (e.g., the cell showing "TEST")

Step 3: Right-click the textbox and select Textbox Properties

Step 4: Go to the Action tab on the left

Step 5: Select Go to bookmark (to link to a worksheet within the same Excel file)

1. In the Bookmark field, type the cell reference you want to link to.
2. For example:

CopyEdit

Sheet2!A4

Note: Make sure the worksheet name is exactly as in Excel (case sensitive and includes apostrophes if spaces, e.g. 'Sheet 2'!A4).

Step 6: Click OK to close the Textbox Properties

Step 7: Export your report to Excel format

1. Click Run or Preview your report.
2. Choose Export > Excel.

Step 8: Open the exported Excel file

1. The cell with the link text ("TEST") should appear as a clickable hyperlink immediately.
2. No extra click needed to activate the link.

Optional: Add Named Bookmarks in your Excel sheets

1. If "Go to bookmark" does not work as expected, you can add named ranges/bookmarks in your Excel file before exporting, then use those named bookmarks in Step 5.

If you still want to embed the Excel formula with =HYPERLINK(...) and make it work immediately, follow this post-processing step:

Post-process with VBA Macro to convert hyperlink formula text into real formulas

Step 1: Open the exported Excel file

Step 2: Press Alt + F11 to open the VBA editor

Step 3: Click Insert > Module

Step 4: Copy and paste this VBA code:

vba

CopyEdit

Sub ConvertTextToFormula()

Dim cell As Range

For Each cell In ActiveSheet.UsedRange

If Left(cell.Value, 11) = "=HYPERLINK(" Then

cell.Formula = cell.Value

End If

Next cell

End Sub

Step 5: Close the VBA editor and return to Excel

Step 6: Press Alt + F8, select ConvertTextToFormula, and click Run

1. This macro converts all the text cells that look like hyperlink formulas into actual Excel formulas.

Now your hyperlinks work immediately as clickable links!