Querying Salesforce Records Via Excel Using Force.com CLI

Earlier this month I wrote a blog post on how to use the Force.com CLI tool to update records directly from an Excel spreadsheet via VBA. As a follow-up I will present a method to run queries from Excel via VBA to query records and load the query results into a spreadsheet.

In order to fetch record data we will be using the “query” command of the Force.com CLI tool. The command will execute a SOQL statement and will output the results. Note that you will have to log into your Salesforce org before you can run other commands. Review the beginning of my previous post for a quick walk through.

From the above screen shot we can see that the query results are returned as a table in the output. The first row contained field names as column headers. The second row separates the header row and the remaining data rows. Each table cell is delimited by the ‘|’ character. So how do we transfer that results in the output to the spreadsheet? Just like the previous post we will create an Excel VBA script.
The Script

We will start with an empty sub procedure.

Sub GetRecords(queryStr As String, wkSheetName As String, pathToForceCli As String)

End Sub

The procedure will accept three arguments:

• queryStr : The SOQL query to be executed.
• wkSheetName : The name of the worksheet where we want the data to be loaded.
• pathToForceCli : The file path to the Force.com CLI executable.

Sub GetRecords(queryStr As String, wkSheetName As String, pathToForceCli As String)

Dim sCmd As String
sCmd = pathToForceCli & " query " & Chr(34) & queryStr & Chr(34)

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

Dim oExec As Object
Dim oOutput As Object

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

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

Dim lineCnt As Integer, rowCnt As Integer
lineCnt = 1
rowCnt = 1

.......................................................................

The first half of the code constructs the command string using the file path provided in the pathToForceCli string variable and the SOQL query provided in the queryStr variable. The command is executed and the output from stdout and stderr is redirected to oOutput and oError object variables. The variable lineCnt will be used to track the line being read from stdout output and rowCnt will used to write the data onto the rows of the specified spreadsheet.

So how do we translate the output and load them onto the spreadsheet? We will loop through each line of the output and process the data returned from running the command. From the above example you can see that each table cell is separated by the ‘|’ character. Using the Split function we will store each cell value into a string array for each line of the output. The example above also shows that the second line divides the header row and the data rows so we have an IF condition in the output while loop that will only process the line if lineCnt does not equal to 2 (indicating that it is the second line).

...........................................................

While Not oOutput.AtEndOfStream
sLine = oOutput.ReadLine
Dim colCnt As Integer
'MsgBox sLine
'MsgBox lineCnt
If lineCnt 2 Then
Dim splitVals() As String
splitVals = Split(sLine, "|")
'MsgBox splitVals(1) & " " & splitVals(2)
'MsgBox splitVals(0)
For colCnt = 0 To UBound(splitVals)
'MsgBox splitVals(colCnt)
Worksheets(wkSheetName).Cells(rowCnt, colCnt + 1).Value = splitVals(colCnt)
Next colCnt
'iterate rowCnt to the next row
rowCnt = rowCnt + 1
End If

lineCnt = lineCnt + 1
Wend

End Sub

For each split value stored in the string array, we will loop through each array index and store the value into the corresponding cell in the spreadsheet. The rowCnt and colCnt integer values will correspond to the specific row and cell index of the spreadsheet. We add colCnt by 1 when specifying the specific index for the column since we start colCnt at zero (0), but the first index for the spreadsheet starts at one (1).
Running the Code

To run the code we will call the procedure and pass the SOQL query we want to run, the name of the worksheet we want the query results to be stored, and the file path to the Force.com CLI tool as arguments respectively.

Call GetRecords("Select Id,FirstName,LastName,Email,Title From Contact Limit 10", "ContactRecords", "C:\force-cli\force.exe")

Running the above sub procedure will result in the below output to the specified worksheet.

With the above short sub procedure you can export data directly into an Excel spread sheet without needing to save the data in a csv format beforehand. I have also included the script in the Github project I created for the previous post on updating records. Hope you found this useful. Any feedback can be provided in the comments below or via twitter @tc_clouddev or @adviceforce.