Connecting Excel to Salesforce

Devart Excel Add-in for Salesforce allows you to connect Excel to Salesforce databases, retrieve and load live Salesforce data to Excel, and then modify these data and save changes back to Salesforce. Here is how you can connect Excel to Salesforce and load Salesforce data to Excel in few simple steps.

 

 

To start linking Excel to Salesforce, on the ribbon, click the DEVART tab and then click the Get Data button. This will display the Import Data wizard, where you need to create Excel Salesforce connection and configure query for getting data from Salesforce to Excel:

Salesforce Excel add-in

 

1. Specify Connection Parameters

To link Excel to Salesforce, first select the authentication Our Salesforce Excel connector supports two authentication types for Salesforce: AccessRefreshToken and UserNamePassword.

If you want to connect Excel to a sandbox environment (test.salesforce.com) or to Database.com, you also need to select the corresponding Host. Or, if you connect to a custom environment, just type the corresponding host in this box.

For AccessRefreshToken authentication, click Web Login and sign in to your Salesforce account. Other required parameters are filled automatically. This authentication does not store Salesforce user name and password. Instead it uses an OAuth authentication token, which can be revoked at any time in Salesforce.

For UserNamePassword authentication, you need to enter the necessary connection parameters in the Connection Editor dialog box, in addition to Host:

  • User Id - your Salesforce account email.
  • Password - your Salesforce password.
  • Security Token - Salesforce security token - an automatically generated key that is used to log into Salesforce from an untrusted network. To generate the security token, login to the Salesforce website, click Setup, then select My Personal Information -> Reset My Security Token.
Link Excel to Salesforce

If you need to configure your Excel Salesforce connector in more details, you can optionally click the Advanced button and configure advanced connection parameters. There you can configure connection encryption, resiliency parameters, etc.

To check whether you have connected Excel to Salesforce correctly, click the Test Connection button.

2. Select whether to Store Connection in Excel Workbook

You may optionally change settings how the connection and query data are stored in the Excel workbook and in Excel settings:

 

3. Configure Query to Get Data

You may either use Visual Query Builder to configure it visually, or switch to the SQL Query tab and type the SQL Query. To configure query visually, do the following:

  1. In the Object list select the Salesforce table to load its data to Excel.

  2. In the tree below clear check boxes for the columns you don't want to import data from.

  3. Optionally expand the relation node and select check boxes for the columns from the tables referenced by the current table's foreign keys to add them to the query.

  4. In the box on the right you may optionally configure the filter conditions and ordering of the imported data and specify the max number of rows to load from Salesforce to Excel. For more information on configuring the query you may refer to our documentation, installed with the Excel Add-ins.

After specifying the query, you may optionally click Next and preview some of the first returned rows. Or click Finish and start data loading. In this way you can easily export Salesforce Contacts or other data to Excel.

Import data from Salesforce to Excel

 

Editing Live Salesforce Data

After the data is loaded from Salesforce to Excel, you can work with these data like with usual Excel worksheet. You can instantly refresh data from Salesforce by clicking Refresh on the Devart tab of the ribbon, and thus, always have fresh live data from Salesforce in your workbook.

If you want to edit Salesforce data in Excel and then update Salesforce from Excel, you need to click Edit Mode on the Devart tab of the ribbon first. Otherwise, the changes you make cannot be saved to Salesforce.

After you start the Edit mode, you can edit the data as you usually do it in excel - delete rows, modify their cell values. Columns that cannot be edited in Salesforce, will have Italic font, and you cannot edit values in these columns. To add a new row, enter the required values to the last row of the table that is highlighted with green.

Update Salesforce from Excel

 

To apply the changes to actual data in the database, click the Commit button. Or click Rollback to rollback all the changes. Please note that the changes are not saved to the database till you click Commit, even if you save the workbook.

Want to know more ways of loading Salesforce data to Excel?

Read blog post