Chapter 2. Excel as Table Editor

Chapter 2. Excel as Table Editor

Let's create a new workbook and save it as payments.xlsx.

Then let's run the Data Connect Wizard and connect to the Companies table.

Data Connection Wizard

Follow wizard steps. At the following screen, uncheck Enable Query List on the ribbon.

Select an object to connect

 

Insert a table at cell B3.

Insert a table

We have the following result:

The first connected table in Excel

We can edit data, add and delete rows. Then just click the Save button to save changes.

Creating tables using VBA

Here is a typical code to insert a connected table:

' Inserts the dbo67.Companies table to range B3
' The code demonstrates the simplest way to insert a connected table
' The dummy parameter is used to prevent showing the procedure in a macro list

Sub Chapter02_1_InsertConnectedListObject(ByVal dummy As Boolean)

    Dim connString As String

    connString = "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=Test"

    InsertConnectedListObject Nothing, Range("B3"), connString, "dbo67.Companies", "TABLE"

End Sub

Here is a function the actually inserts a ListObject table using the SaveToDB InsertConnectedListObject method:

' Inserts a connected ListObject object at the specified range

Function InsertConnectedListObject(ByVal addIn As Object, ByVal r As Range,
    ByVal connString As String, ByVal commandText As String, ByVal objectType As String,
    Optional queryListLocked As Boolean = False) As Boolean

    If addIn Is Nothing Then Set addIn = GetAddIn()
    If addIn Is Nothing Then Exit Function

    InsertConnectedListObject = addIn.InsertConnectedListObject(r, _            connString, commandText, objectType)

    If Not InsertConnectedListObject Then
        Debug.Print addIn.LastResultMessage
        Exit Function
    End If

    If Not queryListLocked Then Exit Function

    Dim lo As ListObject
    Set lo = r.ListObject

    addIn.QueryLocked(lo) = queryListLocked

End Function

In the beginning, we get a SaveToDB add-in variable and then call its methods.

In the end, we get a variable for the created table and change the QueryLocked property.

The following code is used to get the SaveToDB add-in variable:

' Returns the SaveToDB addin object

Private AddInObject As Object

Function GetAddIn() As Object

    On Error Resume Next

    If AddInObject Is Nothing Then

        Dim addIn As COMAddIn
        Set addIn = Application.COMAddIns("SaveToDB")
        
        If addIn Is Nothing Then Exit Function
            
        Set AddInObject = addIn.Object

    End If

    Set GetAddIn = AddInObject

End Function

You can use it like Call GetAddIn().MethodName(parameters) or like GetAddIn().PropertyName = value.

Save and Load using VBA

You can use the following code to reload ListObject table data. This is one add-in method only, Load:

' Reloads the ListObject data from a database

Function LoadListObject(ByVal lo As ListObject, ByVal reloadConfiguraton As Boolean, _      Optional ByVal addIn As Object) As Boolean

    If addIn Is Nothing Then Set addIn = GetAddIn()
    If addIn Is Nothing Then Exit Function

    LoadListObject = addIn.Load(lo, reloadConfiguraton)

    If Not LoadListObject Then
        Debug.Print addIn.LastResultMessage
    End If

End Function

Moreover, you can save data changes using the Save method only:

' Saves the changes of the ListObject object to a database

Function SaveListObject(ByVal lo As ListObject, Optional ByVal addIn As Object) As Boolean

    If addIn Is Nothing Then Set addIn = GetAddIn()
    If addIn Is Nothing Then Exit Function

    SaveListObject = addIn.Save(lo)

    If Not SaveListObject Then
        Debug.Print addIn.LastResultMessage
    End If

End Function

You can use the following macros on the buttons to save and load data of the active tables.

' Saves the changes of the active ListObject

Sub Save()

    Dim lo As ListObject
    Set lo = GetActiveListObject()

    If lo Is Nothing Then Exit Sub

    Call SaveListObject(lo)

End Sub

' Reloads data of the active ListObject

Sub Load()

    Dim lo As ListObject
    Set lo = GetActiveListObject()

    If lo Is Nothing Then Exit Sub

    Call LoadListObject(lo, False)

