You can use VBA to work with the underlying SFDC database access functions of XL-Connector. You can’t record macros with XL-Connector buttons (it wouldn’t do you any good if you could…) so you have to work with VBA code in order to do automation with the SFDC data.

Before you start, you have to get the XL-Connector COM addin properly instantiated in your worksheet’s memory space. And that means putting this code at the top of your VBA module or in an “auto_open” sub for the spreadsheet. Here’s the code you need:

Dim automationObject As Object
Set automationObject = Application.COMAddIns("TaralexLLC.SalesforceEnabler").Object

Don’t forget to make the automationObject public in the main declarations part of your module.

Before you begin writing code…

  • When you’re working with SFDC data through XL-Connector, everything is presented to you as arrays that are addressed only by tuple coordinates.
  • When you’re working with queries, remember it’s only SOQL syntax, not full SQL. So, forget about “SELECT *”. Use XL-Connector’s query-builder window to test any query string that you have, since the error messages you’ll get back from VBA will be misleading, or useless, or both.
  • You’d think that the catch-all “Variant” array data type would be fine swallowing a string array…but it isn’t. So pay attention when a method’s return array is shown as being strings.
  • When you get a dateTime string from a query, it will be in this format: YYYY-MM-DD HH:MM:SS, but when you want to query based on a dateTime, you need to use this one: YYYY-MM-DDTHH:MM:SSZ.
  • If your query has an empty result set, you will not get an array back from SFDC: you’ll get a null object that will cause error messages in your code if you try to do anything with it.

Here are the calls available to your VBA code:

Log in to Salesforce.com

LogIn (username, password+securityToken, host, errorText)

Returns true or false, in case of false – examine the error text

Log out of SFDC instance

LogOut ()

Returns true or false

Put XL-Connector in Safe Mode (Read Only, disabling insert/update/delete buttons but NOT VBA actions)

 SetReadOnly (True)

Return from Safe Mode

SetReadOnly (False)

Get the URL of the instance you are grabbing data from

 string[,] GetServerURL()

Get all the objects in the system

string[,] GetObjects(errorText))

Get all the field names from an object

string[,] GetObjectFields(string objAPIName, out string errorText)

Get all the picklist values names for a field

string[,] GetPickListValues(string objectName, string fieldName, out string errorText)
///returns three columns: value, label, isActive

Retrieve data

object[,] RetrieveData(string query, bool label, bool queryAll, out string errorText, [bool toolingAPI])

Update records

string[,] UpdateData(object[,] data, string objName, bool useAssgnRule, string assignmentRuleId, out string errorText)

Insert records

string[,] InsertData(object[,] data, string objName, bool useAssgnRule, string assignmentRuleId, out string errorText)

Upsert records

string[,] UpsertData(object[,] data, string objName, string extIdColumn, bool useAssgnRule,</b> <b>string assignmentRuleId, out string errorText)

Delete records

public string[,] DeleteData (string[] Ids, out string errorText)

Pull report from SFDC by Id – returns a path to the saved CSV file (note, it does not put it into the open Excel worksheet…you pull it in with your own VBA…see example code lower down)

string RunReport(string id, out string errorText)

Refresh the data in the active worksheet

string Refresh(False)

Refresh the data all worksheets

string Refresh(True)

Run the Flow in a given worksheet

bool RunFlow(Worksheet ws, out error)

This function will run all active Flow steps in a given worksheet. If there’s an error, the function will return False and the error text will be in the ‘error’ output variable.

Run all Flows in the active workbook

bool RunAllFlows(out error)

This function will run all Flows in your document. If there’s an error, the function will return False and the error text will be in the ‘error’ output variable.
Retrieve metadata files from Salesforce.com

This command will download all metadata files (the XML goodies you can see in Eclipse) of the specified object type and put them into the specified folder. Be careful, if you pull the same metadata files twice, the second time will overwrite the files pulled during the first time. Please note that if you are specifying standard object types, you will need to have “*” as the first item in the array (which gets you all the custom object definitions… don’t ask me why the SFDC guys did it that way…). The array that is returned by this call will contain the names of every metadata file pulled during the call.

 GetMetadata(Array type, string folderName, out string errorText, [Array additionalTypes])

The additionalTypes parameter is optional, if it’s omitted – Array(“*”) will be used.

Here’s a snippet of how to use this call:

