Using Custom Formulas to Dynamically Pull Data From Salesforce

Basic features

Admin features



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

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: