Stephanie

I am not sure what you want to achieve. There are products like Sage Intelligence which are Excel based that can pull data from Sage CRM. Actually it is pretty straight forward to pull data from a SQL Server database directly into MS Excel spreadsheets. But what is the user scenario that you are wanting to allow?

Hi Jeff,

Two clients in particular have a custom Excel file which houses their complicated pricing database and quoting template. In construction, the pricing databases can change frequently and updating the products in CRM would be too time consuming. They would like to perform a merge from an Opportunity, to pull the Company, Person, Address and Opportunity Description into specific cells of the existing Excel template. Is this possible?

Stephanie

I don't have an example to hand.

But I know that Excel can connect to a database and pull up data from a SQL Server table or view. This can then be processed by VBA to take the returned data and add to any cell that you want.

For example... You can open a spreadsheet, the user can be prompted for the company name. The user can then enter the name, the spreadsheet then can search for the name and then populate cells in another sheet within the workbook.

I haven't worked with Excel enough to know whether you can pass parameters from a browser URL to a workbook that is opened in a link.

I don't have an example to show, but, first of all you want to get yourself a stored procedure written that pulls the data you need. If you need more than one, then create as many as needed. Then you can run the stored procedures from Excel using VBA:

support.office.com/.../Connect-a-SQL-Server-database-to-your-workbook-Power-Query-22c39d8d-5b60-4d7e-9d4b-ce6680d43bad

Once you have pulled your data, then you only need the functions to put it in the correct cells which isn't that hard to do.

When you create your connection to the SQL database from within the VBA code, I would password protect the VBA in the spreadsheet as you don't want the connection details getting out.

Here is an example of a VBA based connection www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm

Thank you Jeff and Lee!