Using Custom Formulas to Dynamically Pull Data From Salesforce

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