Why isn't my file working?
Most of our users do the majority of their work in Excel (as do we!), and with that comes a multitude of limitations. Files break or become too big, connections get old, updates aren't run, bugs exist -- and all of these occur regularly with Excel and Microsoft in general. Chances are it'll happen to you at some point, so what do you do when it does?
What is a stale file?
A stale file is essentially an Excel based report, schedule, or package that carries inherent risk based on any or all of the following conditions:
- Contains many thousands of rows of data.
- References several external data connections.
- Queries many formulas, tabs, connections, or files.
- Uses hundreds of OLAP formulas.
- Holds many tabs or worksheets (e.g. 15 or more), especially if they call each other formulaically.
- Has existed for a relatively long time (e.g. over 6 months).
- Has been modified many times, such as formula changes, OLAP formulas restructures, new tabs and data connections, new queries to external sources or network connections, saved and shared via the cloud (e.g. Sharepoint, Teams), etc.
- Hasn't been used or refreshed in some time (e.g. 1 month or longer).
- Using old EBM code. All files should be run through the Cube Updater if old.
- File uses Excel slicers (files containing slicers are not supported by EBM Support).
- Duplicated EBM connections to the same data sets (e.g. Financial cube, Profitability cube, JE cube, etc.)
- Hidden or visible tabs that have been locked by another user still connected to the file.
- Hidden columns or rows. Avoid these if possible and simply change the dataset you're filtered too instead.
- Hundreds of blank rows or columns in tabs. Delete these as they only end up corrupting the file.
- File is excessively large (e.g. 50mb or greater).
How do I know if my file is stale?
Most commonly, your file will begin to show signs of corruption via Microsoft error messages. Essentially, the file will stop functioning and limit your ability to refresh your connection to Catalyst, thereby losing its inherent value -- your file's data becomes stale because it cannot be updated to the current data in Catalyst.
Here are a few examples:
How do I fix my file?
Here are some of the steps you can take to get your file back into working condition. Please try these in order before reaching out to EBM Support:
- The first step is to always quit Excel and then restart your computer.
- Run the file through the Cube Updater in your Catalyst website.
- Refresh all cubes in the EBM Office Bridge ribbon.
- Check the connections under the Data tab in Excel. You should only need one connection for each data type (e.g. Financial, Profitability, Journal Entry, AR, AP, Inventory). If you have duplicates, you'll want to pare these down to eliminate the potential for redundancy.
- Restore an older version of the file from Dropbox or wherever it's been saved.
- Limit sharing this file via the cloud, such as in Teams or SharePoint. Use a file transfer service to share a hard copy of the file or keep it saved locally and pull it down to your desktop if it's shared in a cloud based folder.
- Pull down a fresh cube of the same data type and copy your relevant tab from the stale file into the fresh cube file as a new tab. Choose overwrite, rather than copy when pasting.
- Keep the file as is, but remove all other tabs that aren't relevant to the work you need done. Refresh just the one tab. You'll want to make a backup of the original file and save it to your desktop before doing this.
- Rebuild the entire report/schedule in a fresh cube file (method 1)
- Pull down a blank cube file from Catalyst.
- Copy all of the tabs from the problem file and paste into the new file.
- Be sure to select/choose "Overwrite" and don't rename it when prompted.
- The file should now be usable.
- Rebuild the entire report/schedule in a fresh cube file (method 2)
- Convert the corrupt file to a zip.
- Pull out all the external links.
- Convert back to .xlsm file format.
- Re-open the file.
- Pull down a fresh cube file of the same data type.
- Move all the tabs in the previously corrupt file to the new cube file.
- Overwrite all connections as the tabs migrated.