Search

Adding an nVision Instance Hook to Call a Macro that Recalculates all Cells

Adding a Macro to nVision Layouts to Force Recalculation of All Cells when the Report is Run

 
As reported in my post Excel Formulas Not Calculating, some older nVision reports that were originally developed in legacy versions of Excel and now being run in Excel 2010 do not recalculate some formulas.

The quick workaround is to force recalculation of all cells. Excel is efficient with resources when it recalculates a spreadsheet and only recalculates those cells that it recognizes as having changed since they were last recalculated. This issue arises because Excel isn't identifying some cells that need to be recalculated. Typing F9 (recalculate) doesn't work, However, typing Alt+Ctrl+Shift+F9 will force Excel to recalculate all cells, even those it thinks don't need to be recalculated.

An option for layouts that have been identified with this issue is to add a macro that performs the above keystrokes, and then call that macro using an nVision instance hook.

Here are the steps to do that.

Recording a Macro and Adding an nVision Instance Hook


  • Open layout
  • From the Developer ribbon, click Record Macro
     
     



  • Name the macro something simple and descriptive and store in “This Workbook”. Click OK.
     


  • Type Alt+Ctrl+Shift+F9 and immediately click Stop Recording

                     
     

Establish NvsInstanceHook

  • Type Ctrl+F3 and click New to add new name
  • Type Sheet1!NvsInstanceHook in the Name field (be careful not to make any typos)
  • Type =Recalc (or whatever it was you named your macro) in the Refers to field
  • Click OK          
     
     
     
    • The instance hook will appear like this in the names list.
     
     
     
    Close the Name Manager


    Saving the layout as a macro enabled workbook


    Since Excel 2007, Excel files which have macros associated with them must be explicitly identified by the .xlsm file extension. So we now need to identify this file as one that contains a macro, otherwise we will experience an error when we run the report.
    • When saving you will be presented with the following dialog box. Click No.
       
       
       
    • Save the layout as a .xlsm file  
    • Close the .xlsm file
    • Using Windows Explorer confirm you now have two files with the same name, but one is the .xnv file, and the other is the .xlsm file. Delete the original .xnv file from the layout folder and then Change the .xlsm file to .xnv
    • Accept the warning when you change the file extension



     
    Test – the nVision instance should be saved as an xlsm file.
    Labels: ,

    2 comments:

    1. AnonymousJuly 13, 2016 at 1:56 PM

      Having the final file with a .xls extension as a result but based on an XLSM layout (renamed to .xnv) results in an error being issued at the time the user attempts to open the file. What can be done to automate this process of recalculating, as well as getting the file to wind up with a .xls or .xlsx file extension?

      ReplyDelete
      Replies
      1. John CAugust 13, 2026 at 10:52 AM

        Great question, and even though this comment is over a decade old, I think it's still worth answering for anyone who finds this page today.

        Short answer: yes, this has genuinely changed — and for the better. Since PeopleTools 8.54, OpenXML mode became the default engine nVision uses to
        generate reports, and OpenXML-generated reports are true, native .xlsx files (or .xlsm, if your layout uses macros) rather than a .xls file wearing an .xnv
        costume. So for most layouts, you should now get a real .xlsx straight out of the process scheduler, no renaming trick required.

        There's one catch specific to your original question, though: the NvsInstanceHook approach from this post — where a macro fires automatically
        during report generation, right before nVision saves the instance — is NOT supported in OpenXML mode. That hook only works in the older, Windows-based Excel Automation mode. So if you specifically need a macro to run during generation itself (not just when the user later opens the file), you'd still need that classic Excel mode, and the output would still need to stay macro-capable (.xlsm) — which is really the same tradeoff we were dealing with back when this post was written.

        If your underlying goal was really just "stop delivering reports with stale, un-recalculated formulas," that specific pain point is much less likely to bite you today. Excel recalculates on open by default, and if you're on a
        currently supported PeopleTools release, you're almost certainly running a far more current version of Excel than the 2010 release that caused this whole headache in the first place.

        My suggestion: ask whoever administers your PeopleTools environment which mode nVision is running in for this layout — Excel, OpenXML, or a mixed setup that routes only macro-dependent layouts to classic Excel. If it's on OpenXML and you don't strictly need a generation-time macro, you can likely drop the .xnv workaround and just deliver .xlsx directly. If you do need
        that macro, putting it in a .xlsm as a Workbook_Open macro (fires when the recipient opens the file, rather than during generation) is the closer modern equivalent.

        Thanks for the question — sorry it took about nine years to get an answer!

        Delete

    Subscribe to: Post Comments (Atom)