Slow performance when opening an Excel form in a NuGenesis LMS document - WKB50873

SYMPTOMS

• When LMS attempts to open an Excel section, it takes several minutes for Excel to open
• The section may or may not be a Smart Builder form
• If the section is a form, disabling the actions in the form configuration that occur at the time of form-opening does not significantly reduce the amount of time it takes to open the form

ENVIRONMENT

• NuGenesis 9 LMS
• NuGenesis 8 LMS
• NuGenesis 8 ELN

CAUSE

The Excel sections contain formatting on a lot of unnecessary cells. These cell settings increase the size of the section to several megabytes and slow down Excel when it reads the data. This can occur if the form designer made a mistake when applying formatting to a range of cells. Pressing Shift-Control-down selects all cells in the column—up to 1 million cells. If this is not noticed, the formatting would be applied to all of the cells.

FIX or WORKAROUND

1. Open the Excel section and examine all of the sheets within the workbook for cells with unnecessary formatting.
   • If the section is an Excel form, switch the form to Design mode and make visible all of the worksheets.
2. Look for formatted cells at the "edges" of the worksheets—that is, in the last row and column of the worksheet.
3. Removing the formatting with the "Clear Formatting" action does not work; the formatting will clear, but the data size will still be very large.
4. If the worksheet is simple (that is, few configured ranges on the worksheet in Smart Builder, or few formulas/named ranges on the worksheet), the sheet could be deleted and replaced:
   1. Delete the worksheet or worksheets.
   2. Save the workbook to a new file on disk.
   3. Open a new Excel window from the Start menu or Start screen.
   4. Open the original section or Excel file in this window.
   5. Create a new worksheet in the workbook to replace the deleted sheet.  Give it the same name as the original sheet.
   6. Copy only the necessary cells from the unmodified workbook into the new sheet.
   7. Range names do not copy over to the new sheet.  Check the Name Manager in Excel for any range names on the sheet in the unmodified workbook and recreate those ranges in the new sheet.
   8. Save the workbook with the new worksheet to a file on disk.
5. If the sheet is complex and recreating it is not possible following the instructions above, use the Inquire add-in for Excel to clean the workbook:
   1. Activate the add-in:
      1. Click the File menu > Options.
      2. Click Add-ins.
      3. Look for the "Inquire" add-in in the list.
      4. If it is in the inactive add-ins list:
         1. Select "Manage: COM Add-ins" and click the "Go" button.
         2. Put a check mark in the box for the Inquire add-in and click OK.  A new tab, "Inquire", should appear in the ribbon.
   2. Use the add-in to clean the workbook:
      1. Select the worksheet with the excess formatting.
      2. Click the Inquire tab and click "Clean Excess Cell Formatting".
      3. Select "Apply to: Active Sheet" and click OK.
      4. Wait for the add-in to finish its work. This may require 15 minutes or more.
      5. Click Yes when prompted to save the changes.

ADDITIONAL INFORMATION

Microsoft Excel takes a long time to open on PCs that do not have Internet access - wkb5124