Using Microsoft Excel as a Data Entry Tool for Microsoft Dynamics CRM

Suppose the business sales representative of a company are on the road and traveling for 12 hours.  On the road, their internet connectivity has been challenging with respect to access and bandwidth, so they have been logging their sales opportunities into a data intensive excel sheet collecting.   At home, the sales representatives chooses to import the data into Microsoft Dynamics CRM rather than re-keying the information.

Microsoft Excel does not have direct capability to Dynamics CRM for inserting, deleting or updating CRM; however, Excel does have this functionality within its access WebPages and the internet through the uses of the classes:

Microsoft WinHTTP Services, version 5.1 uses C:Windowssystem32winhttp.dll
Microsoft Basic for Applications Extensibility uses C:Program Files (x86)Common FilesMicrosoft SharedVBA
Microsoft HTTML Object Library uses C:WindowssysWOW64mshtml.tlb.DLL
Microsoft XML v6.0 uses C:WindowssysWOW64msxml6.tlb.DLL

These classes are provided by Microsoft as native system components.  Excel does not reference these classes by default, so when developing Excel to communicate with CRM, you must first reference these classes.

Here are the steps to add the class references in an Excel Macro-enabled workbook:

1. Click on the Developer Tab on the Ribbon (if, does not exist go to http://msdn.microsoft.com/en-us/library/vstudio/bb608625.aspx) 
2. Select the Visual Basic icon
3. When the Microsoft Visual Basic Application opens, select Tools and then References to open the References Form
4. Add the references to the classes as outlined above

Before you can develop with VBA to communicate with Dynamics CRM, build a webpage(s) that will perform the required tasks in Dynamics CRM such as creating a new opportunity or updating an account record. The webpage(s) must send a response back as well to handle the different scenarios, i.e. “Account already exists.”

Like creating a plug-in, the webpage that will be built will utilize the Dynamics CRM system Organization Web Service which is the primary service to accessing the data and metadata in Dynamics CRM system.  For the ability to create, update, delete without having many limitations, the OData methodology is suitable.  However, the .Net Language-Integrated Query (LINQ) will do the same except will not allow for outer-joins.

Without regards to which methodology that is chosen for inserting, deleting, or updating Dynamics CRM, the code written would require the need to determine what information was being passed and how much data to the webpage and URL.  The URL having a size limitation, a significant amount of data being passed to the webpage should require the data to be passed via XML in the Http body of the request post.

Note: you might consider passing an opportunity id in the URL if you were updating to do some handling before you parsed the XML.

From our example above, let’s assume that the data in the spreadsheet looked like this and our goal was to insert a new opportunity:

The VBA would take the follow fields and place them into a string in XML format such as:

Dim xmlString as String
xmlString =       <Opportunity> _
                                    <field Content=”Owner”> Bob Hope < field /> _
                                    < field Content =”Address”> New Street < field /> _
                                    < field Content =”Phone”>888888888< field /> _
                                    < field Content =”Contact”> Lisa Hope < field /> _
                                    < field Content =”Product”> Large ABC Shirt < field /> _
                                    < field Content =”Quantity”>50< field /> _
<Opportunity/>

Building your xml string can be elaborate where your nodes can also contain attributes such as <Opportunity Assigned=”Frank”>.  This example of an attribute provides a name which could represent the value of the sales representative’s name.

In VBA, the WinHTTP reference plays an important role in sending the request to the target webpage URL.  The first step that needs to be completed is building the WinHTTPRequest variable such as the following:

Dim web as WinHttpRequest
Set web = New WinHttpRequest
web.Open “POST”, “http://somedomain/yourcreatedblankwebpage.aspx”, False
web.SetTimeouts 1000, 1000, 1000, 1000
web.SetRequestHeader “User-Agent”, “Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)”
web.SetRequestHeader “CONTENT-TYPE”, “text/xml”
web.SetAutoLogonPolicy AutoLogonPolicy_Always

After the variable for web is created, the next step is to use the web variable to send the xmlString which is completed by using:

web.send xmlString

At this point, a message from Excel to the created webpage has been sent and Excel is expecting a response which must be handled on two levels.  The first level is to handle the response from the general web statuses (web.status) such as 500,401,200, although there may be other responses that might need to be handled (a list of responses can be found here: http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html). The 500 status is for internal server errors while 401 are for unauthorized users. If there is a status other than 200, then a handler in VBA should do something based on the requirements.

The response necessary to continue is when web.status = 200 which acknowledges that the request to the webpage has succeeded depending on which method was used.  In the sample above, the “POST” method was used. The second level of handling comes after the 200 status is received.  At this point, the web.ResponseText should be read and handled based on what the webpage sends back as a response.  The web.ResponseText could send “Success”, “Success-Account Added”, or “Failure-Creating New Opportunity” among many other responses that have been determined.  The web.ResponseText is determined by what the webpage posts back as a response.

The building of the webpage(s) would require the following classes to be referenced:

• Microsoft.Xrm.Sdk
• Microsoft.Xrm.Sdk.Client
• Microsoft.Xrm.Sdk.Query
• System.Xml
• System.Xml.Linq
• System.IO

Like any plug-in code, the organization service URL must be set and the service must be built. Once the service has been created your code should check for several things besides the general Dynamics CRM task execution programming.

If the Dynamics CRM task was to insert a new opportunity, the content of the request to the webpage should be checked first.  Since in this example the request method used is a “POST” with a content type of “text/XML” a check would utilize the Request.ContentType and check it against the request, i.e.

If (Request.ContentType == “text/xml; Charset=UTF-8”) {“code here”}

Once the Request.ContentType has been validated, a text reader would be need to created, XML checked for validity, and then the XML parsed. The parsed XML would require the nodes to be put into an array with the attribute, the value, and the type.

Once the array is created, the array is handled to create the new opportunity (in this example).  The attribute data structures should be handled when looping through the array to create the opportunity.  The best method of doing so would be to use case statements in a loop to set the attributes values to the correct value and structure. For example of an attribute of a money data structure then the following code would be an example:

            Opportunity[Array[i].fieldName] = new money(Convert.ToDecimal(Array[i].fieldValue));

The creation of the opportunity should be coded in a try catch statement and send web response back to the request.  If the try statement executed without issues then the last statement of the try would include the Response.Write(“your message”), allowing the VBA to handle the web.ResponseText. The same line of code would be used in the catch statement as to be handled in the web.ResponseText when the web.status is 200.

The final step in the process would, without further-a-due, deploy to the test environment and test, and test, and then do some more testing.  It is important to capture the many different scenarios and develop handlers for them on both the VBA and .net side of the code.

If you are looking to update Dynamics CRM records from Excel and need help creating the code, RSM can help.  We have a team of experienced developers across the United States that can write code unique to your business needs.  Contact our professionals today at 855.437.7202 or by email at crm@mcgladrey.com.

By: Brian Connelly – New Jersey Microsoft Dynamics CRM partner

Meg Moss