Chapter 9. Table Views

Chapter 9. Table Views

Users often apply different filters to loaded data, hide and unhide columns, sort in various ways, etc.

The SaveToDB add-in can help to save such user views and even share them with colleagues.

Let's remove all WHERE filters and click the Save Table View button in the Table Views group:

Table Views of the SaveToDB add-in

Type All Payments and click Save.

Save a view in Microsoft Excel


We see the name of the current view, All Payments, in the Table View field.

Type >0 in cell E2.

Sample of cell filters

The add-in applies the filter to the Sum column.

This is a reason why it is better to insert tables at cell B3.

Users can use row 2 (as a row over the table) as auto-filters. Also, they can place formulas in row 1.

Let's continue and save the view as Incomes (click the Save Table View button again):

Save the Incomes view


Type <0 in cell E2.

Sample of a negative cell filter

The add-in applies the new filter to the Sum column.

Save the view as Expenses.

Save the Expenses view


Remove the filter in cell E2 and apply the Incomes view:

Samples of the applied Incomes view

As we can expect, the add-in applies the saved filter to the Sum column.


I am sure, your users will be happy, and you will have fewer requests for new small database views.

Creating table views using VBA

The following code creates table views described in the chapter:

' Creating table views, Chapter 9

Sub Chapter09_1_CreateTableViews()

    Dim addIn As Object
    Set addIn = GetAddInAndCheck()

    If addIn Is Nothing Then Exit Sub

    Dim ws As Worksheet
    Set ws = GetWorksheet(ActiveWorkbook, "Payments")

    If ws Is Nothing Then
        MsgBox "Worksheet Payments not exists"
        Exit Sub
    End If


    Dim lo As ListObject
    Set lo = GetFirstListObject(ws)

    If lo Is Nothing Then
        MsgBox "Worksheet Payments does not contain a table"
        Exit Sub
    End If

    Dim cell As Range
    Set cell = ws.Range("E2")

    Call addIn.SaveTableView(lo, "All Payments")

    cell.Value = ">0"

    Call addIn.SaveTableView(lo, "Incomes")

    cell.Value = "<0"

    Call addIn.SaveTableView(lo, "Expenses")


    Call addIn.ApplyTableView(lo, "Incomes")

End Sub

Mainly, you will use the SaveTableView and ApplyTableView methods.

Also, you can use the auto-filter row over the tables to filter data in an easy way.