Learning how to use Visual Basic for Applications to import data into Access can make your database application more dynamic. In VBA, you can use a combination of objects to open an existing Excel workbook and import certain data to a table in Access. Use the Microsoft Excel Object Library in VBA to open the workbook. The Recordset object is used to open an existing table in Access and save data from Excel into it. Using VBA to import data can significantly reduce the amount of time you spend importing data manually.
Launch Microsoft Office Excel and type "data1" in A2, and "data2" in B2. Press "Ctrl" and "S" to open the "Save As" dialog Window and save the workbook in "C:\Temp\" as "dataToImport.xlsx." Click "Save" and close Excel.
Launch Microsoft Office Access, click "Blank Database" and click the "Create" button. Click "Database Tools," and click "Visual Basic" to open the VB Editor Window. Click the "Insert" menu and then click "Module" to insert a new code module. Click the "Tools" menu, click "References," and check the box next to "Microsoft Excel <version number> Object Library."
Start by typing the following VBA code to create new sub procedure:
Private Sub importExcelData()
Type the following to create variables you will use to read Excel:
Dim xlApp As Excel.Application
Dim xlBk As Excel.Workbook
Dim xlSht As Excel.Worksheet
Type the following to create variables you will use in Access:
Dim dbRst As Recordset
Dim dbs As Database
Dim SQLStr As String
Type the following to define database objects and also define the Excel workbook to use:
Set dbs = CurrentDb
Set xlApp = Excel.Application
Set xlBk = xlApp.Workbooks.Open("C:\Temp\dataToImport.xlsx")
Set xlSht = xlBk.Sheets(1)
Create a new table with two columns in Access to import data from Excel. Type the following VBA code to create the table using the "DoCmd" object:
SQLStr = "CREATE TABLE excelData(columnOne TEXT, columnTwo TEXT)"
Open the table you just created by using a the Recordset object. Type the following to open the table and add a new row:
Set dbRst = dbs.OpenRecordset("excelData")
Type the following to get values from the Excel workbook, save them to your table and update the record:
dbRst.Fields(0).Value = xlSht.Range("A2").Value
dbRst.Fields(1).Value = xlSht.Range("B2").Value
End your procedure by typing the following VBA code:
Press "F5" to run the procedure. The data in your Excel workbook has just been imported into your Access table.
- Photo Credit Jupiterimages/Photos.com/Getty Images
How to Import a CSV File to Access
The comma separated value (CSV) file type is used because of its versatility. CSV files are formatted like spreadsheets but saved as...
How to Import an Excel Spreadsheet Into Access With a Macro
One of the most useful features of Microsoft Access for business users is its ability to integrate with other types of files....
How to Import Excel Data to Word Using Visual Basic
In the programming world it's useful to know how to import Microsoft Office Excel data to a Microsoft Office Word document for...
How to Use VBA in Access to Open an Excel Spreadsheet
Trying to analyze data through reports in Access is not as user-friendly as analyzing your data using Excel. In Access, you can...
How to Insert a Record Into the VBA Database
VBA is a programming language developed by Microsoft for use within a number of Microsoft products, including Microsoft Office. Programmers can create...