how to import excel into access using vba

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

importing excel into access, step by step

Introduction

In Microsoft Access, it is possible to accomplish most things in at least a couple of different ways, and importing Excel data into Access is no different. So, in this article, we show you how to import Excel into Access using the VBA language. For instance, we will be using the Excel Object Model. By learning this approach, you will learn insights regarding Microsoft Access, Microsoft Excel object model integration and, at the same time, we will present some ideas regarding DML (data manipulation language) operations against Access tables using SQL. 

Advantages of importing Excel into Access Using Excel Object Model

Other advantages of using the Excel object model are:

  1. Full access to each row / column value that allows complex validations, lookup processes and data cleanup
  2. Data type conversion
  • Access to all Excel object model functions if required
Let’s Start Importing Excel Into Access using VBA

To create the final outcome the reader will need

  1. A test excel file
  2. A Microsoft Access database with one table and a single form with a button

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

http://www.sample-videos.com/xls/Sample-Spreadsheet-10000-rows.xls

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

How To Import Excel Into Access

How To Import Excel Into Access

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

import excel into access vba screenshot showing data type of columns

How To Import Excel Into Access

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

How To Import Excel Into Access

How To Import Excel Into Access

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

How To Import Excel Into Access

How To Import Excel Into Access

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:

  1. The user will see a file picker dialog opening
  2. The user will be able to pick the excel file
  • If an Excel file was picked its path will be assigned to a variable
  1. 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.

How To Import Excel Into Access

How To Import Excel Into Access

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

How To Import Excel Into Access

How To Import Excel Into Access

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.

Private Sub cmdImportExcel_Click()

On Error GoTo cmdImportExcel_Click_err:

Dim fdObj As Office.FileDialog ‘declares variable fdObj

Dim varfile As Variant ‘variant type variable that will store the selected file path

Set fdObj = Application.FileDialog(msoFileDialogFilePicker) ‘instantiates the variable creating a filepicker object using late binding

 

With fdObj ‘using the with statement we will be working with fdObj by default

‘does not allow selecting more than one file

.allowmultiselect = False

‘clears the file dialog file type existing filters

.Filters.Clear

‘this file dialog will only allow the selection of xlsx files

.Filters.Add “Excel 2007+”, “*.xlsx”

 

‘optional, set the file dialog title

.Title = “Please select the excel file to import …”

.Show

 

If .SelectedItems.Count = 1 Then ‘a file was selected

Call MsgBox(“The selected file was: ” & .SelectedItems(1)) ‘for now we will test the file picking by sending the file path to a message box on screen

Else ‘no file was selected

Call MsgBox(“No file was selected.”)

End If

End With

 

Exit Sub

 

cmdImportExcel_Click_err:

Select Case Err.Number

Case Else

Call MsgBox(Err.Number & ” – ” & Err.Description, vbCritical + vbOKOnly, “System Error …”)

End Select

End Sub

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

How To Import Excel Into Access

How To Import Excel Into Access

Then the form will be presented

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

How To Import Excel Into Access

How To Import Excel Into Access

 

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.

How To Import Excel Into Access

How To Import Excel Into Access

And mark the check box reference to the Excel application

How To Import Excel Into Access

How To Import Excel Into Access

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

  1. xlApp – will be a reference to a hidden Excel application
  2. 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

  1. Delete existing data in destination table
  2. 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

Private Sub cmdImportExcel_Click()

On Error GoTo cmdImportExcel_Click_err:

 

Dim fdObj As Office.FileDialog ‘declares variable fdObj

Dim varfile As Variant ‘variant type variable that will store the selected file path

 

Set fdObj = Application.FileDialog(msoFileDialogFilePicker) ‘instantiates the variable creating a filepicker object using early binding

 

With fdObj ‘using the with statement we will be working with fdObj by default

‘does not allow selecting more than one file

.allowmultiselect = False

‘clears the file dialog file type existing filters

.Filters.Clear

‘this file dialog will only allow the selection of excel files, this is achieved handling the Filters collection

.Filters.Add “Excel 2003”, “*.xls”

.Filters.Add “Excel 2007+”, “*.xlsx”

 

‘optional set the file dialog title

.Title = “Please select the excel file to import …”

.Show

 

If .SelectedItems.Count = 1 Then ‘a file was selected so data can be imported from Excel from this point the loop import process will run

‘variables declaration

Dim xlApp As Excel.Application ‘the excel application

Dim xlWb As Excel.Workbook ‘the excel workbook reference that will point to the opened workbook

Dim xlWs As Excel.Worksheet ‘the excel worksheet with data

Dim intLine As Long ‘the line counter

Dim strSqlDml As String ‘string variable that stores the executed SQL statements

Dim strColumnBcleaned As String ‘string variable that stores values from column B after replacing single quotes by four single quotes

‘remember the quote is the string delimiter in SQL so it needs to be escaped

Dim strColumnGcleaned As String ‘string variable that stores values from column G cleaned, the clean step replaces commas by dots as

‘the decimal separator in SQL is the dot

varfile = .SelectedItems(1) ‘picking the selected file full path

clean the existing table

CurrentDb.Execute “DELETE * FROM tblExcelImport”, dbFailOnError

 

‘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

strSqlDml = “INSERT INTO tblExcelImport VALUES(” & xlWs.Cells(intLine, 1).Value2 & “, ‘” & strColumnBcleaned & “‘, ” & strColumnGcleaned & “)”

‘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

