Updating Records via Excel with VBA and Force.com CLI

***Note: Originally posted on adviceforce.com

Recently I found myself manipulating data on an Excel spreadsheet via VBA scripts before I was able to load records into Salesforce. While I was writing these scripts I wondered if I could directly update records to Salesforce from Excel without saving the data into a CSV file and loading the file using a tool like Apex Data Loader. That led me to recall the command-line tool that Salesforce released in 2013 called Force.com CLI. You can download a copy of the tool here. Force.com CLI has features like running SOQL queries, updating records, and pushing metadata through the command-line interface.

To use the the functions of Force.com CLI you would initially need to login through the tool by running the following command:

force loginContact Me

or for sandbox:

force login -i=test


This will open your browser to a Salesforce login window where you can log into your org with username and password.

Once logged in you will be prompted to allow Force.com CLI to access your org via oAuth. Once you have logged in you will be able to run commands against the org using the tool.

How to Update records via Force.com CLI

To see a list of all available commands just run force without specifying a command. Using force help command will return details and Usage details of the commands specified. For example I wanted to learn more about the force record command so I ran force help record.

To update a record in Salesforce you would run the following command:

force record update

where is the API name of the Salesforce object, is the record Id, and is list of  field and field value combos separated by a space.

For instance to update the FirstName and LastName fields of a Contact record the command would look similiar to the following:

force record update Contact 00361000002h0jzAAA FirstName:Scott LastName:Lang

Below is an example of a successful record update command run.

Using Force.com CLI to update an Excel spreadsheet with record data

Now that we understand how to update a record using Force.com CLI, how can we leverage the functionality within Excel? This is where VBA scripts come into picture and the ability to run shell commands within the VB code.

Below is the spreadsheet we will be working with in this exercise. The sheet contains Contact records and contains the Id, Birthdate, Email, FirstName, LastName, and Title fields. There are also two additional columns labeled as “Update Status” and “Command”. We will be writing to these columns to basically store the status of the update transaction along with the command that was run to perform the update (more on this later).

The objective is to be able to make updates to the records in the spreadsheet and load the changes directly to Salesforce. In order to accomplish this we will be creating a short VB Sub procedure to handle traversing through the rows of data in the spreadsheet and compiling the data into a format that Force.com CLI accepts to perform a record update (as defined in the previous section). To do so we will go to the Developer tab in Excel and select the Visual Basic option in the ribbon. This will open an editor window where we can create class files or modules.

The VB Code

We will start off with a procedure like the below.


Sub UpdateSFRecordsByCLI(colSize As Integer, worksheetName As String, sheetRange As String, updStatusColIndex As Integer, cmdColIndex As Integer, pathToForceCli As String, sObjectAPIName As String)

End Sub

Here is an explanation of the arguments passed into the procedure:

• colSize : Number of columns in the spreadsheet that contains record data (excluding Update Status and Command columns).
• worksheetName : Name of the worksheet contain the record data (“Updated Contacts” in our case).
• sheetRange: The range of total data that will need to be accessed (A1:F20 in our case).
• updStatusColIndex : The number index of the position of “Update Status”  column (position 7 in our case).
• cmdColIndex : The number index of the position of the “Command” column (position 8 in our case).
• pathToForceCli : The file path to where the force.exe file is located (“C:force-cli\force.exe” in our case).
• sObjectAPIName : The API Name of the Salesforce object that is being updated (Contact in our case).


Sub UpdateSFRecordsByCLI(colSize As Integer, worksheetName As String, sheetRange As String, updStatusColIndex As Integer, cmdColIndex As Integer, pathToForceCli As String, sObjectAPIName As String)
Dim rng As Range
Dim i As Integer, j As Integer, k As Integer
Dim ColHeaders() As String
ReDim ColHeaders(colSize)

‘Select range with specified worksheet and range of cells
Set rng = Worksheets(worksheetName).Range(sheetRange)

‘Loop through each row of the selected range
For i = 1 To rng.Rows.Count

‘Set the initial force.com cli record update command using the provided sObject api name
Dim sCmd As String
sCmd = pathToForceCli & ” record update ” & sObjectAPIName & ” ”

‘If this is the first (header) row, loop through each column and store column header cell values which should be field api names
If i = 1 Then

The first half of the procedure performs the following:

• Access the worksheet and range of cells specified in the arguments worksheetName and sheetRange.
• Defines a string array (ColHeaders) with the size specified in the colSize integer variable.
• Based on the selected range loop through each row of the sheet.
• Start building the command that needs to be run within the script by assigning the file path defined in pathToForceCli, the command “record update”, and lastly the sObject name defined in sObjectAPIName.
• The final part of the above snippet ends with the If condition checking if the current row is the first row, then looping the columns and adding the field names into the ColHeaders string array. We will be using the array to fetch the field names of each corresponding column with field data.

‘If this is the first (header) row, loop through each column and store column header cell values which should be field api names
If i = 1 Then

For j = 1 To rng.Columns.Count
ColHeaders(j) = rng.Cells(i, j).Value
Next j

‘Otherwise proceed to loop through each column of the data rows
Else:

For k = 1 To rng.Columns.Count
‘If this is the first column (which should be the Id column) append the record Id value to the command string
If k = 1 Then

sCmd = sCmd & rng.Cells(i, k).Value & ” ”

‘Otherwise store each field update value with the combination of :
ElseIf k <> 1 And IsEmpty(rng.Cells(i, k).Value) = False Then
Dim cellVal As String
Dim pos As Integer

cellVal = rng.Cells(i, k).Value
pos = InStr(cellVal, ” “)

