Using the RSSBus Salesforce Excel Add-In From Excel Macros (VBA)
- by dataintegration
The RSSBus Salesforce Excel Add-In makes it easy to retrieve and update data from Salesforce from within Microsoft Excel. In addition to the built-in wizards that make data manipulation possible without code, the full functionality of the RSSBus Excel Add-Ins is available programmatically with Excel Macros (VBA) and Excel Functions. 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 any of the Excel Add-Ins available on our website.
Step 1: Download and install the RSSBus Excel Add-In available on our website.
Step 2: Open Excel and create place holder cells for the connection details that are needed from the macro. In this article, a spreadsheet will be created for batch inserts, and these cells will store the connection details, and will be used to report the job Id, the batch Id, and the batch status.
Step 3: Switch to the Developer tab in Excel. Add a new button on the spreadsheet, and create a new macro associated with it. This macro will contain the code needed to insert a batch of rows into Salesforce.
Step 4:
Add a reference to the Excel Add-In by selecting Tools --> References --> RSSBus Excel Add-In. The macro functions of the Excel Add-In will be available once the reference has been added.
The following code shows how to call a Stored Procedure. In this example, a job is created to insert Leads by calling the CreateJob stored procedure. CreateJob returns a jobId that can be used to upload a large number of Leads in one transaction. Note the use of
cells B1, B2, B3, and B4 that were created in Step 2 to read the connection settings from the Excel SpreadSheet and to write out the status of the procedure.
methodName = "CreateJob"
module.SetProviderName ("Salesforce")
nameArray = Array("ObjectName", "Action", "ConcurrencyMode")
valueArray = Array("Lead", "insert", "Serial")
user = Range("B1").value
pass = Range("B2").value
atoken = Range("B3").value
If (Not user = "" And Not pass = "" And Not atoken = "") Then
module.SetConnectionString ("User=" + user + ";Password=" + pass + ";Access Token=" + atoken + ";")
If module.CallSP(methodName, nameArray, valueArray) 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
Next
While (Not module.EOF)
Range("B4").value = module.GetValue(idIndex)
module.MoveNext
Wend
Else
MsgBox "The CreateJob query failed."
End If
Exit Sub
Else
MsgBox "Please specify the connection details."
Exit Sub
End If
Error:
MsgBox "ERROR: " & Err.Description
Step 5: Add the code to your macro. If you use the code above, you can check the results at Salesforce.com. They can be seen at Administration Setup -> Monitoring -> Bulk Data Load Jobs. Download the attached sample file for a more complete demo.
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 RSSBus Salesforce Excel Add-In is already installed.
Macro Sample File
Please download the fully functional
sample excel file that includes the code referenced here. You will also need the RSSBus Excel Add-In
to make the connection. You can download a free trial here.
Note: You may get an error message stating: "Can't find project or library." in Excel 2007, since this example is made using Excel 2010. To resolve this, navigate to Tools -> References and uncheck the "MISSING: RSSBus Excel Add-In", then scroll down and check the "RSSBus Excel Add-In" listed below it.
