Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All -
I'm creating an Excel report that has a Page level for CustomerGroup. The reports will have 20 or more worksheets and I'm trying to come up with a way to create a Table of Contents using Excel links. So far, I've created a table with CustomerGroup as the dimension and an expression that looks like:
=CHR(61) // CHR(61) - =
& 'HYPERLINK'
& CHR(40) // CHR(40) - (
& CHR(34) // CHR(34) - "
& CHR(35) // CHR(35) - #
& CHR(34) // CHR(34) - "
& CHR(38) // CHR(38) -
& CHR(34) // CHR(34) - "
& CHR(39) // CHR(39) - '
& CustomerGroup
& CHR(39) // CHR(39) - '
& CHR(33) // CHR(33) - !
& 'A1'
& CHR(34) // CHR(34) - "
& CHR(44) // CHR(44) - ,
& CHR(34) // CHR(34) - "
& CustomerGroup
& CHR(34) // CHR(34) - "
& CHR(41) // CHR(41) - )
Which creates a string like: =HYPERLINK("#"&"'AUTOLIV'!A1","AUTOLIV")
It works great, but when I run the report, Excel refuses to believe that the cell format is General and treats it like text. I finally found some VBA code to fix the Excel bug:
Sub FixFormula()
ActiveSheet.Range("B1:B100").Select
Dim r As Range, s As String
For Each r In Selection
s = r.Text
r.Clear
r.Formula = s
Next r
End Sub
But for the life of me, I can't get it to run when the report distributes. Anybody have any luck getting a macro or VBA code to run on open?
Thanks,
mike
Hi Michael,
Take a look at the example here: Index on page loop excel
Preview as Xlsm or run the Task (task) to create the output file.
HTH - Daniel.
Hi Michael,
Take a look at the example here: Index on page loop excel
Preview as Xlsm or run the Task (task) to create the output file.
HTH - Daniel.
Daniel, you are a genius!
Thanks!