Dynamics AX
  RSS Feed  LinkedIn  Twitter
Want to turn you're data into a true asset? Ready to break free from the report factory?
Ready to gain true insights that are action focused for truly data informed decisions?
Want to do all of this across mutliple companies, instances of Dynamics and your other investments?
Hillstar Business Intelligence is the answer then! (www.HillstarBI.com)

Hillstar Business Intelligence for Microsoft Dynamics AX and NAV on Mobile, Desktop, Tablet

Let us prove to you how we can take the complexity out of the schema and truly enable users to answer the needed questions to run your business! Visit Hillstar Business Solutions at: www.HillstarBI.com

Tuesday, June 07, 2011

Microsoft Dynamics AX 2012 Excel Add-in

With the release of Microsoft Dynamics AX 2012, the lines between the Rich Client, the Portal as well as Microsoft Office are going to be blurred. This is out-of-the-box ability, that replaces a lot of older ways for doing things.

One such topic, is the old Microsoft Excel Export / Import Wizard for Microsoft Dynamics AX. You can see this, in the Microsoft Dynamics AX 2009 menu system, here.:

With the release of Microsoft Dynamics AX 2012, this no longer exists! For some, I'm sure this will be a little shock, however this is a good thing.

The older Excel Import, honestly was very slow and if you had any records of any size, then you just really would rather create X++ scripts to import data.

Now, with Microsoft Dynamics AX 2012, and the new Excel Add-in for Microsoft Dynamics AX, scripts are a thing of the past. Let me say, this new process is very fast, and is designed so that a developer or architect is really not needed to import a lot of data, into AX.

To help show this process, I have created the following walk through that shoudl give an idea of what it takes to work with the new Dynamics AX Add-in for Excel, and get data into Microsoft Dynamics AX 2012 using this new, and great tool!

First, you have to launch Microsoft Office Excel 2010, and of course have the Excel Add-in installed. I'm going to assume this is already in place, and lets say, for the sake of keeping this simple, that I need to add some Delivery Mode data.

This data, lives in the DlvMode table within the Microsoft Dynamics AX AOT. So to get started, lets click on the Connection button, from the ribbon, and make sure we are in the company account and instance we care about.

(Server specific information, blanked out on purpose.)

We see, that we have the ability to override here, and point to what AOS instance we want to connect too, as well as within that what company entity.

Now moving forward from here, lets go and add the DlvMode table, by clicking on the add data, then add table.

Once we select to Add tables, then we can select the DlvMode table, and actually use the Filter field at the bottom, so we only see that table to select. Also notice the fact, that the process created a new worksheet, in this workbook, just for our DlvMode AX table.

Here we can add and remove fields, using the Field Chooser, on the ribbon. Here I've added a few fields, just to give you an idea.

Now moving forward, I will only have the key field Mode of Delivery as it is required, and then Description field.

I then took and added new rows, which gave me three records within the table, of the Excel worksheet. From here, I added my three new DlvModes.

At this point, we now have the data we need, and want to get this into Microsoft Dynamics AX 2012 instance. With this, we simply take and Publish Data, then Publish All. In doing this, we could have selected only one record to publish, but for now lets just publish all of them.

With doing this, we see the following worksheet that was generated, that shows the status of the process.

Having this, we can see that everything went great, and was able to upload the data. Also, we now see, on the DlvMode worksheet, that our new data, and the existing data that was already in the DlvMode table, now appears.

Going into Microsoft Dynamics AX 2012, we can still hit Ctrl+D, and it will open the new Developer workspace. Here we can expand the AOT, and go to our DlvMode table. Right click, and table browse, and see that our data we just created, exists within Microsoft Dynamics AX 2012.

So with the release of Microsoft Dynamics AX 2012, a lot fo great new things come as part of it. More native Office integration, with very little effort, and very little technical knowledge needed.

That's all for now, but this is very exciting stuff, and we are just getting starte within the great new release.

Till next time!

Update: I wanted to make sure an update this post, so that you would have the latest information about the Excel Add-in, in case you hit this post, from a search engine. Make sure and check out the revisited post for the Excel Add-in located here: AX 2012 - Excel Add-in Revisited

Visit Hillstar Business Intelligence (www.HillstarBI.com) in order to truly unlock your data trapped in your Microsoft Dynamics investment. With our value driven business intelligence strategy Hillstar help you transform into a data informed company.

"Visit the Dynamics AX Community Page today!"

Labels: , , , , , ,


Anonymous Anonymous said...

How is Excel Addin licensed? Is it tied to Rich Client License or Lightweight User(EP user) license?

5:23 AM  
Blogger brandon said...

This will actually use the Lightweight User License, from what I understand.

All services will use the lightweight user license, and that is what the Excel Add-in is based on, therefore it uses that license model.

Hope this helps!


6:47 AM  
Anonymous Anonymous said...

Hi Brandon,

Thanks for the info. It will help us make an important decision about our AX implementation.

Thanks again,

12:51 AM  
Anonymous Patrick said...

We have an installation with every tier on its own machine. When Excel is called on the client-machine and we click on "Connection" we get a popup with the error message "Object reference not set to an instance of an object". When we do the same on the application server machine it works. The office add ins are installed on both machines. Have you had or heard of this problem?

6:58 AM  
Anonymous Anonymous said...

I am working with this now. Only thing is when I click Publish Data, I find that Dynamics Ax is not updating with changes.

There must be a configuration that I should change? Can you please advise?


12:53 AM  
Blogger GEB said...

All working in the latest VPC Image as above, with Dynamics Ax tab appearing in Microsoft Excel. Under "Add Data" I see I have the Budget transactions are update-able and updating - from Excel to Ax when I "publish".

However under "Add Tables", I find I can display data but I cannot "publish" back to Dynamics Ax. Is there a configuration per table I need to investigate?

3:57 PM  
Anonymous Anonymous said...

Hi Brandon. Great write-up. Everything went well for me except when I push the Publish Data button, my only option to select is "Publish Options". "Publish All" and "Publish Selected" are disabled. Is there a security issue possibly?? Thanks

4:41 PM  
Anonymous Mat Grisham said...


Excellent job as always. I did get hung up at one point and I thought that I should pass along something that helped me out.

You have to turn off the Field Chooser before you can refresh the fields or pulish the data up to AX. Seems pretty obvious once you figure it out but it just wasted an hour of my time.



8:40 AM  
Anonymous Xander said...

Nice article, but have you tried importing vendors using the Excel add-in?
I keep getting the error about surrogate key expansion :(
Any suggestions?

7:51 AM  
Blogger Unknown said...

Hi there Brandon

I added a table (Countries), selected the fields to be displayed, refreshed and got all the records. All good up to here..

Then, I add a new row, save and press "Publish all" to get the new row uploaded but that fails. The following error message shows up:

"An error occurred. The data in the workbook and the behavior of any add-ins may not be correct. Consider contacting your system administrator about this problem.

Class 1004745 is not a document class".

Any idea what this error means?
Did I miss any configuration?

Thanking you in advance,

4:38 AM  

Post a Comment

<< Home

Copyright 2005-2011, J. Brandon George - All rights Reserved