Is there a good tutorial (book or video series) that can teach me how to make Excel behave like a database?

[JenniferMurphy]

I have a bunch of applications that seem like they are better suited for a database than a spreadsheet where I can normalize the data and queries and forms to manipulate and interrogate the data in pretty much unlimited ways. But several conversations I have read here suggest to me that Excel has features that I am not aware of that can come pretty close to impersonating, emulating, or actually being a database.

I am wondering if there is a really good book or video series that I can get that will teach me how to make Excel function like a database. I am not looking for something that is free.

I would also be interested in any consultants or tutors who could walk me through that process and critique my work.

Thanks for all tips and pointers.

 

[awoohaw]

One of Excel's power tools is called Power Pivot which can behave very much like MS-Access. Mr. Excel has a forum called Power Tools and there are many You Tube videos that can give you a basic instruction. And these probably have links to subscription services for more robust lessons. The ExcelIsFun channel may have free playlists.

 

[alansidman]

My 2 cents. You can use excel as a DB but it seems that it is a lot like trying to use a screwdriver when you need a hammer. I always lean toward MS Access. It does have a steep learning curve, but once you understand it, it can be very powerful and useful. Just for grins, here is a good link to a tutorial on that subject.

Access Basics

 

[Logit]

If you decide to go with Excel, here is a sample project you might begin with ...


(A partial sample of the code)

Option Explicit
'---------------------------------------------------------------------------------------
' Module    : Database Form
' DateTime  : 31/08/2005 10:55. Updatede 08-02-08
' Author    : Roy Cox
' Purpose   : Data entry form for Excel, with Search facility
' Amended   : 27/04/2013
'---------------------------------------------------------------------------------------
Dim Ws As Worksheet
Dim MyData As Range, c As Range, rFound As Range, rng As Range
Dim r As Long
Const frmMax As Long = 320
Const frmHt As Long = 210
Const frmWidth As Long = 290
Dim oCtrl As MSForms.Control

Private Sub cmbAdd_Click()
'next empty cell in column A

    Set c = MyData.Cells(MyData.Rows.Count, 1).Offset(1)

    Application.ScreenUpdating = False    'speed up, hide task
    'write userform entries to database
    With Me
        c.Value = .TextBox1.Value
        c.Offset(0, 1).Value = .TextBox2.Value
        c.Offset(0, 2).Value = .TextBox3.Value
        c.Offset(0, 3).Value = .TextBox4.Value
        If Me.optYes Then
            c.Offset(0, 4).Value = "Yes"
        ElseIf .optNo Then
            c.Offset(0, 4).Value = "No"
        End If
        'clear the form
        ClearControls
        'resize database
        Set MyData = c.CurrentRegion
        Me.ScrollBar1.Max = MyData.Rows.Count
    End With
    Application.ScreenUpdating = True
End Sub

Private Sub cmbDelete_Click()
    Dim msgResponse As String    'confirm delete
    Application.ScreenUpdating = False
    'get user confirmation
    msgResponse = MsgBox("This will delete the selected record. Continue?", _
                         vbCritical + vbYesNo, "Delete Entry")
    Select Case msgResponse    'action dependent on response
    Case vbYes
       If c Is Nothing Then Set c = Ws.Cells(r, 1)
        c.EntireRow.Delete    'remove entry by deleting row
        Set MyData = Ws.Range("a8").CurrentRegion   'database
        'restore form settings
        With Me
            .cmbAmend.Enabled = False    'prevent accidental use
            .cmbDelete.Enabled = False    'prevent accidental use
            .cmbAdd.Enabled = True    'restore use
            .ScrollBar1.Max = MyData.Rows.Count
            'clear form
            ClearControls
        End With

    Case vbNo
        Exit Sub    'cancelled
    End Select
    Application.ScreenUpdating = True
End Sub

Private Sub cmbFind_Click()
    Dim strFind As String    'what to find
    Dim FirstAddress As String
    Dim f As Integer

'    imgFolder = ThisWorkbook.Path & Application.PathSeparator & "images" & Application.PathSeparator
    strFind = Me.TextBox1.Value    'what to look for

    With MyData
    .AutoFilter
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it

            With Me    'load entry to form
                .TextBox2.Value = c.Offset(0, 1).Value
                .TextBox3.Value = c.Offset(0, 2).Value
                .TextBox4.Value = c.Offset(0, 3).Value
                .cmbAmend.Enabled = True     'allow amendment or
                .cmbDelete.Enabled = True    'allow record deletion
                .cmbAdd.Enabled = False      'don't want to duplicate record
                If c.Offset(0, 4).Value = "Yes" Then .optYes = True
                If c.Offset(0, 4).Value = "No" Then .optYes = True
                r = c.Row
                f = 0
            End With
            FirstAddress = c.Address
            Do
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
            If f > 1 Then
                Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")

                Case vbOK
                    FindAll
                Case vbCancel
                    'do nothing
                End Select
                Me.Height = frmMax

            End If
        Else: MsgBox strFind & " not listed"    'search failed
        End If
    End With

