04 – Excel Automation (Standard Code)

In this post, we will be showing you how to use VBA to import from an Excel file.

Although Access comes with built-in importing functionality, sometimes you will want extra control over how the data is being imported. You might not want to import data into one single table or you may wish to convert certain entries before inserting them into a table. VBA gives you the power to do this and much more. So, check out our video below!

The Code

Option Compare Database
Option Explicit


Public Sub GetData()
On Error GoTo ErrorHandler
Dim oExcel As Excel.Application
Dim oWB As Workbook
Dim oWS As Worksheet
Dim strExcelFilePath As String
Dim strExcelFileName As String
Dim db As dao.Database
Dim rs As dao.Recordset
Dim i As Integer
Dim j As Integer
Dim lColumn As Long
Dim lRow As Long
Dim strFieldNames() As String
Dim strTableName As String


'Path and file name for Excel
strExcelFilePath = "C:\Users\Robert\Desktop\postcodes.xlsx"
strExcelFileName = "postcodes.xlsx"

'Starts hourglass so user knows that the application is busy
DoCmd.Hourglass True

'Starts Excel App in memory
Set oExcel = New Excel.Application
Set oWB = oExcel.Workbooks.Open(strExcelFilePath)

'Here we provide table name
strTableName = "PostCodes"


'instantiate recordset - we will not be looping through here
'we will be using the recordset to add values as we go
Set db = CurrentDb
Set rs = db.OpenRecordset(strTableName)


With oWB
   'we will need to activate the workbook to reference its properties
        .Activate
        
   'These line of code tells us how many rows there in the Excel sheet
   lRow = .Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
       
        
   'from 2nd row to last
   For i = 2 To lRow
            
      'we tell the recordset that we will be adding a record here
      rs.AddNew
            
      'From first column to last
                    
         'columns:
         ' 1 = id
         ' 2 = outcode
         ' 3 = lat
         ' 4 = lng
                    
         Debug.Print "Importing from row " & i
                   
         rs.Fields("id") = _
             CLng(Trim(Nz(.Worksheets(1).Cells(i, 1).Value, -1)))
         rs.Fields("outcode") = _
             CStr(Trim(Nz(.Worksheets(1).Cells(i, 2).Value, "")))
         rs.Fields("lat") = _
             CDbl(Trim(Nz(.Worksheets(1).Cells(i, 3).Value, 0)))
         rs.Fields("lng") = _
             CDbl((Nz(.Worksheets(1).Cells(i, 4).Value, 0)))
         
      'nz(arg1, arg2) checks whether arg1 
      'is null and if it is returns arg2.
      'ergo nz(.fields("lat"), 0) would return 
      '0 if .fields("lat") was null
                        
       'trim(arg1) trims any leading or following 
       'spaces from a string. Good practice
                    
       'cLng/cStr/cDbl all convert an argument 
       'into a data type. Again, good practice
                     
            
       'This writes the update to the table
       rs.Update
           
   Next i
        
   'close the recordset
   rs.Close
End With
    
DoCmd.Hourglass False
MsgBox "Finshed"

ExitSub:

    Set rs = Nothing
    Set db = Nothing
    Set oWB = Nothing
    Set oExcel = Nothing
    DoCmd.Hourglass False
    Exit Sub
    
ErrorHandler:
    MsgBox "There has been an error. Please reload the form and start again"
    Resume ExitSub

End Sub

Using VBA to loop through Excel is a little tricky at first as you need to familiarise yourself with the Excel object model. There are, however, definite rewards if you persist as using Excel as a means to transfer information between databases is a very popular option!