Getting Salesforce reports with VBA - Phelipe Teles

Getting Salesforce reports with VBA

9 min.
View source code
salesforcevbaexcel...

For those who find themselves in an environment which heavily relies on Excel and Salesforce, you may be interested in a way to automate the process of downloading reports from inside Excel with VBA only.

I think there are probably better tools for the job, like Apex, SOQL query or a better programming language. I created a Python package for this purpose but I didn’t use it so much because it’s hard to integrate with Excel.

Instead, I looked into a way to do something similar with VBA and I managed to do it. In this post I’ll share this with you.

Authentication

The more painless way I know of to authenticate your requests for a Salesforce web service is via SOAP API, with username, password and a security token.

It returns a bunch of XML in the response, but we will only need the session id (a JWT) inside of it. This is what the function below does.

From here onwards, we will need to authenticate every request by passing the header Authorization: Bearer $sessionId.

vb
Function SalesforceLogin(Username As String, _                         Password As String, _                         SecurityToken As String) As String     Dim Request As Object    Set Request = CreateObject("MSXML2.XMLHTTP.6.0")     Dim XMLBody As Object    Dim XMLResponse As Object     Set XMLBody = CreateObject("MSXML2.DOMDocument.6.0")    Set XMLResponse = CreateObject("MSXML2.DOMDocument.6.0")     Dim Url As String    Dim Body As String    Dim Response As String     Url = "https://login.salesforce.com/services/Soap/u/47.0"     Body = "<?xml version=""1.0"" encoding=""utf-8"" ?>" & vbNewLine & _           "<env:Envelope xmlns:xsd=""http://www.w3.org/2001/XMLSchema""" & vbNewLine & _           "    xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""" & vbNewLine & _           "    xmlns:env=""http://schemas.xmlsoap.org/soap/envelope/"">" & vbNewLine & _           "  <env:Body>" & vbNewLine & _           "    <n1:login xmlns:n1=""urn:partner.soap.sforce.com"">" & vbNewLine & _           "      <n1:username>" & Username & "</n1:username>" & vbNewLine & _           "      <n1:password>" & Password & SecurityToken & "</n1:password>" & vbNewLine & _           "    </n1:login>" & vbNewLine & _           "  </env:Body>" & vbNewLine & _           "</env:Envelope>"     XMLBody.LoadXML Body     Request.Open "POST", Url, False    Request.setRequestHeader "Content-Type", "text/xml"    Request.setRequestHeader "SOAPAction", "login"    Request.send XMLBody.XML    Response = Request.responseText     XMLResponse.LoadXML Response    XMLResponse.setProperty "SelectionNamespaces", "xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:urn=""urn:partner.soap.sforce.com"""     Dim SessionId As String    SessionId = XMLResponse.SelectSingleNode("//urn:result/urn:sessionId").Text     SalesforceLogin = SessionId     Set Request = Nothing End Function

Parsing JSON inside VBA?

Now, to get an actual report inside Excel we will need to use the Analytics API. So far, we didn’t had to rely on any external tools, there is an XML parser inside VBA, but not a JSON parser at least that I know of. So we’re in trouble here because that’s what Analytics speaks.

Fortunately, there is a JSON parser implementation for VBA which works flawlessly. You just need to download this file and import it as a module.

Unfortunate API limitations

This API unfortunately have a critical limitation, which is to return only a maximum of 2000 rows per report. Also, there is no way to filter by row limits.

This almost turns it useless. The only way I know of is to use a column which has only unique values and exclude already seen values with a filter, which is what we’ll gonna do.

Getting report metadata

To be able to filter a report, we will need to fetch its metadata, which is a huge JSON with key-value pairs describing the report.

We can get it with a GET request to https://$YOUR_INSTANCE_URL/services/data/v47.0/analytics/reports/$REPORT_ID/describe.

vb
Function GetMetadata(ReportId As String, SessionId As String) As String    Dim Request As Object    Set Request = CreateObject("MSXML2.XMLHTTP.6.0")    Dim Response As String    Dim Url As String     ' NOTE: You will need your organization URL here    Url =  YOUR_INSTANCE_URL & "/services/data/v47.0/analytics/reports/" & ReportId & "/describe"     Request.Open "GET", Url, False    Request.setRequestHeader "Authorization", "Bearer " & SessionId    Request.send     GetMetadata = Request.responseTextEnd Function

Getting an individual report

With the function below, you will be able to get the report in JSON format.

This is achieved with a POST request to https://$YOUR_INSTANCE_URL/services/data/v47.0/analytics/reports/$REPORT_ID, optional metadata goes into the request body, this is what we use to filter the report.

