Generation of a Custom Excel Quote out of Dynamics CRM

Clients sometimes have pre-existing Quote, Order (and sometimes Invoice) spreadsheets that are based on Microsoft Excel.  The logical reasoning behind this is that when a salesperson initiates a Quote, then this information will be readily available on the subsequent tabs of the spreadsheet in order to generate the Order and possibly the Invoice... and also very easy to print, change, and reprint as needed.

Even with its tight integration with Dynamics CRM, Excel is primary used as a way to export rows and columns of data out of Dynamics CRM where all of the many beneficial Excel-based functions can then be performed [Mathematics (Add, Subtract, Summation, etc.), Grouping/Sorting, Pivot Table reports, Charts, Reports and Trend analysis, etc. ].  The benefits of Excel are well known.  The only issue is that all Excel exports from CRM are derived from this “rows & columns” format, meaning based on a query that gives a result set of individual records with their associated field data. 

What if you want to then generate a Quote form for one specific quote… and that the preferred Quote format happens to be in Excel? Yes, yes… These reports can obviously be recreated as SQL Reporting Service Reports.  In the case where all products are write-in products that are not stored in the CRM product catalog (due to a massive continually changing, multi-vendor sourced catalog of products, product types, product versions, parts, part types, years that a given part are applicable for, etc.), then we're talking about another animal here.  To still gain the benefits of CRM, it is prudent to have enabled sales engineers to generate key client header info [Customer, Quote #, etc.] for the Quote, while also be able to freely add requested parts – as well as being able to automatically have these part totals auto-calculate, which is one of the obvious benefits of Excel.

So, while this code has been completed in CRM 4.0, it will apply just as readily to CRM 2011.  I will post update steps and code in a later release, but my editor’s due date limits my ability update my code to CRM 2011.   

Our tact here will be to selectively push CRM Quote information into the pre-existing Company Excel Quote/Order/Invoice spreadsheet so that it can readily be used by our sales engineers.

First, we need a button on the Quote form that we can press in order to open the selected Excel spreadsheet and then push the selected data into it.

We can create this button modifying the ISVconfig record that we export out of this specific organization.  Here’s the code snippet that needs to be added:

        <Entity name="quote">

          <!-- The Quote Tool Bar -->

          <ToolBar ValidForCreate="0" ValidForUpdate="1">

            <Button Icon="/_imgs/ExcelQuote.bmp" JavaScript="startEXCEL();">

              <Titles>

                <Title LCID="1033" Text="Excel Quote" />

              </Titles>

              <ToolTips>

                <ToolTip LCID="1033" Text="Open Excel Quote" />

              </ToolTips>

            </Button>

          </ToolBar>

        </Entity>

Once the ISVconfig file is uploaded to CRM, here’s what the Quote record looks like with the “Excel Quote” button on it:

Now we can go over to the customization area of the Quote entity.  From here, we want to open up the Form Properties -> Events -> OnLoad -> Select OnLoad and hit the “Edit” button.  Then we can insert the following code into this Quote OnLoad event area:

startEXCEL = function()

{

var myApp = new ActiveXObject("Excel.Application");

if (myApp != null) {

myApp.visible = true;

myApp.workbooks.open("http://Sharepoint/ExcelQuote.xls");

myApp.Sheets("CRM").Cells(1,1) = "ExcelBuildDateTime"

myApp.Sheets("CRM").Cells(1,2) = new Date()

myApp.Sheets("CRM").Cells(2,1) = "quotenumber"

myApp.Sheets("CRM").Cells(2,2) = crmForm.all.quotenumber.DataValue;

myApp.Sheets("CRM").Cells(3,1) = "quotename"

myApp.Sheets("CRM").Cells(3,2) = crmForm.all.name.DataValue;

var customer = new Array;

customer = null;

customer = crmForm.all.customerid.DataValue;

if (customer[0] != null)

{

myApp.Sheets("CRM").Cells(4,1) = "customer"

myApp.Sheets("CRM").Cells(4,2) = customer[0].name;

}

catch (e) {

    alert(e.message);

}

myApp.AlertBeforeOverwriting = false;

myApp.DisplayAlerts = false;

}

As you can see, this code uses the ActiveXObject command to instantiate the Excel application and open the specified Excel Workbook.  It will take the requested information from the CRM Quote record and populate it directly to a new “CRM” tab I’ve created in the Workbook. This tab is then populated by each piece of data based on which cell it is specified to occupy. I’ve chosen to put the actual data in the second column, while inserting its name in the first column – this kind of formatting is purely optional, but good from a testing standpoint.

As a note, I’ve only included the current date, the Quote #, the Name of the Quote, and the name of the Customer.  Any other piece of data contained on the form can be pushed into the Excel file, or else you can make a web service call out to related records to pull back additional information [such as the Address of the Customer, etc.]. 

Once you’ve published your customizations in order to make the whole thing work, you need update your Internet Explorer Security Settings to enable it to launch Excel via ActiveX.  This functionality is turned off by default, but we are enabling it for the trusted CRM url located in our Local Intranet zone.  Here’s the process:

Go to Internet Explorer ->Tools -> Internet Options -> Security tab -> Intranet zone -> Custom Level button -> ActiveX controls and plug-ins section -> “Initialize and script ActiveX controls not marked as safe for scripting”.  Set this to be Enabled.

Once you've got your security settings configured, then just go back to one of your CRM Quotes, click the Excel Quote button and WA-LA!  This image shows additional Quote related info can be pulled down into the spreadsheet using the typical CRM web service calls.  The best part is that the data populates across all of the related Excel Worksheets as long as they reference the appropriate cell on this CRM data Worksheet - Easy Peasy.   Enjoy!