Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

Import and Export for Office in GP 2013

Import and Export for Office in GP 2013

Rate This
  • Comments 1

Patrick Roth - Click for blog homepageA 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.

Page 1 of 1 (1 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 3 and 2 and type the answer here:
  • Post