Get Started Free 
In this article we wanted to show you a little VBA trick that will help you run SOQL queries in Salesforce to populate a single cell in your spreadsheet using a formula that looks as follows:
=RUNQUERY(“SELECT id FROM opportunity WHERE name =’”&B1&”‘”)
In order to achieve this, you’ll need to create a custom formula in your VBA editor as follows:
-
1
Click on Developer -> Script Editor in Excel
-
2
Create the following function in VBA editor:
Public Function RUNQUERY(query As String)
Dim addin As Office.COMAddIn
Dim automationObject As Object
Set automationObject = Application.COMAddIns("TaralexLLC.SalesforceEnabler").Object
ar = automationObject.RetrieveData(query, False, False, errorText)
If Not errorText = Empty Then
RUNQUERY = errorText
Else
If UBound(ar, 2) > 0 Then
RUNQUERY = ar(0, 1)
Else
RUNQUERY = ""
End If
End If
End FunctionNow you can use this custom formula run any SOQL query for a single cell. Please, keep in mind that only the value in the first row of the first column will be returned as the result of this formula.
If a query is written correctly, you should see something like the following in the cell:
Now if you want this macro to appear in all documents that you open on this computer click on File -> Save As and save the document as an Excel Add-In:
