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:
- Click on Developer -> Script Editor in Excel
- 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 RUNQUERY = ar(0, 1) End If End Function
Now 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: