VBA Interface

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  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 function:

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

If you will be calling the plug-in functions from multiple places in your code, it’s a good practice to have a global variable that is only assigned once and then access it by calling a function:

Global automationObject As Object
Function GetXLC() As Object
    If automationObject Is Nothing Then
        Set automationObject = Application.COMAddIns("TaralexLLC.SalesforceEnabler").Object
    End If
    Set GetXLC = automationObject
End Function

Before you begin writing code…

• The easiest way to automate is to use VBA to run Flow Steps that have been saved in your worksheet(s).
• When you’re working with queries, remember it’s only SOQL syntax, not full SQL. Use XL-Connector’s query-builder window to test any query string that you have.
• 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:

Get current plug-in version

Version

Example:

Set automationObject = Application.COMAddIns("TaralexLLC.SalesforceEnabler").Object
res = automationObject.Version
MsgBox (res)

Log in to Salesforce.com

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

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

Example:

Public Function testLogin() 
  Dim automationObject As Object 
  Set automationObject = Application.COMAddIns("TaralexLLC.SalesforceEnabler").Object
  Result = automationObject.LogIn("test@xappex.com", "PasswordSecurityToken", "https://login.salesforce.com", Error)
End Function

Log in to Salesforce.com using a previously saved login (by alias)

LogInWithAlias(alias, errorText)
Returns true or false, in case of false – examine the error text

Example:

Public Function testLoginWithAlias() 
  Dim automationObject As Object 
  Set automationObject = Application.COMAddIns("TaralexLLC.SalesforceEnabler").Object
  Result = automationObject.LogInWithAlias("Xappex Dev", Error)
End Function

Show the Web and SSO Login Window

ShowSSOLogin([optional] url)

If the optional url parameter is passed – the window will open and automatically go to that url. Use it to specify your custom SSO page address, for example https://myDomain.my.salesforce.com

Log out of SFDC instance

LogOut ()

Returns true or false

Check if the user is currently logged in to Salesforce

IsLoggedIn ()

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 the login name of the currently logged in user
string GetLoggedInUserName()

Get the Salesforce Id of the currently logged in user
string GetLoggedInUserId()

Get the Salesforce Profile Id of the currently logged in user
string GetLoggedInUserProfileId()

Get the Salesforce UserRole Id of the currently logged in user
string GetLoggedInUserRoleId()

Get all the objects in the system

string[,] GetObjects(errorText))

Get all the field names from an object

string[,] GetObjectFields(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(variant data, string objName, bool useAssgnRule, string assignmentRuleId, bool ignoreNativeDuplicateChecks, out string errorText)

The data parameter can be either a 2-dimensional array (see samples below), or a Range object. , there must be a flow step with correct mappings present in the sheet.

Insert records

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

The data parameter can be either a 2-dimensional array (see samples below), or a Range object. If you choose to use the range, , there must be a flow step with correct mappings present in the sheet.

Upsert records

string[,] UpsertData(variant data, string objName, string extIdColumn, bool useAssgnRule, string assignmentRuleId,bool ignoreNativeDuplicateChecks, out string errorText)

The data parameter can be either a 2-dimensional array (see samples below), or a Range object. If you choose to use the range, there must be a flow step with correct mappings present in the sheet

Delete records

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

The data parameter can be either an array of Ids, or a Range object. If you choose to use the range, there must be a flow step with correct mappings present in the sheet.

Example:

Public Function testDelete() 
  Dim xlObj As Object 
  Set xlObj = Application.COMAddIns("TaralexLLC.SalesforceEnabler").Object
  Dim xlResult As Variant 
  Dim xlArr() As Variant 
  Dim xlErr As String 
  Dim listObj As ListObject 
  Dim listCol As ListColumn 
  Set listObj = Range("Opportunities").ListObject 'Excel table named "Opportunities"
  Set listCol = listObj.ListColumns("Id") '"Id" column from that table
  Dim i As Long Dim rowCount As Long
  rowCount = listCol.DataBodyRange.Rows.Count 
  If (rowCount = 0) Then Return 
  ReDim xlArr(0 To rowCount - 1) As Variant 
  For i = 1 To rowCount 
    xlArr(i - 1) = listCol.DataBodyRange.Cells(i, 1).value
    Next i 
  xlResult = xlObj.DeleteData(xlArr, xlErr) 
End Function

Pull report from SFDC by Id – returns a path to the saved CSV file. Please note that it does not put it into the open Excel worksheet. You will need to read the file, parse the CSV file and process the data with your own VBA code. If you want to trigger a report refresh from VBA, pull it manually first and then use the Refresh VBA command to repopulate the report data in your spreadsheet.

string RunReport(string id, out string errorText)

Refresh the data in the active worksheet

string Refresh(False)

Returns an empty string in case of success, otherwise – error text.

Refresh the data all worksheets

string Refresh(True)

Returns an empty string in case of success, otherwise – error text. Example:

Sub test()
   Dim automationObject As Object
   Set automationObject = Application.COMAddIns("Salesforce Enabler for Excel").Object
   error_msg = automationObject.Refresh(True)
End Sub

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.
Example:

Dim automationObject As Object 
Set automationObject = Application.COMAddIns("TaralexLLC.SalesforceEnabler").Object
run_result = automationObject.RunFlow(Worksheets("Sheet1"), errorText)
if not run_result then
   msgbox (errorText)
end if

Run all Flows in the active workbook

bool RunAllFlows(out error)

Run a single Flow Step 

bool RunFlowStep(Worksheet ws, string flowStepName, out string errorText)

This function will run the specified Flow Step in the specified worksheet. If there’s an error, the function will return False and the error text will be in the ‘errorText’ output variable.

Example:

Dim automationObject As Object 
Set automationObject = Application.COMAddIns("TaralexLLC.SalesforceEnabler").Object
run_result = automationObject.RunFlowStep(Worksheets("Sheet1"), "MyFlowStep", errorText)
if not run_result then
msgbox (errorText)
end if

Run anonymous Apex code in Salesforce:

string RunApexCode(string apexCode)

This function will run the specified Apex code in Salesforce and return the result. ‘success’ is returned if the code ran successfully, otherwise – you’ll get the error description.

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, false, 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