Dim sObjects()
Dim sTypes()
sTypes = Array("CustomObject") 
sObjects = Array("*", "Task", "Event", "Campaign", "CampaignMember", "Lead", "Account", "Contact", _
 "Opportunity", "OpportunityLineItem", "Case", "Entitlements", "Contract", _
 "Solutions", "Product", "Pricebook", "Ideas", "Question", "Reply", "Asset", "User")
 ' this is the list of standard objects, after the first * which indicates "get all custom objects"
 Dim MDfiles() as String
 MDfiles = automationObject.GetMetadata(sTypes, "C:\Temp", error, sObjects)
 ' MDfiles() is the array of file names that will be created, in this case, in the C:\Temp\Object directory
 Dim fso As New FileSystemObject
 Dim fname As TextStream
 ' do NOT use VBA's internal "Open File for Input" method, as the Input Line statement will draw in the 
 ' entire file as a single line of text. Yes, really.
 ' Instead use the Microsoft Scripting Library methods that do more sensible things...you may need to add
 ' it to your References for the project.
 Set fname = fso.OpenTextFile(MDfiles(0)) ' this is just an example of opening the first file in this list
 Do While Not fname.AtEndOfStream ' this is fancy-talk for EOF
 debug.print (fname.ReadLine) ' pull in the first line of text, as terminated by an LF (ASC-10) character
 Loop
 fname.Close ' do your housekeeping to avoid messes!

And here’s a sample of usage of most of the calls above:

Sub Button1_Click()
   Dim automationObject As Object 
   Set automationObject = Application.COMAddIns("TaralexLLC.SalesforceEnabler").Object
   Dim error
   result = automationObject.LogIn("my@login.com", "password+token", "https://login.salesforce.com", errorText)   
   If result = False Then
       MsgBox errorText
       End
   End If
   
   'getting information about object fields
   fieldinfo = automationObject.GetObjectFields("Account", errorText)
   If Not error = Empty Then
       MsgBox errorText
       End
   End If
   numberOfFields = UBound(fieldinfo, 2)
   field1Name = fieldinfo(0, 0)
   field1Label = fieldinfo(1, 0)
   field1Type = fieldinfo(2, 0)
   field2Name = fieldinfo(0, 1)
   field2Label = fieldinfo(1, 1)
   field2Type = fieldinfo(2, 1)
   'fieldNName = fieldinfo(0, N - 1)
   'fieldNLabel = fieldinfo(1, N - 1)
   'fieldNType = fieldinfo(2, N - 1)
   
   ar = automationObject.RetrieveData("SELECT Id,name FROM Car_Order__c", False, False, errorText)
   If Not error = Empty Then
       MsgBox errorText
       End
   End If
   'the result table is a 2-dimensional array with the first row as column headers
   NumberOfRows = UBound(ar, 2)
   NumberOfColumns = UBound(ar, 1)
   For Column = 0 To NumberOfColumns
       ColumnName = ar(Column, 0)
       For Row = 1 To NumberOfRows
           CellValue = ar(Column, Row)
       Next Row
   Next Column
   
   'Now let's update some data in Salesforce
   Dim MyArray(1, 3) As Variant
   MyArray(0, 0) = "Id"
   MyArray(1, 0) = "Name"
   MyArray(0, 1) = "a03E0000007igvaIAA"
   MyArray(1, 1) = "MyName"
   MyArray(0, 2) = "a03E0000009HszSIAS"
   MyArray(1, 2) = "It Works!"
   MyArray(0, 3) = "a03E0000007igvbIAA"
   MyArray(1, 3) = "Another Record"
   
   result = automationObject.UpdateData(MyArray, "Car_Order__c", False, Nothing, errorText)
   If Not error = Empty Then
       MsgBox errorText
       End
   End If
   UpdatedRecord1Id = result(0, 0) 'first record Id
   UpdatedRecord2Id = result(0, 1) 'second record Id
   UpdatedRecord3Id = result(0, 2) 'third record Id
   'UpdatedRecordNId = result(0, N - 1) 'Nth record Id
   Record1UpdateResult = result(1, 0) 'first record update status ("OK" or error message)
   Record2UpdateResult = result(1, 1) 'second record update status ("OK" or error message)
   Record3UpdateResult = result(1, 2) 'third record update status ("OK" or error message)
   'RecordNUpdateResult = result(1, N - 1) 'Nth record update status ("OK" or error message)
End Sub