How To Convert An Access Database To Excel - PT1

The IT department at most companies installs the MS Office suite on an employee’s computer without Access, but MS Access is a “specialty item”, and has to be requested as a separate install.

Since some folks don’t have the Access program, but they have Excel, in this post I am going to convert my Access database into an Excel format.

Using Access is much quicker and straight forward, but if you have to, you can use this method.

Note: you could have one installation of Access (paid) and then use the Access runtime (free) .

Here is my Access form:

******************************
In order to replicate this, I need a main form / sub form (datasheet) type setup.

I plan to accomplish the main form with just a regular userform, and for the subform (datasheet) I will be using a listbox, and labels as the column headings.

Then, finally, you will find out how to click on the listbox row and display the clicked item, so we can eventually open the compliance form (to do in the next tutorial).
*******************************

Here is what the final result will look like:

First I am going to put all of the data from my database tables into worksheets in the workbook, and add a new worksheet to start from.

This worksheet can be “decked out”, but I’m just going to add a “Start” button, and add a macro to it (as shown in the next few images).

I’ll add an image because it’s more functional for making it visually appealing than the common “button” from the Design toolbar’s toolbox.

Now add the macro to show the UserForm, and attach it to the new rectangle object

Here is the image and the code for the top part of the form

'Erik Loebl
'https://vbastring.com
'erik@loeblcomservices.com

'We will make a modular scope variable because we need this _
    row reference as long as the form is open

Dim m_lngRow As Long

Private Sub btnNext_Click()
    Worksheets("Stations").Activate
        
    'increment the active row and sync the UserForm and the Activesheet
    'm_lngRow = ActiveCell.Row + 1
    m_lngRow = m_lngRow + 1

    'select the next cell and render the new data on the UserForm
    
    Worksheets("Stations").Range("A" & m_lngRow).Select
    Me.txtStationID = Range("A" & m_lngRow)
    Me.txtStationName = Range("B" & m_lngRow)
    
    Me.txtManagerID = Range("C" & m_lngRow)
    Me.txtVisitInterval = Range("D" & m_lngRow)
    Me.txtNextVisitDate = Range("E" & m_lngRow)
    
    '*************************************************
    'these are the inspections done at the station
    '*************************************************
    GetInspections (Range("A" & m_lngRow))
    

End Sub



Private Sub btnPrev_Click()
    'increment the active row and sync the UserForm and the Activesheet
    m_lngRow = ActiveCell.Row - 1
    
    If m_lngRow > 1 Then
        'select the next cell and render the new data on the UserForm
        Worksheets("Stations").Range("A" & m_lngRow).Select
        Me.txtStationID = Range("A" & m_lngRow)
        Me.txtStationName = Range("B" & m_lngRow)
        
        Me.txtManagerID = Range("C" & m_lngRow)
        Me.txtVisitInterval = Range("D" & m_lngRow)
        Me.txtNextVisitDate = Range("E" & m_lngRow)
        
        '*************************************************
        'these are the inspections done at the station
        '*************************************************
        GetInspections (Range("A" & m_lngRow))
    End If
End Sub

Here is where the data for the top comes from:

And here is the code for the bottom (sub) part of the form. The information pertaining to each station.


Function GetInspections(Arg1) As Boolean
    'Purpose: Find the new cities we did business in
    
    Dim cnn As Object
    Dim rst As Object
    Dim strSQL As String
    Dim lngCount As Long
    
    Dim strValue As String
    Dim blnNew As Boolean
    
    Dim strDate As String

    'In order to do queries with a WHERE clause, you need to name the range, otherwise use the worksheet name.

    'get the new list cell value
    strValue = Arg1
    
    If strValue = "staID" Or strValue = "" Then
        'you are at the beginning or end so don't continue
        Me.ListBox1.Clear
        GetInspections = False
    Else
        'Set up the connection to the Excel worksheet
        Set cnn = CreateObject("ADODB.Connection")
        With cnn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .ConnectionString = "Data Source=" &amp; ThisWorkbook.Path &amp; "\" &amp; ThisWorkbook.Name &amp; ";" &amp; _
                "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
            .Open
        End With
            
        'check if that value is already one we've done business in.
        strSQL = "SELECT * FROM [SC] WHERE [sacStationID] =" &amp; strValue
        Set rst = cnn.Execute(strSQL)
            
        i = 0
        
        With Me.ListBox1
            .Clear
            .ColumnCount = 6
            .ColumnWidths = ".5in,0in,.5in,1in,.5in,0in"
        End With
        
        Do Until rst.EOF
            
            With Me.ListBox1
                .AddItem
                .List(i, 0) = rst.Fields(0)
                .List(i, 2) = rst.Fields(2)
                .List(i, 3) = rst.Fields(3)
                .List(i, 4) = rst.Fields(4)
    
                i = i + 1
            End With
    
            rst.movenext
        Loop
        
    
        
        rst.Close
        cnn.Close
        
        Set rst = Nothing
        Set cnn = Nothing
        
        GetInspections = True
    End If
    
    
End Function

Here is where the data for the lower form comes from:

Get The Listbox Value In VBA

To see the detail about a particular compliance inspection, we need to be able to click the sub row and then show a form based on the item that was clicked.

The form is not created yet, but for illustrative purposes, here is how we are going to capture the id to pass to the compliance inspection form.

Private Sub ListBox1_Click()
    Dim intItems As Integer
    
    For intItems = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(intItems) = True Then
           MsgBox ListBox1.List(intItems)
        End If
     Next intItems
End Sub

Stay tuned for part 2 of this.

Any questions so far? Leave them in the comments below, and share this with someone else.

How To Convert An Access Database To Excel – PT1

Here is what the final result will look like:

Now add the macro to show the UserForm, and attach it to the new rectangle object

Here is the image and the code for the top part of the form

And here is the code for the bottom (sub) part of the form. The information pertaining to each station.

Get The Listbox Value In VBA