Issue
When a user runs a report in Jet Reports, the status box hangs on 'Prefetching Replicated Data' for an extended period of time.
Resolution:
** Before following the steps below it is VERY IMPORTANT to create a copy of your workbook as there is a high risk of permanently damaging your report if the steps are not followed properly. **
Terminology
Shadow Sheets:
When a report contains +Values in cell A1 of any sheet in the workbook the final step in the 'Refresh' process is to create Shadow Sheets.
Shadow Sheets are where all of the Jet functions in the workbook are stored while a report is in 'Report Mode' so that Viewers do not see them and cannot modify/damage them.
When a report is restored back to 'Design Mode' or 'Refreshed' the Jet functions from these Shadow Sheets are restored back to the workbook, and in the case of a 'Refresh' they are recreated.
While in 'Design Mode' you should not have any Shadow Sheets unless your report contains 'Stored Viewer Data', in which case we would not recommend following the steps below.
Determining if you have orphaned Shadow Sheets:
To determine if you have orphaned Shadow Sheets and remove them follow these steps.
** Make sure that you only perform these steps while in 'Design Mode'. **
-
Open your report and place it in Design Mode.
-
Press ALT+F11 to open Microsoft Visual Basic for Applications.
-
Look at the Project pane on the left hand side of the window.
-
You should see all of the sheets in your workbook listed under the Microsoft Excel Objects section listed as...
Sheet1 (Report)
Sheet2 (Options)
Where the names of the sheets are in parentheses (this is important).
-
If you see other sheets listed which are not actual sheet tabs in your workbook then these are likely orphaned Shadow Sheets.
Sheet10 (Sheet100)
Sheet11 (Sheet101) -
Select the sheet and look at the Visible status in the Properties pane below.
-
If the Visible status is 2- xlSheetVeryHidden then you can change it to -1 - xlSheetVisible which will make it appear in your workbook as a sheet tab.
*Note: '0 - xlSheetHidden' are not Shadow Sheets, these are standard hidden sheets which can be unhidden by a standard Excel unhide. -
Repeat this process for all sheets which are 2 - xlSheetVeryHidden.
-
Close the 'Microsoft Visual Basic for Applications' window.
-
Delete the sheets which are now visible in your workbook via the sheet tabs.
-
Save the workbook and test it.
Comments