ErpCoder

Query Dynamics 365 FSCM OData from Excel VBA

To query D365 Finance and Supply Chain Management OData entities from external sources you can use simple HTTP GET Requests. Here is an example for Excel and VBA code.

App Registration in Microsoft Entra ID

In Entra ID (Azure Active Directory) create a new app registration.

• Provide a meaningful name for your registration
• Select only accounts in the organization
• Leave the Redirect URL blank
• Create a new secret. Make sure to copy the secret value because it will not be shown again.
• Give the API Permissions for Application Connector.FullAccess and Delegated Odata.FullAccess

Configure D365 F/SCM Entra ID Application

In Dynamics 365 Finance and Supply Chain Management navigate to System Administration > Setup > Entra ID Application. Create a new record using the Client ID from the app registration, a meaningful name and associate a user account in D365.

Request a Bearer Token

To request a bearer token we have to call login.microsoft.com with the Tenant ID and v2.0 version:
https://login.microsoft.com/TENANT_GUID_HERE/oauth2/v2.0/token . The tenant ID is a GUID that can be found in Entra ID Portal. You have to provide additional data in the request:

• tenant_id: Again the GUID of your Azure Active Directory
• client_id: App registration client ID
• client_secret: App registration secret value
• grant_type: “client_credentials” (hardcoded string value for this type of request)
• scope: your Dynamics 365 FSCM URL followed by /.default

I recommend to try this in Postman before coding in Excel VBA because it’s easier to debug and test.

Here is the Excel VBA code to call an HTTP POST request and send the data to get a token. Fill in your values for tenant, client, secret, scope. The result should look like this:

I’m not parsing the JSON in my example but use String functions. Therefore I’m looking for the location of access_token in the response and cut out the bearer token string. I know this is not the best way to do this but it’s only an example. VBA and Excel have a limited view on strings. Don’t get confused if the value you see in the debug window or MSGBOX is not the complete bearer token string.

Dim objHTTP As Object
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")

Dim strURL As String
strURL = "https://login.microsoft.com/_YOUR_TENTANT_ID_/oauth2/v2.0/token"

Dim strData As String
strData = "tenant_id=__&client_id=___&client_secret=___&grant_type=client_credentials&scope=___.operations.eu.dynamics.com/.default"
    
objHTTP.Open "POST", strURL, False
objHTTP.send (strData)
    
Dim strResponse As String
strResponse = objHTTP.responseText

Set objHTTP = Nothing

'Find the access token in the response
Dim posAccessToken As Integer
posAccessToken = InStr(1, strResponse, "access_token", vbTextCompare)
posAccessToken = posAccessToken + 14
        
Dim token As String
token = Right(strResponse, Len(strResponse) - posAccessToken)
token = Left(token, Len(token) - 2)

Query OData Entity

You can query entities using HTTP GET and provide OData query syntax values like $filter and $select. In my example I’m looking for a certain customer by its CustomerAccount and select only the OrganizationName. The %20 and %27 in the address are URL encoded Blanks _ and Quotes “.

Like in the code above I’m using string functions instead of JSON parsing for this example. The customer account is in cell B2, the name will be written to B3. I had to manually add a reference to MSXML2.ServerXMLHTTP.6.0 version in the VBA Code Editor (Menu Bar, References). Here is the VBA code to call the Odata query.

Dim custAccount As String
custAccount = Cells(2,2).Value

Dim objHTTP2 As Object
Set objHTTP2 = CreateObject("MSXML2.ServerXMLHTTP.6.0")

Dim strUrl2 as String
strURL2 = "https://_YOUR_INSTANCE_.operations.eu.dynamics.com/data/CustomersV3?$filter=CustomerAccount%20eq%20%27" & custAccount & "%27&$select=OrganizationName"

objHTTP2.Open "GET", strURL2, False
objHTTP2.setRequestHeader "Authorization", "Bearer " & token
objHTTP2.send

Dim strResponse2 as String        
strResponse2 = objHTTP2.responseText
Set objHTTP2 = Nothing

Dim posCustomerName As Integer
posCustomerName = InStrRev(strResponse2, "OrganizationName")
posCustomerName = posCustomerName + 18

Dim d365CustomerName As String
d365CustomerName = Right(strResponse2, Len(strResponse2) - posCustomerName)
d365CustomerName = Left(d365CustomerName, Len(d365CustomerName) - 3)
d365CustomerName = Left(d365CustomerName, InStr(1, d365CustomerName, "}") - 1)

Cells(2,3).Value = d365CustomerName