vb
Function GetReport(ReportId As String, _                   SessionId As String, _                   Optional Metadata As String = "") As String     Dim Request As Object    Set Request = CreateObject("MSXML2.XMLHTTP.6.0")     Dim Response As String    Dim Url As String     Url = YOUR_INSTANCE_URL & _          "services/data/v47.0/analytics/reports/" & ReportId     Request.Open "POST", Url, False    Request.setRequestHeader "Authorization", "Bearer " & SessionId    Request.setRequestHeader "Content-Type", "application/json"     If Metadata <> "" Then        Request.send (Metadata)    Else        Request.send    End If     Response = Request.responseText     GetReport = ResponseEnd Function

Writing the data into a worksheet

Now we need to extract the data and write it into a worksheet.

Every thing we need is inside the factMap key. This can get complex if we intend to cover matrix and summary reports, which has groupings etc., but tabular reports are much simpler.

What we need to do is to iterate through the values at factMap.T!T.rows and, for each row, get every value inside .dataCells array.

This approach will only cover tabular reports, you can take a look at the reportforce source code if you need to cover these. By the way, there is an option to export to an Excel (but not to write to a worksheet, obviously).

The below function takes care of this, its job is to go through every cell and store it in an array, and then append that array to a worksheet range starting from column A.

vb
Function WriteIntoWorksheet(Report As Dictionary, _                            WorksheetName As String, _                            ColumnLabels As Variant) As Variant     Dim Rows As Object    Dim Columns As Object    Dim ColumnInfo     Set Columns = Report("reportMetadata")("detailColumns")    Set ColumnInfo = Report("reportExtendedMetadata")("detailColumnInfo")    Set Rows = Report("factMap")("T!T")("rows")     Dim Total_Rows As Long: Total_Rows = Rows.Count    Dim Total_Columns As Long: Total_Columns = Columns.Count     If Total_Rows = 0 Then        Exit Function    End If     Dim Table() As Variant    ReDim Table(Total_Rows - 1, Total_Columns - 1)     Dim row As Variant    Dim cell As Variant     Dim i As Long: i = 0    Dim j As Long    Dim dataType As String     For Each row In Rows        j = 0        For Each cell In row("dataCells")            dataType = ColumnInfo(Columns(j + 1))("dataType")             ' If column is a date, get value property;            ' Excel understands it better            If dataType = "date" Then                Table(i, j) = cell("value")            Else                Table(i, j) = cell("label")            End If             j = j + 1        Next cell        i = i + 1    Next row     With ThisWorkbook.Worksheets(WorksheetName)        Dim LastRow As Long        LastRow = .Range("A:A").Find("*", _                                     SearchOrder:=xlByRows, _                                     SearchDirection:=xlPrevious).row         .Range(.Cells(LastRow + 1, "A"), _               .Cells(Total_Rows + LastRow, Total_Columns)) = Table    End With     WriteIntoWorksheet = Table End Function

We also need to get the values at .reportMetadata.detailColumns and reportExtendedMetadata.detailColumnInfo to check how many columns the report have and to extract the value at .value if it is a date.

Getting the entire report

To get the entire report, the key thing to overcome the API limitations is a key called .allData, which will be true only if we got all the data from the API.

So we get the first 2000 rows and, if .allData is false, we get the values of an identifier column, filter them out and request a new report, until we get all data.

We will modify the keys .reportMetadata.standardDateFilter and .reportMetadata.reportFilters, to filter dates and other filters respectively.

If your report has a boolean filter, it’s likely that you’ll need to change it because we will insert new filters. This is already done in the code by changing the value at .reportMetadata.reportBooleanFilter, so you only need to pass it as a parameter.

The function looks way more involved because we have to get the report headers (the label property of each object inside .reportExtendedMetadata.detailColumnInfo, the ones you see in the browser).

We also need the identifier column API name (an internal value), which we will need to filter it.

