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.