Skip to main content
Waters

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

Article number: 50873

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, then disabling the actions in the form config which occur at form-open time does not materially reduce the amount of time to open the form

ENVIRONMENT

  • NuGenesis 8 LMS
  • NuGenesis 8 ELN

CAUSE

The Excel sections contains 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 will select all cells in the column - up to 1 million cells - and if this is not noticed, then the formatting would be applied to all of the cells.

FIX or WORKAROUND

  1. Open the Excel section and look at all of the sheets within the workbook for cells with unnecessary formatting
    1. If the section is an Excel form, then 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, at the last row and column of the worksheet
  3. Removing the formatting with the "Clear Formatting" action does not work; the formatting will be cleared, but the data size will still be very large
  4. If the worksheet is simple (that is, few configured ranges on that worksheet in Smart Builder, or few formulas/named ranges on the worksheet), then the sheet could be deleted and replaced:
    1. Delete the worksheet(s)
    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 un-modified workbook into the new sheet
    7. Range names will not be copied over to the new sheet.  Check the Name Manager in Excel for any range names on the sheet in the un-modified 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 as per the above instructions, then 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 checkmark 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 it's work.  This may require 15 minutes or more
      5. Click Yes when prompted to save the changes

ADDITIONAL INFORMATION

 

Not able to find a solution? Click here to request help.