How To Import Excel Into Access using VBA; step by step

How To Import Excel Into Access using VBA; step by step

How to import excel into Access using the VBA language is the purpose of this article. Specifically, we will be using the Excel Object Model. The reader should be aware this is not the only possible approach to import Excel into Access. Choosing this option will provide some insights regarding Microsoft Access, Microsoft Excel object model integration and, at the same time, will present some ideas regarding DML (data manipulation language) operations against Access tables using SQL.

If you only have the time to learn how to import excel data into Access, for now, stay here and let’s begin.

The first step will be to obtain some Excel sample data to test the load process. Once generating several lines of dummy data is a tedious process a simpler approach is to download some sample test data, I found this sample file

In the same page the reader will find other smaller and bigger files. The selected file layout is as follows

There are ten columns present without column headings.

In this example we will import columns A, B and G into the destination database table.

Let’s start …

After creating an empty Microsoft Access database, the user should create an empty table with the layout presented below

The table field ItemId will be mapped to column A, the field Description will be mapped to column B and the field Price to column G. The reader should pay attention to the field price underlying data type. Once source data can have decimal numbers the destination field should be able to accommodate them (remember when working with SQL the decimal separator is the dot). The first field is simply an integer sequence and the second one a description text string (more notes on this later on).

Once the table is properly created the next step will be the creation of a simple form with a button to trigger the process. Assuming the reader already has some Microsoft Access experience the final form should be similar to

Change the button property caption to Import Excel and the button name to cmdImportExcel (as mentioned in previous tutorial, naming conventions are always a good practice). Click the save button and name the form as frmImportExcel.

To provide some user friendly features the created button will call a file picker control dialog, this way the user will be able to select the source file from the hard-drive picking it from any location.

Right click on the insert button, choose build event and then code builder. The Visual Basic editor should open

As a side not let’s force explicit variable declaration in the code so each required variable is declared using its proper data type and no machine resources are allocated in vain. In the top of the code (bellow Option Compare Database) simply add

Option Explicit

From now on all required variables will require a proper declaration.

Before implementing the code let’s explain the underlying sequence logic:

The user will see a file picker dialog opening

The user will be able to pick the excel file

If an Excel file was picked its path will be assigned to a variable

The path stored in that variable will be used to open the Excel in background and load all rows into the Access table

Implementing the file dialog control

To use the file dialog control, we must provide a reference to the Microsoft Office XX Object Library (will depend on the office version installed). To do that access the Tools -> References menu in the VBA editor.

Navigate down using the scroll bar and choose the installed Office object library installed.

From now on all objects and methods provided by it are available to use.

Let’s add the file dialog picker underlying code…

In the VBA editor, inside cmdImportExcel_Click() event put the following code. The code is highly commented so the reader understands what each line is doing.

Now let’s test the code and check how it works. First let’s visualize the form view by clicking as presented in the next picture

Then the form will be presented

The reader should now press the Import Excel button and a file picker dialog will be presented

Next please select the downloaded file, hit the open button and the outcome should be similar to the one bellow only showing a proper path

If this is the outcome, everything is working as expected and the file picking component is working already. Next we will see how to include the Excel object model into the Microsoft Access solution. The reader will have to include the proper reference to the Excel object model. Once again, access the Tools -> References menu in the VBA editor.

And mark the check box reference to the Excel application

From now on all Excel object methods and properties will be available once an Excel variable is created inside the Microsoft Access VBA code. This technique is called early binding, data types are known in advance but checking these references is not mandatory. If those references are not set a late binding technique is being used but it will make development more difficult as the code editor intellisense will not show the properties and methods, deep documentation reading will be required. A common approach is to use the reference while developing and removing it when finished, the unknown constants must then be replaced by their corresponding integer values. We will not get into much more detail but it is important to be aware these references are not mandatory, they just make life easier.

Once the reference to Excel is created we can declare variables types defined there. We will need to declare three variables

xlApp – will be a reference to a hidden Excel application

xlWb – will be a reference to the workbook opened as a result of opening the file to import

xlWs – will be a reference to the worksheet having data to import

The steps involved in the load process can be described as follows

Delete existing data in destination table

Loop through all excel lines running an insert statement per line until the last line is reached

The loop process will stop when it does not find any more data in the column A

The required code for the entire process is the following. We will add extensive comments on all lines, please check them bellow in green

‘instantiate the Excel application, creating the Excel application in memory, the excel Accplication will be visible so the user is able to see the loop iterating through Excel rows but usually it is hidden and only visible if indeed required

Set xlApp = New Excel.Application

xlApp.Visible = True

‘opening the picked file by calling the Excel workbooks collection open method, it receives the file location as parameter and returns a reference for the opened file

Set xlWb = xlApp.Workbooks.Open(varfile)

‘seting the worksheet to the first one within the available, as it is the one having data to be imported

Set xlWs = xlWb.Worksheets(1)

‘default counter initial value/line, this means we start iterating in line one

intLine = 1

Do

‘the next two lines replace single quotes in column B value and commas by dots as decimal separator in column G

strColumnBcleaned = Replace(xlWs.Cells(intLine, 2).Value2, “‘”, “”””)

strColumnGcleaned = Replace(xlWs.Cells(intLine, 7).Value2, “‘”, “”””)

‘the next line creates a SQL insert statement using the previous obtained cleaned variables and the value for column A

The insert statement must have the sequence present in the destination table and is obtained by concatenating values per each line presented in the Excel file while iterating

‘executes the insert statement against the database, the dbFailOnError is an optional value that will make the Execute process return an error if the SQL was not properly executed

CurrentDb.Execute strSqlDml, dbFailOnError

‘the next line only puts the selected cell in Excel in the actual line position, this is not required and will even make the process slower, it is just present here so the reader can see things running

xlWs.Cells(intLine, 1).Select

intLine = intLine + 1

Loop Until IsEmpty(xlWs.Cells(intLine, 1)) ‘stopping criteria, when values in column A stop the loop will stop, please note in cells collection the first index is the row and the second one the column so we are making row changing. Once the loop stops the steps after close the open workbook, quit excel and clean the memory references to the created objects

xlWb.Close False

xlApp.Quit

Set xlApp = Nothing

Set xlWb = Nothing

Set xlWs = Nothing

‘the next step opens the loaded table so the user can see imported data

As mentioned before, this is not the only approach to import Excel data, it is possible to create linked tables and, to import data into Microsoft Access, the reader should also consider the DoCmd.TransferDatabase method as well. Linked tables can make use of connectivity drivers so they may not point only to databases, it is even possible to create a linked table to a text file.

Some relevant examples related to the IN clause can be found over here