End Sub

This function returns the active ListObject table:

' Returns the active ListObject

Function GetActiveListObject() As ListObject

    Set GetActiveListObject = ActiveCell.ListObject

    If Not GetActiveListObject Is Nothing Then Exit Function

    If ActiveSheet.ListObjects.Count <> 1 Then Exit Function

    Set GetActiveListObject = ActiveSheet.ListObjects(1)

End Function

Let's customize the design:

  1. Click on a table. Select the Design tab. Select the desired design in the Table Styles gallery.
    I prefer White, Table Style Medium 15.
  2. Right click on the selected design and click Set as Default.
  3. Uncheck Bunded Rows in the Table Style Options group.
  4. Select the View tab. Uncheck Gridlines in the Show group.
  5. Select cell A4 and click Freeze Panes in the Window group.
  6. Rename the worksheet to Companies.

Repeat the steps for the Items and Accounts tables.

Now we have the workbook that allows editing master tables.

Excel workbook to edit master tables

Creating master table editors using VBA

The following code allows creating editable tables described in the chapter in a completely new workbook:

' Creates master table editors, Chapter 2

Sub Chapter02_2_CreateMasterTableEditors()

    Dim addIn As Object
    Set addIn = GetAddInAndCheck()

    If addIn Is Nothing Then Exit Sub

    Dim wb As Workbook
    Set wb = ActiveWorkbook
    ' Set wb = Workbooks.Add(ActiveWorkbook.FullName)

    Call addIn.InsertAddInSheets(wb)

    Dim connString As String
    connString = GetConnectionString()

    Dim ws As Worksheet
    Set ws = GetOrCreateConnectedWorksheet(wb, _            "Companies", connString, "dbo67.Companies", "TABLE", True)

    If ws Is Nothing Then Exit Sub

    Call GetOrCreateConnectedWorksheet(wb, _            "Items", connString, "dbo67.Items", "TABLE", True)
    Call GetOrCreateConnectedWorksheet(wb, _            "Accounts", connString, "dbo67.Accounts", "TABLE", True)

    ws.Select

End Sub

The code contains an important call: addIn.InsertAddInSheets(wb)

This method inserts hidden worksheets used by the SaveToDB add-in to make the tables editable.

The GetAddInAndCheck function encapsulates the GetAddIn function and displays the installation error message:

' Returns the SaveToDB addin object and shows an error message

Function GetAddInAndCheck() As Object

    Set GetAddInAndCheck = GetAddIn()

    If GetAddInAndCheck Is Nothing Then
        Debug.Print "Install the SaveToDB add-in"
        MsgBox "Install the SaveToDB add-in." & vbCrLf _
             & "You can download it at www.savetodb.com"
    End If

End Function

GetOrCreateConnectedWorksheet is a high-level function to create a worksheet with an editable table:

' Returns a worksheet, creates if not exists, and inserts a connected table

Function GetOrCreateConnectedWorksheet(ByVal wb As Workbook, ByVal sheetName,
    ByVal connString As String, ByVal commandText As String, ByVal objectType As String,
    Optional queryListLocked As Boolean = False) As Worksheet

    Dim ws As Worksheet
    Set ws = GetOrCreateWorksheet(wb, sheetName)
        
    Set GetOrCreateConnectedWorksheet = ws

    If ws.ListObjects.Count > 0 Then Exit Function

    Dim r As Range
    Set r = ws.Range("B3")

    If Not InsertConnectedListObject(Nothing, _             r, connString, commandText, objectType, queryListLocked) Then
        Exit Function
    End If

End Function

We have discussed the InsertConnectedListObject function above in this chapter.

The following utile function returns an existing worksheet or creates it:

' Returns a worksheet, creates if not exists

Function GetOrCreateWorksheet(ByVal wb As Workbook, ByVal sheetName As String) As Worksheet

    Set GetOrCreateWorksheet = GetWorksheet(wb, sheetName)

    If Not GetOrCreateWorksheet Is Nothing Then Exit Function

    Set GetOrCreateWorksheet = AddWorksheet(wb, sheetName)

End Function

You can find and test a complete code in the payments.xlsm workbook.

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