vb
Sub DownloadEntireReport(ReportId As String, _                         WorksheetName As String, _                         SessionId As String, _                         Optional IdentifierColumn As String = "", _                         Optional BooleanFilter As String = "", _                         Optional startDate As String = "", _                         Optional endDate As String = "")     Dim Report As Object     Dim ReportMetadata As Object    Set ReportMetadata = JsonConverter.ParseJson(GetMetadata(ReportId, SessionId))     Dim IdentifierColumnApiName As String    Dim IdentifierColumnPosition As Long     Dim Col As Variant    Dim i As Long: i = 0     Dim ColumnDetails As Object    Set ColumnDetails = ReportMetadata("reportExtendedMetadata")("detailColumnInfo")     ' This is an array to store the column headers    Dim ColumnLabels() As Variant    ReDim ColumnLabels(0, ColumnDetails.Count - 1)     ' Collection the column headers    For Each Col In ColumnDetails.Keys()        ColumnLabels(0, i) = ColumnDetails(Col)("label")         ' Remember which column matches the identifier column label        If ColumnDetails(Col)("label") = IdentifierColumn Then            IdentifierColumnApiName = Col            IdentifierColumnPosition = i        End If         i = i + 1    Next Col     ' Write column headers starting from A1    With ThisWorkbook.Worksheets(WorksheetName)        .Range(.Cells(1, "A"), .Cells(1, ColumnDetails.Count)) = ColumnLabels    End With     Dim ReportMetadataJson As String: ReportMetadataJson = ""     ' Setting date filters, if any    If startDate <> "" And endDate <> "" Then        ReportMetadata("reportMetadata")("standardDateFilter")("durationValue") = "CUSTOM"        ReportMetadata("reportMetadata")("standardDateFilter")("startDate") = Format(startDate, "yyyy-mm-dd")        ReportMetadata("reportMetadata")("standardDateFilter")("endDate") = Format(endDate, "yyyy-mm-dd")         ReportMetadataJson = JsonConverter.ConvertToJson(ReportMetadata)    End If     ' Get first 2000 rows    Set Report = JsonConverter.ParseJson(GetReport(ReportId, SessionId, ReportMetadataJson))     Dim ReportTable As Variant    ReportTable = WriteIntoWorksheet(Report, WorksheetName, ColumnLabels)     ' Getting Remaining Values By Filtering Out Old Values     If BooleanFilter <> "" Then        ReportMetadata("reportMetadata")("reportBooleanFilter") = BooleanFilter    End If     Dim IdentifierColumnValues    Dim IdentifierColumnFilter As String: IdentifierColumnFilter = ""     Dim Filters As Scripting.Dictionary    Set Filters = New Dictionary     Dim ReportFilters As Object     Do Until Report("allData")        IdentifierColumnValues = GetValuesAtColumn(ReportTable, IdentifierColumnPosition)        IdentifierColumnFilter = IdentifierColumnFilter & _                                 Join(Application.Transpose(IdentifierColumnValues), ",")         Filters.RemoveAll        Filters.Add "filterType", "fieldValue"        Filters.Add "isRunPageEditable", True        Filters.Add "column", IdentifierColumnApiName        Filters.Add "operator", "notEqual"        Filters.Add "value", IdentifierColumnFilter         Set ReportFilters = ReportMetadata("reportMetadata")("reportFilters")         ' If IdentifierColumn was already added, just change the filter value        If ReportFilters(ReportFilters.Count)("column") = IdentifierColumn Then            ReportFilters(ReportFilters.Count)("value") = IdentifierColumnFilter        Else            ReportFilters.Add Filters        End If         ReportMetadataJson = JsonConverter.ConvertToJson(ReportMetadata)         Set Report = JsonConverter.ParseJson(GetReport(ReportId, SessionId, ReportMetadataJson))         ReportTable = WriteIntoWorksheet(Report, WorksheetName, "")    Loop End Sub

To do the filter, we create a Filters dictionary to store all key-value pairs we need, then add it to the reportFilters object, and change its value in later iterations. Then we convert the metadata into a JSON string, get a new filtered report and repeat the loop until we get all data.

Also notice that I use a custom function to get all values at a given column, GetValuesAtColumn.

Get the full source code in this GitHub gist.

How to use it

It’s up to you how you’re gonna use this code. Here’s an example.

vb
Sub DownloadReports   Dim Username As String, Password As String, SecurityToken As String   Username = "username"  Password = "password"  SecurityToken = "secret"   Dim SessionId As String  SessionId = SalesforceLogin(Username, Password, SecurityToken)   If IsEmpty(SessionId) Then      MsgBox "Authentication Error"      Exit Sub  End If   Dim ReportId As String: ReportId = "REPORT_ID"  Dim WorksheetName As String: WorksheetName = "MY_REPORT"   Call DownloadEntireReport(ReportId, WorksheetName, SessionId, _                            IdentifierColumn:="Número do caso", _                            BooleanFilter:="1 AND 2 AND (3 OR 4)", _                            startDate:="01/07/2020", _                            endDate:="31/07/2020") End Sub

Final words

You probably have a better way to do it, I would only recommend this approach to someone in an environment very dependent on Excel, simply because it is super convenient. You can put the report anywhere you want with zero overhead — no need for a library to understand the complexity of an Excel archive.

And I gotta say, VBA is kind of a hard, its ecosystem is not great, obviously. But I was very impressed by what it can do, despite of its shortcomings. Nonetheless, it was still fun to write this.