How to Import Delimited Excel Spreadsheet Into Access VBA

Import Excel spreadsheet into Access using VBA.

Knowing how to import an Excel tab delimited spreadsheet into Access in Visual Basic for Applications (VBA) is useful knowledge to have. A common task when working with Microsoft Office applications is to transfer data from one application to another. VBA can be used in Microsoft Office applications to automate processes such as importing and exporting data. It's not difficult to write a small function in VBA to import your tab delimited Excel spreadsheet into Access.

Instructions

1. Start MS Access 2007 and select "Database Tools." Click on "Visual Basic" to open the "Microsoft visual Basic" window.

2. Create a new sub by typing "Sub importExcelfile()" and press "Enter."

   Type "DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, _

   "SpreadSheetImported," "C:\excelFileToImport.xlsx," True, "Sheet1!A1:D100"" inside your new sub.

3. Press "F5" to run your new sub. Switch windows to "Microsoft Access" and you will see a table named "SpreadSheetImported" under "All Tables."

Tips & Warnings

• Your new sub should look like this:

• "Sub importExcelFile()

• DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, _

• "TestData1", "C:\excelFileToImport.xlsx", True, "Sheet1!A1:D100"

• End Sub"

References

• Microsoft: DoCmd.TransferSpreadsheet Method