Insert Dynamics 365 FSCM OData via Excel VBA

If you have the need to insert data into Dynamics 365 Finance and Supply Chain Management via Excel but don’t want to use the Addin you can do this task manually via VBA Code. Here is an example how to create a new customer via CustomerV3 OData entity.

App Registration

You have to register a new application in Entra ID. Allow API access to Dynamics 365 FSCM Entities. Create an application secret and note the value somewhere. Next you have to register the App ID (Client ID) in Dynamics 365 FSCM in the Entra ID Application form.

Authentication

OData in Dynamics 365 FSCM uses OAuth for authentication. You have to request an access token first and use it later for the communication with D365. In Excel create a VBA function that request an access token based on the Application Registration.

Function CreateAccessToken() As String
  Dim tenantId As String
  Dim clientId As String
  Dim secret As String
  Dim scope As String

'PROVIDE YOUR DATA HERE
  tenantId = ""
  clientId = ""
  secret = ""
  scope = "https://YOUR_D365FO_URL_dynamics.com/.default"

  Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")

  strURL = "https://login.microsoft.com/" & tenantId & "/oauth2/v2.0/token"
  strData = "tenant_id=" & tenantId & "&client_id=" & clientId & "&client_secret=" & secret & "&grant_type=client_credentials&scope=" & scope & ""

  objHTTP.Open "POST", strURL, False
  objHTTP.send (strData)
  strResponse = objHTTP.responseText
  Set objHTTP = Nothing

  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)

  CreateAccessToken = token
End Function

Data Entry Form

Create a form in Excel to provide the required data. In my example there is no AccountNum because it is generated in Dynamics 365 FSCM from a Number Sequence. Add a button and link it to a VBA Macro.

Call the CustomerV3 Entity from VBA

To create a new customer create an HTTP POST request to the CustomerV3 OData Entity. Provide the required fields as JSON String. Here is an example:

Sub CreateCustomerV3()

  Dim url As String
  Dim http As Object
  Dim data As String
  Dim response As String

  Dim token As String
  token = CreateAccessToken

  url = "https://YOUR_D365FO_ENV.dynamics.com/data/CustomersV3"

  Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")

  Dim name As String
  Dim cur As String
  Dim grp As String
  Dim cmp As String

  name = Cells(3, 3).Value
  cur = Cells(4, 3).Value
  grp = Cells(5, 3).Value
  cmp = Cells(6, 3).Value

  data = "{""OrganizationName"" : "" " & name & " "",""SalesCurrencyCode"": """ & cur & """,""CustomerGroupId"": """ & grp & """,""dataAreaId"" : """ & cmp & """}"

  http.Open "POST", url, False
  http.setRequestHeader "Authorization", "Bearer " & token
  http.setRequestHeader "Content-Type", "application/json"
  http.send data

  response = http.responseText

  'Only for debugging
  Cells(10, 5).Value = response
End Sub