End Sub

Private Sub cmbAmend_Click()
    Application.ScreenUpdating = False
    If r <= 0 Then Exit Sub

    Set c = Ws.Cells(r, 1)
    c.Value = Me.TextBox1.Value          ' write amendments to database
    c.Offset(0, 1).Value = Me.TextBox2.Value
    c.Offset(0, 2).Value = Me.TextBox3.Value
    c.Offset(0, 3).Value = Me.TextBox4.Value
    If Me.optYes Then
        c.Offset(0, 4).Value = "Yes"
    ElseIf Me.optNo Then
        c.Offset(0, 4).Value = "No"
    End If
    'restore Form
    With Me
        .cmbAmend.Enabled = False
        .cmbDelete.Enabled = False
        .cmbAdd.Enabled = True
        ClearControls
        .Height = frmHt
    End With
    If Sheet1.AutoFilterMode Then Sheet1.Range("A8").AutoFilter
    Application.ScreenUpdating = True
    On Error GoTo 0
End Sub
Sub FindAll()
    Dim wesTemp As Worksheet
    Dim strFind As String    'what to find

    strFind = Me.TextBox1.Value

    If Not Ws.AutoFilterMode Then MyData.AutoFilter

    MyData.AutoFilter Field:=1, Criteria1:=strFind

    Me.ListBox1.Clear
    For Each c In MyData.Columns(1).SpecialCells(xlCellTypeVisible)
        With ListBox1
            .AddItem c.Value
            .List(.ListCount - 1, 1) = c.Offset(0, 1).Value
            .List(.ListCount - 1, 2) = c.Offset(0, 2).Value
            .List(.ListCount - 1, 3) = c.Offset(0, 3).Value
            .List(.ListCount - 1, 4) = c.Offset(0, 4).Value
            .List(.ListCount - 1, 5) = c.Row
        End With
    Next c

End Sub
Private Sub cmbLast_Click()
    Dim LastCl As Range

    With MyData
        Set LastCl = .Cells(.Rows.Count, 1)
    End With

    With Me
        .cmbAmend.Enabled = False
        .cmbDelete.Enabled = False
        .cmbAdd.Enabled = True
        .TextBox1.Value = LastCl.Value
        .TextBox2.Value = LastCl.Offset(0, 1).Value
        .TextBox3.Value = LastCl.Offset(0, 2).Value
        .TextBox4.Value = LastCl.Offset(0, 3).Value
         If LastCl.Offset(0, 4).Value = "Yes" Then
            .optYes = True
        Else: .optNo = True
        End If
    End With
End Sub


Private Sub cmnbFirst_Click()
    Dim FirstCl As Range

    'first data Entry
    Set FirstCl = MyData.Cells(2, 1)

    With Me
        .cmbAmend.Enabled = False
        .cmbDelete.Enabled = False
        .cmbAdd.Enabled = True
        .TextBox1.Value = FirstCl.Value
        .TextBox2.Value = FirstCl.Offset(0, 1).Value
        .TextBox3.Value = FirstCl.Offset(0, 2).Value
        .TextBox4.Value = FirstCl.Offset(0, 3).Value
        If FirstCl.Offset(0, 4).Value = "Yes" Then
            .optYes = True
        Else: .optNo = True
        End If
    End With
End Sub

Private Sub ListBox1_Click()
Set c = Nothing
    With Me.ListBox1

        If .ListIndex = -1 Then    'not selected
            MsgBox " No selection made"
        ElseIf .ListIndex >= 1 Then    'User has selected
            r = Val(.List(.ListIndex, .ColumnCount - 1))
        End If
    End With

    With Me
        .TextBox1.Value = .ListBox1.List(.ListBox1.ListIndex, 0)
        .TextBox2.Value = .ListBox1.List(.ListBox1.ListIndex, 1)
        .TextBox3.Value = .ListBox1.List(.ListBox1.ListIndex, 2)
        .TextBox4.Value = .ListBox1.List(.ListBox1.ListIndex, 4)
         r = .ListBox1.List(.ListBox1.ListIndex, 5)
        .cmbAmend.Enabled = True      'allow amendment or
        .cmbDelete.Enabled = True     'allow record deletion
        .cmbAdd.Enabled = False       'don't want duplicate
        If ListBox1.List(.ListBox1.ListIndex, 4) = "Yes" Then
            .optYes = True
        Else: .optNo = True
        End If
    End With

