Skip to main content

Notifications

Importing Excel budgets with Calculated fields

Have 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.

Comments

*This post is locked for comments

  • Community Member Profile Picture Community Member Microsoft Employee
    Posted at

    Shawn King explains why  Importing Excel budgets with Calculated fields   has changed.

    The post Importing

  • Community Member Profile Picture Community Member Microsoft Employee
    Posted at

    Shawn King explains why  Importing Excel budgets with Calculated fields   has changed.

    The post Importing

  • Community Member Profile Picture Community Member Microsoft Employee
    Posted at

    Pingback from  DynamicAccounting.net | Importing Excel budgets with Calculated fields – Microsoft Dynamics GP CommunityImporting Excel budgets with Calculated fields - Microsoft Dynamics GP Community - DynamicAccounting.net

  • Community Member Profile Picture Community Member Microsoft Employee
    Posted at

    Pingback from  Options to be aware of with Excel Budget Imports into Dynamics GP 2013 - Etelligent Solutions-