Learn more
 

Ready to get started?

Download a free trial of the Excel Add-In for Salesforce to get started:

 Download Now

Learn more:

Salesforce Icon Excel Add-In for Salesforce

The Salesforce Excel Add-In is a powerful tool that allows you to connect with live Salesforce account data, directly from Microsoft Excel.

Use Excel to read, write, and update Leads, Contacts, Opportunities, Accounts, etc. Perfect for mass imports / exports / updates, data cleansing & de-duplication, Excel based data analysis, and more!

In this article

Related articles

Excel Spreadsheet Automation with the QUERY Formula


Pull data, automate spreadsheets, and more with the QUERY formula.

The CData Excel Add-In for Salesforce provides formulas that can edit, save, and delete Salesforce data. The following three steps show how you can automate the following task: Search Salesforce data for a user-specified value and then organize the results into an Excel spreadsheet.

The syntax of the CDATAQUERY formula is the following: =CDATAQUERY(Query, [Connection], [Parameters], [ResultLocation]);

This formula requires three inputs:

  • Query: The declaration of the Salesforce data records you want to retrieve or the modifications to be made, written in standard SQL.

  • Connection: Either the connection name, such as SalesforceConnection1, or a connection string. The connection string consists of the required properties for connecting to Salesforce data, separated by semicolons.

    There are several authentication methods available for connecting to Salesforce: Login, OAuth, and SSO. The Login method requires you to have the username, password, and security token of the user.

    If you do not have access to the username and password or do not wish to require them, you can use OAuth authentication.

    SSO (single sign-on) can be used by setting the SSOProperties, SSOLoginUrl, and TokenUrl connection properties, which allow you to authenticate to an identity provider. See the "Getting Started" chapter in the help documentation for more information.

  • ResultLocation: The cell that the output of results should start from.

Pass Spreadsheet Cells as Inputs to the Query

The procedure below results in a spreadsheet that organizes all the formula inputs in the first column.

  1. Define cells for the formula inputs. In addition to the connection inputs, add another input to define a criterion for a filter to be used to search Salesforce data, such as Name.
  2. In another cell, write the formula, referencing the cell values from the user input cells defined above. Single quotes are used to enclose values such as addresses that may contain spaces.
    3. =CDATAQUERY("SELECT * FROM Account WHERE Name = '"&B4&"'","User="&B1&";Password="&B2&";SecurityToken="&B3&";Provider=Salesforce",B5)
  3. Change the filter to change the data.