‘If boolean value convert true and false values to a format accepted by force.com cli
If rng.Cells(i, k).Value = True Then ‘check if the value is a boolean true
sCmd = sCmd & ColHeaders(k) & “:” & Chr(34) & “true” & Chr(34) & ” ”
ElseIf rng.Cells(i, k).Value = False Then ‘check if the cell value is a boolean false
sCmd = sCmd & ColHeaders(k) & “:” & Chr(34) & “false” & Chr(34) & ” ”
‘If date value convert to YYYY-MM-DD format before adding to command string
ElseIf IsDate(rng.Cells(i, k).Value) Then
sCmd = sCmd & ColHeaders(k) & “:” & Year(rng.Cells(i, k).Value) & “-” & Month(rng.Cells(i, k).Value) & “-” & Day(rng.Cells(i, k).Value) & ” ”
‘If string value has contains white spaces, enclose value in double quotes
ElseIf pos <> 0 Then
sCmd = sCmd & ColHeaders(k) & “:” & Chr(34) & cellVal & Chr(34) & ” ”
‘Otherwise store append field name and field value combo to the command string
Else:
sCmd = sCmd & ColHeaders(k) & “:” & rng.Cells(i, k).Value & ” ”
End If
End If
Next k

The second half of the procedure continues with processing the rows that contain actual record data within the Else condition. Based on the type of data in each column cell value we apply formatting before appending it to the command string. Each field is appended to the command string variable sCmd in the : format. Each field set is separated by a single space. The field name is fetched by accessing the value stored in the ColHeaders string array. Each index column index in variable k should match with the index of the array. The cell value is translated based on the below condition:

• If the cell value is boolean, the value is translated into the string “true” or “false” using Chr(34) function to enclose the value in double quotes.
• If the cell value is of type Date, change the value to YYYY-MM-DD format.
• If the cell value contains spaces enclose the value with double quotes using Chr(34). I found that the tool will not recognize field values with spaces as one value without enclosing within quotes.
• Note that the code assumes that the Id field is the first column of the spreadsheet.


Dim oShell As Object
Set oShell = CreateObject("WScript.Shell")

Dim oExec As Object
Dim oOutput As Object

‘store generated command string to the command column cell
rng.Cells(i, cmdColIndex).Value = sCmd

‘execute force.com cli command
Set oExec = oShell.Exec(sCmd)

‘Store stdout and stderr stream
Set oOutput = oExec.Stdout
Set oError = oExec.StdErr

Dim sLine As String, errLine As String

‘Loop through stdout stream and store value to the Update Status column for current row
While Not oOutput.AtEndOfStream
sLine = oOutput.ReadLine
Dim statusCurrVal As String
statusCurrVal = rng.Cells(i, updStatusColIndex).Value
statusCurrVal = statusCurrVal & ” | ” & sLine
rng.Cells(i, updStatusColIndex).Value = statusCurrVal
Wend

‘Loop through any error returned by force.com cli in stderr stream and store in the udpate status column for current row
While Not oError.AtEndOfStream
errLine = oError.ReadLine
Dim statCurrVal As String
statCurrVal = statCurrVal & ” | ” & errLine
rng.Cells(i, updStatusColIndex).Value = statCurrVal
Wend

End If

Next i

End Sub

The third half of the procedure will take the command string generated by looping through the columns of the current row in the outer for loop and running the Force.com CLI command. This is achieved by  running the Exec method of the WshShell object and passing in the command string sCmd as an argument. Here is a breakdown of what occurs in this code snippet.

• The generated command string is stored in the “Command” column cell of the current row using the current row index and the cmdColIndex value. This is so we have a record of the command that was run for the row.
• The Exec method is called with sCmd passed as an argument. This will run the force command that we have generated. The Exec method return a  WshScriptExec object, which we will use to capture any outputs to stdout or stderr for any success or error messages returned after the command is finished running.
• Using the returned WshScriptExec object, we store the Stdout and Stderr streams to oOutput and oError respectively.
• We loop through any output in oOutput and oError and read each line and store any output into the “Update Status” column using the current row index and the value provided in updStatusColIndex.

The code will continue to loop through each row specified in the selected range and perform an update to Salesforce for each row.

Running the Code

So now that we have the procedure built, how can we test it? We can do so by calling the procedure and providing the required arguments. For the spreadsheet we are using for our scenario, let’s say I have updated fields in the first four rows.

From the Visual Basic code editor window we can create a new class module and call the sub procedure.


Call UpdateSFRecordsByCLI(6, "Updated Contacts", "A1:F5", 7, 8, "C:force-cli\force.exe", "Contact")


• I am passing 6 as the first argument specify there are 6 actual field columns (columns A-F).
• The second argument is the string “Updated Contacts” which is the name of the worksheet where the data is contained.
• The third argument is the range which will be A1 to F5 since I am updating the first four rows of data. I would specify A1 to F20 if I wanted to run an update on all rows.
• The fourth argument is the integer value of 7 indicating the “Update Status” column position index where results of the update will be stored for each row.
• The fifth argument is the integer value of 8, indicating the “Command” column position index where the command string will be stored for each row.

 

After the each row has been updated, the “Update Status” column and “Command” columns will be populated with the results of the update and the command string respectively. The sub procedure is re-usable to update any SObject records from a spreadsheet. You will need to import it to the workbook as module. For each case you can call the UpdateSFRecordsByCLI procedure and change the arguments to fit your update scenario.

I created a Github project for this and I will continue to explore ways to also create and query records from Salesforce from Excel and Force.com CLI. I will add any results to the repository and post my findings as well so please stay tuned by following me on twitter here or here.