2 Replies Latest reply: May 16, 2016 4:45 PM by Michael Reagan RSS

    NPrinting Excel/On Open script

    Michael Reagan

      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