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.

The PS/nVision macrodir registry setting is missing or invalid

If you get the error message "the ps/nvision macrodir registry setting is missing or invalid" when starting up nVision check for the following in the user's configuration file.

• Start Config Manager (pscfg.exe in the \bin\client\winx86 folder)
• Go to the Profile tab and click on the Edit button
• On the nVision tab, confirm the following:
• That the path points to the correct location where the Nvsuser.xlsm file is stored (default is the Excel folder under the PeopleTools home directory)
• That the there is no slash at the end of the file path (slashes at the end of any of the file paths in the config file cause warnings at startup)

nVision 3-tier Known Errors Guide PeopleTools 8.52

Here's a summary of all the nVision 3-Tier (app server connection from client install) that I have been able to identify a root cause for so far PeopleTools 8.52. There are a couple of errors that I have not yet been able to nail down, listed at the end. If anyone has been able to "crack the code" for those, please share in the comments.

Summary of Errors and Root Cause

OLE error

• Laptop/computer has no network connection
• Other causes possible but yet to be determined

Excel Interface Exception 1593 / 2160 / OLE in sequence

• Running an nVision report and a Query at the same time

Excel Interface Exception 2160 standalone

• For one reason or another, Excel cannot save the nVision instance

Excel Interface Exception 1828

• Occurs when closing an Excel workbook from a Taskbar preview thumbnail and the following conditions apply: the workbook being closed is not Excel’s active workbook, and the workbook being closed has unsaved changes

Excel Interface Exception 2518

• Another user has the report instance open from the same location you are running the report to

Call was rejected by callee

• While an nVision report is running, the user is active in another Excel workbook. This makes Excel unavailable to nVision when the results are ready and nVision “calls” Excel.

Internal Application Error

• Running query and multiple nVision reports concurrently. NVision must be restarted.

Excel Interface Exception 2061

• Yet to be determined

Excel Interface Exception 4475

• Yet to be determined

 

 

Error Message Screenshots, Root Cause, and Mitigation

Stand-Alone OLE error

Root Cause:

• This error will occur if your computer has lost all connections to the network, for example, your LAN cable is unplugged and wireless card is disabled.

 

Mitigation:

• Check at the back of your computer or laptop that the LAN cable light is lit and blinking
• Check that the wireless light on your computer is blue (not red)
• Check that you have a network connection by going to the Network and Sharing Center in the Control Panel
  

Excel Interface Exception 1593 / 2160 / OLE in sequence

Root Cause:

• Both an nVision report and a Query are running concurrently – the nVision report is initiated before the query has returned its results to Excel.

Mitigation:

• Do not run queries and nVision reports concurrently
• Always wait for your nVision report to finish before running a query, and vice-versa
• Consider running nVision and/or Query online by signing in to FMS which will allow you to run reports and queries concurrently

 

Excel Interface Exception 2160 standalone

 

 

 

Multiple Possible Root Causes – all related to issues with saving the nVision instance:

·         The report has a macro attached to it, but the layout has never been saved as a macro file – Excel will not save macro files with the xlsx extension.

·         The file name or path does not exist

·         The file is being used by another program

·         The workbook you are trying to save has the same name as a currently open workbook

·         The user (or server) does not have security access to the output destination

·         The output destination is out of space

Mitigation:

·         When the nVision developer adds a macro to a layout, they should save the layout as an xlsm file, close the file, and then change the file extension back to .xnv through Windows Explorer

·         Make sure no one else is running the same report to the same location

·         Make sure you (or the server if running online) have access to the output destination

·         Make sure the output destination is not out of space

 

Excel Interface Exception 1828

Root Cause:

• Occurs when closing an Excel workbook from a Taskbar preview thumbnail and the following conditions apply: the workbook being closed is not Excel’s active workbook, and the workbook being closed has unsaved changes

Mitigation:

• Close workbooks using one of the traditional methods:
• File > Close from the workbook
• Clicking the X at the top right of the workbook
• Typing Ctrl + W
• Note: if you receive this error message, click OK and then when prompted to save the workbook, click Cancel. Then make the workbook the active workbook, and close it using any of the traditional methods.

 

Excel Interface Exception 2518

 

Root Cause:

Occurs when the nVision instance cannot be saved.

Possible causes:

• Most commonly occurs when someone else ran the same report to the same location and has the file open
• You do not have rights to save files in the destination folder
• The destination folder has run out of allocated space

Mitigation:

• Remove the output path from the nVision report request. NVision will save the instance to your default location on your C drive: C:\nvision\instance (Do not save the changes to the report request)

 

Call was rejected by callee

Root cause:

• Occurs when user is active in another Excel workbook while an nVision report is being run and when the report finishes the other workbook is in “edit mode”; i.e. user is typing in a cell in the other workbook.
• Note: User will need to end and restart nVision

Mitigation:

• Always allow nVision to run to completion before using Excel again – do not multitask in Excel when an nVision report is running

 

Internal Application Error

Root Cause:

• Running query and multiple nVision reports concurrently
• Note: Database connection is lost when this error is received and nVision must be restarted

Mitigation:

• Always wait for an nVision report or query to complete before
• Don’t run nVision reports and queries at the same time

 

Excel Interface Exception 2061

Root Cause:

• Yet to be determined

 

Excel Interface Exception 4475

Root Cause:

• Yet to be determined