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