posts - 88 , comments - 3 , trackbacks - 0

Access Salesforce From Excel Macros (VBA)

The CData Salesforce Excel Add-In makes it easy to retrieve and update data from Salesforce from within Microsoft Excel. In addition to using the CData ribbon, you can use Excel Macros (VBA) and Excel Functions to access the full functionality of the CData Excel Add-Ins.

In addition to create, read, update, and delete operations, the add-in also includes stored procedures that expose other capabilities supported by the Salesforce API. This article shows how to write an Excel macro that can be used to perform bulk inserts into Salesforce. Although this article uses the Salesforce Excel Add-In as an example, the same process can be applied to execute any stored procedure with a CData Excel Add-In.

Run the Sample Macro

The following sections explain the basic steps of how to create a simple spreadsheet that creates a job to bulk insert Salesforce Leads. Download the sample spreadsheet for a fully functional example.

After entering the connection properties in the spreadsheet, click Insert Leads to insert some sample data. To check the results, click Check Batch Status. You can also check the results at They can be seen at Administration Setup -> Monitoring -> Bulk Data Load Jobs.

Note: This example is compatible with Excel 2010 and above. You can modify the example to be compatible with Excel 2007 with the following steps: Navigate to Tools -> References, uncheck "MISSING: CData Excel Add-In", and then scroll down and check the "CData Excel Add-In" listed below it.

Add a Macro to a Spreadsheet

Follow the steps below to create a simple spreadsheet that can automate tasks in Salesforce. The same example as the example spreadsheet is used. For example, creating a job to bulk insert Leads.

  1. Open Excel and create placeholder cells for the required connection properties: User Name, Password, and Access Token.
  2. Create placeholder cells that the macro will use to report on the status of the bulk insert: the Id of the newly created job, the Id of the batch, and the batch status.
  3. Switch to the Developer tab in Excel. Click Insert and click the button icon to add an "Insert Leads" button to the spreadsheet. A new macro will be associated with the button.
  4. Name the macro CreateJob. This macro will contain the code needed to insert a batch of rows into Salesforce. When the macro is created, Visual Basic for Applications is opened.

    You can also create and edit macros by clicking Developer -> Macros.

  5. In Microsoft Visual Basic for Applications, add a reference to the Excel Add-In by selecting Tools --> References --> CData Excel Add-In. The macro functions of the Excel Add-In will be available once the reference has been added.

Connect to Salesforce from Code

First, define the required variables: The connection properties and the CData.ExcelAddIn.ExcellComModule instance. The lines below read the connection properties from the spreadsheet:

Dim user As String
Dim pass As String
Dim atoken As String
user = Range("B1").value
pass = Range("B2").value
atoken = Range("B3").value

To initialize the module, use the code below after adding the reference to the add-in:

Dim module As New ExcelComModule
module.SetProviderName ("Salesforce")

The macro functions of the add-in can be accessed by invoking the methods of the CData.ExcelAddIn.ExcelComModule class. To connect to Salesforce from a macro function, initialize the module and call the SetConnectionString method to provide the required connection properties.

The line below performs some basic validation and then provides the connection properties as a connection string, a semicolon-separated list of name-value pairs:

If (Not user = "" And Not pass = "" And Not atoken = "") Then
module.SetConnectionString ("User=" + user + ";Password=" + pass + ";Access Token=" + atoken + ";")

Call Stored Procedures

After initializing the module and setting the connection string, you can create the bulk insert job. To use the Excel add-in to perform bulk inserts, you can call the CreateJob and CreateBatch stored procedures. Stored procedures are function-like interfaces that can be used to search, modify, and update data. They expose functionality in the underlying API that cannot be represented as create, read, update, and delete (CRUD) statements.

The macro functions of the add-in can be accessed by invoking the methods of the CData.ExcelAddIn.ExcelComModule class. You can use the CallSP method to call stored procedures.

The example below shows how to call the CreateJob procedure and report the Id of the newly created job. You can iterate over the names of the output parameters returned by calling GetColumnName(column-number). You can iterate over the values by calling GetValue(column-number).

See the help documentation for more information on the available methods for the ExcelComModule class.

If module.CallSP("EXEC CreateJob ObjectName=Lead, Action=Insert, ConcurrencyMode=Serial;") Then        
Dim ColumnCount As Integer
ColumnCount = module.GetColumnCount
Dim idIndex As Integer
For Count = 0 To ColumnCount - 1
Dim colName As String
colName = module.GetColumnName(Count)
If module.GetColumnName(Count) = "id" Then
idIndex = Count
End If
While (Not module.EOF)
Range("B4").value = module.GetValue(idIndex)
MsgBox "The CreateJob query failed."
End If
Exit Sub
MsgBox "Please specify the connection details."
Exit Sub
End If
MsgBox "ERROR: " & Err.Description

Distributing an Excel File With Macros

An Excel file with macros is saved using the .xlms extension. The code for the macro remains in the Excel file, and you can distribute your Excel file to any machine where the CData Salesforce Excel Add-In is already installed.

Print | posted on Tuesday, November 27, 2012 8:36 AM |


No comments posted yet.
Post A Comment

Powered by: