How to Use VBA to Import Data From Excel Into Access

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Use VBA to Import Data From Excel Into Access
Import data from Excel into Access using the Excel Object Library. (Jupiterimages/Photos.com/Getty Images)

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.

Skill level:
Moderate

Other People Are Reading

Instructions

  1. 1

    Launch Microsoft Office Excel and type "data1" in A2, and "data2" in B2. Press "Ctrl" and "S" to open the "Save As" dialogue Window and save the workbook in "C:\Temp\" as "dataToImport.xlsx." Click "Save" and close Excel.

  2. 2

    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."

  3. 3

    Start by typing the following VBA code to create new sub procedure:

    Private Sub importExcelData()

  4. 4

    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

  5. 5

    Type the following to create variables you will use in Access:

    Dim dbRst As Recordset

    Dim dbs As Database

    Dim SQLStr As String

  6. 6

    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)

  7. 7

    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 excel Data(column One TEXT, column Two TEXT)"

    DoCmd.SetWarnings False

    DoCmd.RunSQL (SQLStr)

  8. 8

    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")

    dbRst.AddNew

  9. 9

    Type the following to get values from the Excel workbook, save them to your table and update the record:

    xlSht.Range("A2").Select

    dbRst.Fields(0).Value = xlSht.Range("A2").Value

    xlSht.Range("B2").Select

    dbRst.Fields(1).Value = xlSht.Range("B2").Value

    dbRst.Update

  10. 10

    End your procedure by typing the following VBA code:

    dbRst.Close

    dbs.Close

    xlBk.Close

    End Sub

  11. 11

    Press "F5" to run the procedure. The data in your Excel workbook has just been imported into your Access table.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.