End Sub

Private Sub ScrollBar1_Change()
Dim Rw As Long
Rw = Me.ScrollBar1.Value
With Me
        .cmbAmend.Enabled = False
        .cmbDelete.Enabled = False
        .cmbAdd.Enabled = True
        .TextBox1.Value = MyData.Cells(Rw, 1).Value
        .TextBox2.Value = MyData.Cells(Rw, 2).Value
        .TextBox3.Value = MyData.Cells(Rw, 3).Value
        .TextBox4.Value = MyData.Cells(Rw, 4).Value
        If MyData.Cells(Rw, 5).Value = "Yes" Then
            .optYes = True
        Else: .optNo = True
        End If
    End With

End Sub

Private Sub UserForm_Initialize()
'change sheet name and Range here
    Set Ws = Sheet1
    Set MyData = Ws.Range("a8").CurrentRegion   'database
    With Me
        .Caption = "Database Example"    'userform caption
        .Height = frmHt
        .Width = frmWidth
        .ScrollBar1.Max = MyData.Rows.Count
        .ScrollBar1.Min = 2
    End With
End Sub

Sub ClearControls()
    With Me
        For Each oCtrl In .Controls
            Select Case TypeName(oCtrl)
            Case "TextBox": oCtrl.Value = Empty
            Case "OptionButton": oCtrl.Value = False
            End Select
        Next oCtrl
    End With
End Sub

Download sample workbook : Internxt Drive – Private & Secure Cloud Storage

 

[awoohaw]

My 2 cents. You can use excel as a DB but it seems that it is a lot like trying to use a screwdriver when you need a hammer. I always lean toward MS Access. It does have a steep learning curve, but once you understand it, it can be very powerful and useful. Just for grins, here is a good link to a tutorial on that subject.

Access Basics

@alansidman , I agree. I was answering her specific question. But, to add on to what you say about MS-Access .... users can easily link MS-Access tables to MS-Excel.

 

[JenniferMurphy]

One of Excel's power tools is called Power Pivot which can behave very much like MS-Access. Mr. Excel has a forum called Power Tools and there are many You Tube videos that can give you a basic instruction. And these probably have links to subscription services for more robust lessons. The ExcelIsFun channel may have free playlists.

Thanks, I'll check that out.

 

[JenniferMurphy]

My 2 cents. You can use excel as a DB but it seems that it is a lot like trying to use a screwdriver when you need a hammer. I always lean toward MS Access. It does have a steep learning curve, but once you understand it, it can be very powerful and useful. Just for grins, here is a good link to a tutorial on that subject.

Access Basics

Wait, screwdrivers are for nails, right? And what are hammers for? Ham radios?

I actually agree with you. It's just that I am so much more familiar with Excel... And I am lazy, so getting up to speed on Access is a ways down on my To Do list.

 

[JenniferMurphy]

If you decide to go with Excel, here is a sample project you might begin with ...

(A partial sample of the code)

Download sample workbook : Internxt Drive – Private & Secure Cloud Storage

If that's a partial sample, Access might be easier...

But thanks for the sample.

 

[awoohaw]

Wait, screwdrivers are for nails, right? And what are hammers for? Ham radios?

I actually agree with you. It's just that I am so much more familiar with Excel... And I am lazy, so getting up to speed on Access is a ways down on my To Do list.

Wait, screwdrivers are for nails, right? And what are hammers for? Ham radios?

I actually agree with you. It's just that I am so much more familiar with Excel... And I am lazy, so getting up to speed on Access is a ways down on my To Do list.

One of the tutorials on Power Pivot by ExcelIsFun describes how it is like MS-Access tables. So, when you start on the MS-Access path you'll have a head start. I agree with you that adding a little bit more of excel knowledge will serve you quicker than a brand new application. But, MS-Access can be a very powerful addition to your excel toolbox.

 

[JenniferMurphy]

@alansidman , I agree. I was answering her specific question. But, to add on to what you say about MS-Access .... users can easily link MS-Access tables to MS-Excel.

Why would anyone want to link Access tables to Excel? What advantages does that offer?

And that brings up another question: Do either Access or Excel allow me to create a form that will run on my iPhone that will have R/W access to the data on my Win 11 laptop?