Skip to main content
Announcements
Defect acknowledgement with Nprinting Engine May 2022 SR2, please READ HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
mikecrengland
Creator III
Creator III

NPrinting Excel/On Open script

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

1 Solution

Accepted Solutions
Daniel_Jenkins
Specialist III
Specialist III

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.

View solution in original post

2 Replies
Daniel_Jenkins
Specialist III
Specialist III

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.

mikecrengland
Creator III
Creator III
Author

Daniel, you are a genius!

Thanks!