How to Import Data in Access With VBA

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Import Data in Access With VBA
It's simple to import data to Access using VBA. (binary data image by rgbspace from Fotolia.com)

Importing data through automation is much quicker than importing data manually. Microsoft Access is a relational database application that is part of the Microsoft Office suite. In Access, you can use Visual Basic for Applications (VBA) to automate the process of importing data. Importing data using VBA could be useful when you have many sheets to import in an Excel workbook. In just a few steps you can write VBA code to import data into Access.

Skill level:
Easy

Other People Are Reading

Instructions

  1. 1

    Start Microsoft Excel and add the following data in "Sheet1" and in "Sheet2":

    FIELD 1 FIELD 2

    DATA 1 DATA 2

    DATA 1 DATA 2

    Save the workbook in "C:\" as "XLToImport.xlsx".

  2. 2

    Start Microsoft Access, click "Database Tools" and select "Visual Basic."

    Type the following to create a new sub:

    Sub importExcelWorkbook()

    Press "Enter."

  3. 3

    Type the following to import "Sheet1."

    DoCmd.TransferSpreadsheet ac Import, acSpreadsheetTypeExcel97, _

    "Sheet1", "C:\XLToImport.xlsx", True, "Sheet1!A1:D100"
    
  4. 4

    Type the following to import "Sheet2."

    DoCmd.TransferSpreadsheet ac Import, acSpreadsheetTypeExcel97, _

    "Sheet2", "C:\XLToImport.xlsx", True, "Sheet2!A1:D100"
    
  5. 5

    Execute your sub to import the first two sheets of your Excel workbook.

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.