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 may 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 may use row 2 (as a row over the table) as auto-filters. Also, they may 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 may 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

    ws.Select

    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")

    cell.ClearContents

    Call addIn.ApplyTableView(lo, "Incomes")

End Sub

Mainly, you will use the SaveTableView and ApplyTableView methods.

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

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.OK