Import and Export for Office in GP 2013

A customer ran across an interesting issue with Excel Based Budgeting after they upgraded to GP 2013.

They had been using Excel Based Budgeting in GP 2010 successfully - mostly to import/update existing budgets based on their existing spreadsheets. 

What was successful in GP 2010, wasn't working in GP 2013 - specifically cells that were based on formulas were not coming in correctly as they did in GP 2010.

After a bit of experimentation, we found that a formula such as

=D1+D2+D3

would come into GP as 1.00 (currency).

The reason is that the code interpreted the formula as the literal amount.  The "1" comes from the first cell calculation in "D1".  Changing the formula to =D3+D2+D1 resulted in a value of 3.00 so that seemed to be the pattern.

So what changed between versions to make this fail in this manner?

 

What Changed and Why?

It was the change from Dynamics using COM automation of Excel to using the OpenXML SDK for Microsoft Office.

In previous versions of Dynamics - import & export to Office applications (Excel in this example) is accomplished by using native COM calls to Excel using Dexterity.

This worked pretty well across multiple versions of Dynamics and Excel so why the change in GP 2013?

The biggest reason - most likely the only reason - is for the Microsoft Dynamics GP Web Client.

From a Web Client point of view, COM isn't supported (but might still work) by Microsoft as how the web client runs isn't compatible with a UI and would also affect objects that are instantiated by the runtime.

As it happens, we just had a case today where an ISV was automating Microsoft Word.  The object was to open an existing Word and then replace "tags" in the document with substituted values.

The ISV was using code similar to:

objSel.WholeStory

which will select the contents of the Word document.

Under the desktop client, this worked fine (as with previous versions of Dynamics GP).

But under the web client, this throws an exception.  The document opens fine and other method calls worked, this method did not. 

So the question is - is the customer stuck?

 

Solution

As it turns out, it looks like we were able to find a solution for them.

As I reviewed the code, I noticed that there was an "if" check that didn't call the OpenXML routines.  Intrigued, I researched further and I found what appears to be an undocumented Dex.ini switch that affects this behavior.

Warning: This is an undocumented ini switch. Its use is not tested or supported, use at your own risk, your mileage may vary. Using this switch on the web client may also violate your licensing agreement for MS Word/Excel.

We added the Dex.ini switch:

UseCOMForExcelExport=TRUE

This tells GP to use the old COM routines for both Import & Export instead of using the OpenXML SDK routines.  I didn't fully research the entire application, but from what I could tell is that this affected the common Import & Export routines meaning that it would affect SmartList exporting as well as Excel Based Budgeting.

After we made this change (and restarted GP), importing the Excel budget worked as expected.

As a test, we exported a GL Transaction SmartList.  It did export, but we noticed that it was slower than usual and the currency information was now formatted with $ symbols.

To recap:

1. GP 2013 now imports/exports to Excel using the OpenXML SDK
    
2. The OpenXML SDK method is much faster than using COM
    
3. Exporting using OpenXML SDK loses currency formatting as the field is exported as a decimal and not currency.
    
4. Using the OpenXML SDK means that Excel (or Word) no longer has to be installed on the machine in order to read/write to the files.

 

Best Regards,

Patrick
Senior Escalation Engineer, GP

 

PS: I'll mention it again:

Warning: This is an undocumented ini switch. Its use is not tested or supported, use at your own risk, your mileage may vary. Using this switch on the web client may also violate your licensing agreement for MS Word/Excel.

Comments

• Anonymous
  April 30, 2014
  Posting from Mark Polino at DynamicAccounting.net mpolino.com/.../import-and-export-for-office-in-gp-2013-developing-for-dynamics-gp-site-home-msdn-blogs

• Anonymous
  January 02, 2016
  If you have an Excel budget tool you have been using that has formulas in the cells and want a simple way to solve this problem, just copy the sheet with the budget information,  highlight all the cells, cut, and "special paste" values only.  Gets rid of all formulas and just puts the numerical value in it's place.

• Anonymous
  January 28, 2016
  Hi Rob That is always an option to resolve the issue, but not everyone would be happy with the extra step. David

• Anonymous
  August 05, 2016
  Hi, Do you know why I am getting this error message when doing an upload?It says it had to repair the file and removed records.-error032320_01.xmlErrors were detected in file 'R:\BI360\Practice Files - Michelle\MBR\Import - 2015 Customer number to REgion.xlsx'-Removed Records: Cell information from /xl/worksheets/sheet.xml part