Turbocharging Excel

Posted: 2023-12-17

Aka: Fast data retrieval from a database.

In a lot of enterprises that Process IQ had a pleasure to work with, there is always an Excel spreadsheet that implements a certain business process. One of the more painful aspects of Excel is how the data is getting there. There are few ways: - Manual data entry (🤷 although it can be pretty easy especially with built-in data validation) - Linking a worksheet to another worksheet on the shared drive (👎 ugly, evil and hideous) - Pulling the data from a database (👍👊👊)

We are big fans of bringing in the data via a database query. This helps to maintain data quality, perform audits or other regulatory reporting and later restructure the process to use more streamlined UIs or even move the process / application to the cloud.

There are few ways to query the database from Excel, but the one that is fairly fast is to use WorkbookQuery. The code below shows how to do it from a VBA level, which allows for additional parametrization of SQL.

WorkbookQuery is a relatively cheap trick that can help to restructure spreadsheets that may contain even few hundred thousand records (yes we have seen such monsters 🤕). And the nice thing is reduction of data load times. With cloud-hosted database, loading 100,000 records takes only 15 seconds now - way faster than opening the spreadsheet from a slow remote drive.

Here is VBA for those who are interested in

Private Sub btnRefresh_Click()
On Error GoTo ErrorHandler

    Dim wq = Me.Parent.Queries("Query1")
    wq.Formula = "let Source = Odbc.Query(""dsn=" & dsn & """, """ & sql & """) in Source"
   
    Dim conn As WorkbookConnection
    Set conn = lo.QueryTable.WorkbookConnection
    lo.QueryTable.BackgroundQuery = False
    conn.Refresh

    Exit Sub
    
ErrorHandler:
    MsgBox Err.Description + " at " + Err.Source, Error.vbExclamation
End Sub

Image by Herbert Aust from Pixabay