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
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:
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?
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:
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.
PatrickSenior Escalation Engineer, GP
PS: I'll mention it again:
Posting from Mark Polino at DynamicAccounting.net
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.
That is always an option to resolve the issue, but not everyone would be happy with the extra step.
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.