Help!! I closed the Excel Addin

A colleague of mine pinged me today and wondered if there is a way to reopen the Dynamics Excel Addin if you accidentally closed it, other than going back to Dynamics and reopen the file.

Since she was nice enough to ask the question and i did not know the answer I thought I might as well write it down so I do not forget 🙂

1. In Excel, go to the insert tab. Then click My Addins

2. Click on Microsoft Dynamics (if it is not there, click See all… instead and you will find it in the list)

3. The Addin will open up again and reload the condent of the excel file from Dynamics 365 FO

Good Luck

Microsoft 365 add-ins blocked

Today one of ´my colleagues had an issue on her customer laptop. The issue was that she was not able to use the “Open in Excel” funcitonality in Dynamics 365 for Operations and Supply Chain. Apparently someone had blocked the Office addin that was required to make it work.

There are 2 options to handle this… the easiest (and less secure) option is to allow the user to run and install Office Addins. The setting resides in the Office Admin Center and requires an Admin to change it. If some one has disabled it, go the Office Admin Portal – Setting – Org Setting – User owned apps and services and enable “Let users access the Office Store”

The second option is to provision the specific apps to the users that need them. This is also done by an admin in the Office Admin Portal.

In the Office Admin Portal, go to Settings, Integrated Apps and click Add-ins

Click Deploy Add-in

Click Next

Click Choose from the Store

Seach for Dynamics and click Add next to the Microsoft Dynamics Office Add-in

Click Continue

Now you will get the option to set which users will get the add.in deployed. You have the options of Everyone, an Azure AD Group (preferred) and just you. The last option is used for testing and you will later in the wizard get the option to change.

Note: If you are logged in ad Global Admin, that is the user that will get the add-in. You should preferably not use your “regular user” to manage your organizations infrastructure. If you are curious, google Tiering…

Verify that your deployment was successful

You are now done. If everything works, now you get another option to assign which users should get the app.

You can click on the add-in and adjust the deployment scope.

That is it… Good Luck

Links
Prevent add-in downloads by turning off the Office Store across all clients (Except Outlook)
Deploy add-ins in the Microsoft 365 admin center

Finding the PC name of a POS register

In my previous post I showed you how to get the installed version of the POS software on each register. In some cases you might need to manually update this if the update has not worked correctly you will need to connect to the actual PC (or even go on site).

Unfortunately the information in D365FO does not contain the computer name. To get the computer name we need to look in LCS Telemetry. The only value I found to map between the export from D365FO and LCS is Physical Device ID. In order to be able to match the two Excel spread sheets we will need to add the field Physical Device ID to the Export from D365FO.

To export telemetry from LifeCycleServices, Log into LCS, select the environment you are looking at and click in Environment Monitoring.

The maximum number of loglines is 5000 and in order to see what we want to see we will need to limit the results a bit. First of all set Query Name to Retail Channel events. Select a time interval (10 days or so) and set log source to Retail Modern POS. To further limit the results the query return I add 1018 on the search terms. Export the information by clicking Export Grid.

Copy the entire sheet and paste it into a second tab in the D365FO Excel. Name the two tabs D365FO and Telemetry. In the Telemetry tab go to Data and click Remove Duplicates. Click Unselect All and then choose the Role Instance column and click OK.

In the D365FO tab, choose the first cell in the first empty column and paste the following in the formula field

=XLOOKUP(LEFT([@[Physical device ID]];25);Telemetry!Q:Q;Telemetry!G:G;;-1)

Note: You might need to adjust the Column name at the start of the formula in order to fit your export. The columns pointing to the Telemetry tab should be OK if you keep it standard. Fill all the cells in the columns with the same formula. This should add the PC Name to the Excel.

That is it for today 🙂

Office Add-in for Visual Studio crashing when using Open in Office for a DevOps Query

Hi…

Long Time no see… good to be back

I am making heavy use of DevOps queries in my daily tasks as release manager/scrum master at my customer. Especially when it comes to generating release notes.

Currently I have a query returning all DevOps Items, Ready for Release and tagged with Hotfix. I open them in Excel, format them a bit and paste them into ours Microsoft Teams Wiki. Excel really makes this table-tweaking much easier.

Today Excel kept crashing when I used Open in Excel and I could not figure out why. I used it successfully last week. After some googling I bumped into a forum post that told me to clear cache folders for Team Foundation Service. It helped !! Yay!!!

The article is linked below

Links:
https://developercommunity.visualstudio.com/content/problem/982029/azure-devops-add-on-crashes.html

