Salesforce reporting: XL-Connector and VBA

In an earlier post, I mentioned a tool I'm using to import Salesforce data - via SOQL or existing reports - into Excel. This post is more about using that tool, XL-Connector from Xappex. Here, I'll walk through the (simple) process of importing and refreshing a report, and I'll provide a simple VBA macro to automate the refresh. In a future post, I'll expand on that macro to show a friendly view of my opportunity pipeline and a single-page view of how each of my sales reps are doing against a series of KPIs.

Importing a report is simple enough. From the XL-Connector tab, select Log In and enter your credentials. I'm using the old id and password (as opposed to SSO), so I provide that along with my 'token'.  (Don't remember your token? Log in to Salesforce via your browser, click on your photo, select Settings, then 'reset my security token'.)

Once you're logged in the lock turns green. Back in Excel, on the XL-Connector tab, select Reports. Because you're logged in with your own Salesforce credentials, the connector will present you with the reports and folders that you have access to in Salesforce. Select the report of interest and click Run Reports. The raw data from your report will be pulled into your Excel file. For me, this is already a win vs my old process of exporting the report details, then switching to Excel and opening the file from my downloads folder.

But the bigger win comes when I start to automate this. Unfortunately, you can't simply use the macro recorder. You can try, but it comes up empty. You can also read Xappex' blog post about using VBA with XL-Connector.

For my purposes, however, I'm only using two of the VBA calls they outline: login and refresh. That leaves me a really simple macro:

Option Explicit
Public automationObject As Object
Dim errorText As String
Dim result As Boolean

Sub SFDCLogin()
    Set automationObject = Application.COMAddIns("TaralexLLC.SalesforceEnabler").Object
    result = automationObject.Login("[UserId]", "[PswdToken]", "https://login.salesforce.com", errorText)
     If result = False Then
       MsgBox errorText
       End
    End If
End Sub

Sub RefreshSheet()
    automationObject.Refresh (False)
End Sub

Sub LoginAndRefresh()
    Call SFDCLogin
    Call RefreshSheet
End Sub

I could save that macro with the Excel file into which I pulled the report. In practice, I have that macro in a file of its own. Then I can easily reuse it, and I only have one file in which to update my password and token. It also allows me to distribute the updated report without setting of security warnings about embedded macros.

Another benefit of using XL-Connector to import and refresh a report is that the data is pulled into Excel as a table. That offers a number of benefits. Two at the top of my list: (1) any formulas I have in adjacent columns automatically get updated, and (2) the table range definition changes to adapt to a changing number of rows in my report; that's especially handy when I use the table data as the source of a pivot table. But that's a future post...