DoCmd.OpenTable “tblExcelImport”, acViewNormal, acEdit

‘this branch only happens if no file was selected

Else ‘no file was selected

Call MsgBox(“No file was selected.”)

End If

End With

 

Exit Sub

 

cmdImportExcel_Click_err:

Select Case Err.Number

Case Else

Call MsgBox(Err.Number & ” – ” & Err.Description, vbCritical + vbOKOnly, “System Error …”)

End Select

End Sub

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

https://msdn.microsoft.com/en-us/library/bb177907(v=office.12).aspx

To run a query in Access pulling data from an Excel file a similar solution to the following one can be used

SELECT CustomerID, CompanyName

FROM [Customers$]

IN “c:\documents\xldata.xls” “EXCEL 5.0;”

WHERE CustomerID Like “A*”

ORDER BY CustomerID;

Also, when dealing with SQL statements built dynamically it is very important to handle possible null values, replacing them by default values or even forcing the null value insert.

If a value can be null the variable needs to be of type Variant to store it and then use it in the built insert statement.

Conclusion

Thanks for reading about how to import excel into Access. Leave a comment if you have any questions on how to import excel into access.

Categories:

12 Responses

  1. Hi, thanks a lot for the code. I managed to use the first part with the file picking. However, the second one was more difficult. I don’t want to delete the info in my database and just add some additional info. Is it possible to send me the code that I should use for this purpose? Thanks and regards, Susy

      • Hi ededic87,
        I would like to import some data to my database via Excel without passing through the Access form I created. As I understood, your code is deleting all data in the database and then loads the entire data from the Excel file. I would like to only complete the data that I already have in my database.

        • Yes using a linked table to an excel file is a possible solution to insert
          rows into a table (linked or not).

          If the excel table is named tbl_Excel and destination one is tbl_Dest for
          two sample fields things would be like

          INSERT INTO tbl_Dest(FIELD_A, FIELD_B)
          SELECT FIELD_A, FIELD_B
          FROM tbl_Excel

          of course you do need to take note of the source fields format and
          destination fields expected date types.

  2. I’m with Susy, above. The first part regarding the file picker worked perfectly. The next part, not so much.

    I have my Excel source file (Import Test.xlsx) with headings that match the headings of my destination table in Access (_tblMain). I want to import all contents of the source file worksheet into the destination table (not just columns A, B, and G).

    I also didn’t understand the purpose of changing single quotes to double quotes or commas to dots, or even if I need to do that for my specific task. I do have long strings of text in some of the cells that contain quotation marks, commas, and semicolons.

    How can I import everything and ensure the source punctuation remains in the destination fields?

    • First, put a break point (https://docs.microsoft.com/en-us/office/vba/language/how-to/set-and-clear-a-breakpoint) on the line you think is causing the problem and see if that variable has properties and values in the watch window.

      So, once this is ok the next step is to iterate through Excel rows that is
      made by the Do loop cycle that will end when this condition is verified Loop
      Until IsEmpty(xlWs.Cells(intLine, 1))
      intLine variable will increment one by one.
      So when iterating through each line one needs to build an insert statement
      dynamically

      strSqlDml = “INSERT INTO tblExcelImport VALUES(” & xlWs.Cells(intLine,
      1).Value2 & “, ‘” & strColumnBcleaned & “‘, ” & strColumnGcleaned & “)”

      This example will insert data into all table columns so they do not need to
      be enumerated.

      The reason to replace single quotes and comas by dots is related to SQL
      Syntax. The single quote is the text delimiter in SQL and if you have it in
      entering value you need to escape it for instance in name Mc’Donald you
      need to replace it so you get
      Mc”Donald otherwise SQL engine will assume it as a string delimiter.
      Similar logic for decimal separator.

      SQL always use dot as decimal
      separator but in some countries like Portugal we use the coma so it needs
      to be replaced by dot.
      As final note that is relevant due to computer regional settings or if
      value in Excel cells is stored as text. If cell value is stored as number
      it will always be passed to a string using regional settings decimal
      separator.

      • I’m still a bit lost. I’m looking at the following:

        ——-
        Dim strColumnBcleaned As String

        Dim strColumnGcleaned As String

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

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

        strSqlDml = “INSERT INTO tblExcelImport VALUES(” & xlWs.Cells(intLine, 1).Value2 & “, ‘” & strColumnBcleaned & “‘, ” & strColumnGcleaned & “)”
        ——-

        My source worksheet has over 40 columns of data that I need to import. Do I have to do the Dim and clean steps for every single column? Or is there a way to cleanly do all of them with just a few lines of code? I also don’t see the lines of code where you change the commas to dots.

        What e-mail list? You’ve piqued my interest.

        Thanks,
        J.

        • Hi,

          There should be a pop-up when you visit this page to your right. If you don’t see it, I can send the info to your email. But I go over in a video how to use VBA to import. (a simpler method) and I think the video instruction makes things more clear.

          Ermin

  3. I would suggest people sign up for the email list because I have alternative ways for you to import Excel files and also actual video instead of articles.

    Regards,
    Ermin

  4. Hi ermin
    I tried this tutorial but unexpectedly getting an error at this line

    strSqlDml = “INSERT INTO tblExcelImport VALUES(” & xlWs.Cells(intLine, 1).Value2 & “, ‘” & strColumnBcleaned & “‘, ” & strColumnGcleaned & “)”

    as Expected: End of statement

    can u pls help

    regards
    Ji

Leave a Reply

Your email address will not be published. Required fields are marked *