Office Addins Dynamics AX 2012
May 14, 2011 38 Comments
In the newer version AX2012, Their is a great Addin available called Office Addin, with this Addin the data Import/Export is really easy. The office addin also have the out of box consumer for comsuming the AX document services. This is real cool feature. You can use this addin to export data from Table, or if you need to export data on some criteria, then you can build the AOT queries to do the query based export. The Addin can be used to consume the web service operation, means Now you can fill the record in excel and just published, the record will be created/ updated in AX, read operation is also supported with this Addin. see pictures below for look of this tool.
Inorder to install it you need to run the setup, in the Addins you will find the Addin for Office. The installation is really simple, just next next and finished.
Once setup is installed, you have to do some setup in dynamics ax to use the addins.
Under Organization Administration> Setup> Document Management > Document datasources
On this form you need to do the setup, if you want to consume any document service from office addin, or you want to use the Query based export. Simply create a new record, specify the type of datasource (service/ Query), select the service or Query and make it active.
Note: this setup is not required for Table export.
Once the Addin installed, you will see the Dynamics AX tab in you Excel. You will see options like, Add data (for selecting service/ Query) , Add Table (here you can select Tables from AX). you will also find option for publishing the data to and from Dynamics AX. See screen below.
After selecting the Table, Query, or Service click Ok. This will open the Fields Choser in Excel where you can select the fields or drag it to Excel sheet.
Close the fields choser, click the Refresh data to see the data in excel,
In case or Web service, you can create records in Excel or modify the record in Excel, click the publish button to insert/ update records in AX.
This is Amir Nazim from Karachi Pakistan, having very good experience on Microsoft Dynamics AX and related technologies, currently working as Microsoft Dynamics Ax Sr. Development Engineer and Solution Architect in MBS (Implementation) team. 
Amir's Space on Dynamics AX and Dynamics 365 for Operations 
Pingback: Consume document service from Dynamics ax 2012 office Addin « Amir's Microsoft Dynamics AX space
Pingback: Amir's Microsoft Dynamics Ax Space
Hi Amir,
When trying to retrieve some vendgroup information (via the refresh function) in Excel, I get following message : “Invalid bookmark. Parameter name : ValueBasedPagingBase.Bookmark”. Any idea?
FYI, I’ve createad an AIF inbound port with the VendVendGroup create. service and I’ve done the document data sources setup.
Kind regards,
Frank
How can I send an image / picture from AX to Word? I set up the query that has the image in AX but when it brings it into Word its all gibberish. How can i get the actual image?
Any input would be appreciated.
Regards,
Christine
Hi Christine,
I think you need to look at below link.
You can get help from system class for Task recorder, SysTaskRecorderDocStandardWord and its method insertScreenshot() , you can also copy the image to clip board then from there you can write it to word document with the following set of steps
Image.clipboardCopy();
COMrange.paste();
if you looking for how to write word document, generally then
COM wordApplication;
COM wordDocuments;
COM wordDocument;
COM wordRange;
;
wordApplication = new COM(“word.application”);
wordApplication.visible(TRUE);
wordDocuments = wordApplication.Documents();
wordDocument = wordDocuments.add();
wordDocument.saveas(“c:\\YourDocument.doc”);
wordDocument.activate();
wordRange = wordDocument.range(0,0);
wordRange.insertafter(“mouli);
wordRange = wordDocument.range(6,19);
wordRange = wordDocument.range(11,26);
wordRange.italic(true);
wordDocument.save();
wordDocument.close();
wordApplication.quit();
Thanks Amir, but I was talking about from the new capabilities in DAX 2012, how we can send create Word documents from Document Handling and as shown above how you have shown the add-ins, if there was a way to send the image as part of the query.
thanks,
Christine
Hello Amir,
I have a strange behaviour after installing, without any kind o pronblem, the Office Add-in.
When launching Excel, the add-in is not loaded; when I try to have a look at the setup, the “Check box” of Microsoft Excel Office add-in fo Dynamics Ax is not cheched. After tried to check it, the following message appears:
“The type initializer for ‘Microsoft.Dynamics.AX.Framework.OfficeAddin.DisplayFormatProviderManager’ threw an exception.
************** Exception Text **************
System.TypeInitializationException: The type initializer for ‘Microsoft.Dynamics.AX.Framework.OfficeAddin.DisplayFormatProviderManager’ threw an exception. —> System.TypeLoadException: Could not load type ‘Microsoft.Dynamics.AX.Framework.OfficeAddin.Excel.DisplayFormatProvider’ from assembly ‘Microsoft.Dynamics.AX.Framework.OfficeAddin.DynamicsAX, …….
Do you have any idea for this error message?
Thanks and kind regards
Thanks Amir, very helpful. I’m having an issue with data sources I create as services showing up in Excel though. Query works fine, but I want to be able to publish back to AX.
Thanks.
Eric,
you can publish the data through document service, see the link below how u can configure the document service if you have already created it
Thanks
Amir
i recently found that u can published the data through table as a datasource i have not tried it yet, but i am sure it will be same as service, so definately u just need to setup the table as a datasource in AX instead of service then u will see the datasource in Excel addin. there u can use that datasource to created table in Excel, fill the data in it and use the published button to push the data to AX, make sure u have filled the key, mandatory field
Hi Amir
I am using this for the first time and when I am in excel and in the AX2012 tab select Add Data, the list is empty. How do I add the datasources please?
Cheers
Carl
HI Carl,
Please check that, Under Organization Administration> Setup> Document Management > Document datasources, On this form you need to do the setup, if you want to consume any document service from office addin, or you want to use the Query based export. u need to select the query or document service on that form, inorder to show this on excel.
Thanks
Amir
Hi there
I have the problem that I can read the data from Dynamics Ax but “publish all” is not working. I am obviously using the image currently downloadable from Microsoft.
Am I missing some security setting please?
Thank you
Hi George
Published button will only be enabled or workable if you have created the web service, and u have selected service as a datasource in excel. because Excel addin use the service create, update operation to move data into the AX.
Thanks
Amir
Hi Amir,
Publish button is also disabled for me. Can u tell me how can I expose service and which service to expose. It’s very good if u send me step by step info.
Abhishek
Hi There
I have installed the add-ins without any errors and I have set up the data sources.
The Excel tabs are not visible and the Com add in for ax is not active.
When I try to actovate the com add-ins nothing happens
Any suggestions on how to solve the error?
Hi ,
ServicePack 1 of Office 2010 is recomended, please verify that u have that installed, if not please remove the excel addin,, upgrade office 2010 and reinstalled it again.
Thanks
Amir
How can I get all the fields in the table in one shot into XL?
i dont think there is a way to move all the fields to XL, although u can move all the mandatory fields to XL just by by double clicking on the datasource node in field chooser area.
Hello,
I am working with the Excel add in and the Field Lookup and Retrieve Status buttons are inactive. How can I activate them.
Pingback: Customer or Vendor import In Dynamics ax 2012 using Office add-ins « Amir's Microsoft Dynamics AX space
Pingback: Amir's Microsoft Dynamics Ax Space
Hi,
i’m iwan, would you mind to explaint how to create word or excel template and generated in ax 2012. thanks
best regards,
iwan
Hello Amir. Great Post.!
I am trying to get this functionality to work and I am getting an error which I cannot get past. Whenever I click the ‘Connection’ or ‘Add Data’ button I get the following error:
“This operation is not supported for a relative URI.”
Have you ever encountered this error before? Is there anything you can suggest that would help me get past this?
Please let me know
Thanks, i have not encounter this error before, but can if you upgrade your Build to CU2 Dax 12 then most of office addins problem would be solved. Also you should have SP1 2010 MS Office then on top of it you can installed Office addin.
Hi
I am also facing the same issue. I have CU2 and office 2010 SP1 or Higher.
Did you guys got any solutions to this?
Thanks
Hello Amir.
In office i can see in ADD-data Tables end query but is not visible the active services.
Can you help me?
did you have selected your active service as datasource here
Under Organization Administration> Setup> Document Management > Document datasources
I’have already done it, but i can not see in office the service
Oh strange,i never come accross such an issue, but only document service are supported, not custome service. i think you also have activated the service from have your inbpound port setup and webservice component installed also.
Pingback: Customer or Vendor import In Dynamics ax 2012 using Office add-ins
Have a question for you and would love to see if you could point us in the right direction. We recently installed AX 2012 and are struggling with General Ledger Uploads via Excel. We have over 60+ companies and over 16 accountants who need to upload data via excel. According to what we have experienced, we need to create a unique template for each company and for each employee? That’s insane. Is there no way to just have a connection to AX 2012 and allow the user to input a different company in the template when they are going to upload? And what makes matters worse, if we save the template, it remembers the company that was used prior even if we have selected a new company, thus it attempts to load entries into the wrong company.
Any and all help would be greatly appreciated.
Thank you kindly,
dsy
Hi David,
Sorry for replying you late. i think currently in ax 2012 you would not find any tool out of box that helps you in creating or posting the GJs, excel addin is not that strong. in that scenario you need to use the AIF web services. we can develop a transformation that can transform data from excel to AX supported format. then that data can be used in AX for GJs.
A utility can be build with Web service AIF, Transformation, file adapter. after that your users would be able to use excel to create journals
Thanks
Amir
Hi Amir,
I am trying to use the document data source for ProjInvoiceNormal (Query). There were some other tables that I needed to link in through relations, so I modified the Query in the AOT. But now I need help trying to update the document data source. It has not updated the datasource in the Word Doc where I’m trying to use Dynamics AX add-in. How do you update the document data source after modifying the query?
Thanks in advance!
Bryan
Hi Bryan,
Sorry for replying you late, i suggest you to make a copy of the query and add whatever tables you want to add to it. try removing the usage data to it may be it is cached.
Thanks
Amir
Hi
I would like to add parameters but I dont Know,
can you helep me please
Hi,
When using a query, I would like to add extra (calculated) columns in the excel, but that doesn’t seem to work.
eg. I want to sum 2 fields from the AX query together. But whenever I refresh the data, those columns are thrown away again. Has anyone seen the same behaviour ?
Tx,
Tom
Hi,
It’s working correctly, Suppose if i populate using query reference and service at the it’s populating table in ax to excel but it’s not populating excel to table after updation.
Give me the solution.
Thanks in advance.
Purushoth