Problems with Excel AddIn for Azure Devops for Visual Studio 2019

A couple of days ago I reinstalled my computer and since I usually go by “Newer is always Better” I installed Visual Studio 2019. When I was going to generate release notes for our latest release Excel Addin for DevOps did not work. I looked through the prerequisites and found nothing I missed… It should work… WTF

Well… after some research I found this forum post online saying that apparently there is an issue with Excel Addin included in the latest version of Visual Studio. The correct version is here: Azure DevOps Office® Integration 2019

Links:
https://developercommunity.visualstudio.com/content/problem/776394/issue-with-azure-devops-open-in-excel.html

The macro ‘@CurrentIteration’ is not supported without a team context.

Today when writing release notes for my customers latest release I bumped into an issue. I have a query that returns all item in the current Iteration Path with their release notes. The release notes are written in Microsoft Word so I thought that if I could just get the query into Excel it would be an easy “Copy and Past” operation into word.

Fortunately Azure DevOps have just this feature. You simply go to queries, click the three dots negt to the query and select Open in Excel

My query looks like this:

In order to use Open in Excel you first need to install Azure DevOps Office® Integration 2019. After authenticating to Azure DevOps I got an error message… interresting

Turns out the setting @CurrentIteration does not work in Excel… You have to set it to the actual iteration path in order for the Excel Addin to work.

Good to go 🙂

Flow Friday: Getting random row from Excel

Finally back for Flow Friday after some much needed rest over the holidays. This week is sort of a proof of concept. There are times when you need to just get a random value. This could be for a lottery or get a random name for the person that should take out the trash this week.

  1. Create an Excel Sheet in OneDrive with a table containing all of the names you need to randomize
  1. Go to Microsoft Power Automate and create a new flow
  2. Since I want it to run automatically I set it on a schedule
  1. Next we read the Excel file and figure out the number of rows
  1. We then create a variable that is a random number between 0 and [number of rows in the table]
Formula: rand(0,int(length(body(‘ListRows’)?[‘value’])))
  1. Then we get the name column form the row with the random number
Formula: body(‘ListRows’)?[‘value’]?[variables(‘RandomRow’)]
  1. Finally I sent the name in a notification
outputs(‘Compose’)?[‘Name’]

That is all… I will now get a notification with the name of the person that will take out the trash 🙂

Looking at OData in Excel

In Dynamics 365 for Finance and Operations most of the data is represented in Entities which in turn are surfaced as OData feeds for used for integrations. If you need a raw look at the entity you can access the raw OData feed, unfortunately it is not easily readable for humans:

Excel to the rescue. In Excel go to Data – Get Data –  From Other Sources – From OData Feed

A lot more readable… right 🙂

That’s it for today

Problems using Excel Workbook Creator in AX “7”

We have started working with some customers who are looking into implementing The New Dynamics AX (aka AX 7) and one of the very useful features in AX are integration with the Microsoft Office. This has historically been done using an Office Addin which has been installed from the AX Install CD (of deployed by IT). In the new AX version this is done a little differently. You need to install a plugin from the Office Store (which I talk about here).

The other day one of my colleagues came to me and told me that it did work to use the Excel workbook creator… the login failed Sad smile. The login started, the user was redirected to our ADFS site and it was visible for a moment and then it was redirected to a blank place with the address: https://az689774.vo.msecnd.net/dynamicsofficeapp/v1.2.1.0/App/DynamicsApp.html#id_token

SNAGHTMLdc73d34

This is an issue with Office and how Office handles ADFS logins according to this article. According to Microsoft this has been fixed in the 16.0.6868.2060  build of Office 2016. This build is at the moment (2016-05-20) only available for Office Insiders which means that if you are using Office 365 your admin needs to enable you for Current Channel and you need to reinstall Office. 

Links:

https://ax.help.dynamics.com/en/wiki/office-integration-troubleshooting/

Using Office Integration in Dynamics AX

Today one of my customer had trouble using the Excel Workbook Designer in Dynamics AX (“7”). He got the following error when he tried opening the workbook in Excel:

SNAGHTML1023f908

The Error is No Applet Registration found

Turns out he had not installed Microsoft Dynamics Office Add-in which can be found here: https://store.office.com/microsoft-dynamics-office-add-in-WA104379629.aspx?assetid=WA104379629&sourcecorrid=a8afb77b-cfe0-4b70-baeb-f93fa38ad77b&searchapppos=0

Once this was install it worked great Smile

/Johan