How To Import Excel Data Into Access VBA

 

How To Import Excel Data Into Access VBA

How To Import Excel Data Into Access Using VBA code

Udemy Sale – IT Category Page-Dev & ITCategorySale-Discount:All Dev & IT Courses are $10, All others are $15-StartDate:12/14-EndDate; 12/23


In this article we will explain how to import excel data into Access using VBA language. Specifically, we will be using the Excel Object Model.

The reader should be aware this is not the only possible approach to import Excel data into an Access table. Choosing this option will provide some insights regarding Microsoft Access, Microsoft Excel object model integration and, at same time, will present some ideas regarding DML (data manipulation language) operations against Access tables using SQL.

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

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

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:

  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.

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.

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

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

  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.

Leave a Reply

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