Excel / sforce Add-in

Note: This add-in was written by Ron Hess at Neoforma, and was graciously open-sourced by him for the benefit of the sforce community � neither Ron nor salesforce.com can provide support for or guarantee correct performance of this tool � use at your own risk!

Downloading

A zip file with the .xla and this document is attached to the bottom of this message; rename the file from .zp to .zip to decompress.

Features

This simple add-in for Excel wraps the sforce API so that it can be used to query and update salesforce.com-based data directly from a spreadsheet. The tool allows API commands like describe, query, update and insert to be performed directly from cells, and has been tested on Excel XP (10) and 2003 (11).  The tool uses XMLRPC, and is written in VBA.

Quick Start

Install the XLA file into Excel using the Tools -> Add-Ins... , then Browse to locate the file and open it.

1. In cell A1, enter the name of a valid entity.
2. Select �Describe Table� from the salesforce drop down menu
3. Enter a query in cells B1, B2, and B3; for example �company | contains | sforce�
4. Select �Query Table� from the salesforce.com drop down menu
5. After changing the value of a cell, select â¬?Update selected cellsâ¬? to send the new values back to salesforce.com.   

Usage

Once the floating Menu bar appears you can begin to run commands.

First try "Describe New Table" to see the format that a table takes on the worksheet.  The format is defined by the Excel "Current Region" so that multiple tables can be placed on one sheet.  A Region is defined as the cells bounded by empty rows and columns.

The Table name appears in the upper left cell ( "A1" ) and the column names appear in row 2, ( "A2:Z2" ). The data begins in the range below that ("A3:Z999" )

To fill the table with data, you will need to construct a filter for the Query command, there is no list command. The filter is placed into three cells, in this example this range is B1 : D1, an example filter would be

A1="account"

B1="Account Name"

C1="contains"

D1="3M"

This returns the data specified by the labels found in row #2.

full example :

account|Account Name|contains|3M

account_id|Account Name|Billing City

With the cursor in or near these cells, the Query table menu item will query the database for accounts with the name 3M, it will return the id, name and city for these.  For completeness there are provisions for 3 query filters, in the first row, each to the right of the preceding, i.e: E1:G1 and H1:J1 can hold each hold an additional filter

Some queries can produce a large list and Excel will appear to hang while working on very large queries, so please exercise care when constructing queries to avoid more than ~ 1000 rows. 

Larger queries are best done in salesforce.com reports tab, exported to excel with the table ID in the first column, then you can add a new row, above the columns to hold th table name, then updates can be done.

Update, after a table is described and queried , you may edit cells, then select them, then click Update Selection menu button, only the cells which are selected will be updated to Salesforce.

Insert, this operates on the same data tables, substitute the entity ID with the string "new" and the row will be uploaded to Salesforce as a new record.

Describe a table , fills into a row of cells, the labels for all columns of the table

Query a table using filter operator and value, fill out the data columns of a table given filters in the first row of the current region of the worksheet

Update cells, allows changes made in cells of the worksheet to be uploaded or "sfdc.update" ed from the workbook to Salesforce.

Insert new rows into a table directly from a worksheet.  Using the data on a row, insert the fields into a new record in the table described in the current region

public VBA Function "=sflookup(table name, cell)" allows looking up Salesforce ID's given names in a spreadsheet, very handy for indexing external lists into sales force ID's

Not Supported

Several features which would be handy are not implemented, these include

Compression is not used

Batching is not used

Version of the API is currently back-revved

Wizard functionality is highly desirable, not yet complete.

No optimization done for re-using sessions until they expire

There is no end user documentation at this time

Delete is not supported

Picklists are not enforced on update or insert

Message Edited by adamg on 11-04-2003 05:18 PM

Message Edited by adamg on 11-04-2003 05:19 PM