Importing Excel budgets with Calculated fields
16
Like
Share
ReportHave you tried to import an Excel budget you’ve been using for YEARS into GP 2013 lately? If you’ve been using GP for a long time and have imported a budget for the last few years, this budget probably has a plethora of calculated fields and formatting.
You’ll notice that now these sheets will not import into GP. This is because of a functionality change in GP using Open XML (OOXML) to communicate with Office applications instead of using Inter-Object Communication (COM) to do so.
A short story of how they work is that COM will read the file as if you were opening Word, Excel or any Office application, whereas OOXML will read the file as if you were reading some sort of .csv file or text file, by opening up a stream in the application. COM opens the file in the background and reads the data from whatever Office application is running. The performance cost comes from having to open Word or Excel in the background. OOXML saves this performance by just having to read the XML data from the file.
For instance if you have the below in an Excel sheet:
| Office Supplies Expense | $ 200.00 |
| Warehouse Supplies Expense | $ 100.00 |
| Total Expenses | $ 300.00 |
COM will see the 300.00 value because the document is open in the background and the formulas calculate. OOXML will not see the value because the formulas don’t calculate and it’s stored as XML data. OOXML will see the following:
| Office Supplies Expense | $ 200.00 |
| Warehouse Supplies Expense | $ 100.00 |
| Total Expenses | =SUM(B1:B2) |
How can I import my old budget in GP?
The reason for the functionality change is for performance. Plain and simple, OOXML will read the data faster than COM will. You’ll see this performance increase when you export a SmartList, or when you import a very large budget.
Good news! There is a Dex.ini value you can add to the file that indicates whether to use COM or OOXML when communicating with Office applications. Just add the following value to your Dex.ini file and you can use whichever mode you want; maybe you want to keep the performance for SmartLists and only use the COM model to import your budget.
UseCOMForExcelExport=TRUE
TRUE indicates that COM will be used to communicate with Office, FALSE indicates that OOXML will be used.
Reference Material for OOXML
http://msdn.microsoft.com/en-us/library/office/bb448854(v=office.15).aspx
Reference Material for COM
http://msdn.microsoft.com/en-us/library/windows/desktop/ms693719(v=vs.85).aspx
Good luck importing!
Shawn K.
*This post is locked for comments