Importing a Spreadsheet
You can easily learn how to have Microsoft Access Visual Basic read from and write to .xls files. One of the simplest things you can do to integrate Excel and Access is to import an .xls spreadsheet file into Access as a new or existing table. There's already a Bright Hub guide to tell you how to do this, but if you have the need to use VBA, then you'll need this article. The simplest way to do it is to use the following command:
Docmd.TransferSpreadsheet <TransferType>, <SpreadsheetType>, <TableName>, <FileName>, <HasFieldNames>, <Range>
You'll need to replace the terms in angled brackets with the relevant data as follows:
<TransferType> - Choose from a dropdown when entering the command. You'll want acImport to import data from a spreadsheet.
<SpreadsheetType> - Again, you'll be shown a dropdown when you get to this part of the command. The choices you see will depend on the version of Access you are running, but acSpreadsheetTypeExcel9 refers to Excel 2003 .xls files.
<TableName> - Enter the name of the table you wish to import the data into. If it doesn't exist, it will be created; if it does exist, then the field names must match (or at least the number of columns must match). Existing data in the table will not be deleted.
<FileName> The full filepath and name of the Excel spreadsheet you wish to import.
<HasFieldNames> True or False, if True then the top row of the spreadsheet will be imported as fieldnames; if False, it will be imported as another row of data.
<Range> If you only wish to import certain rows or columns, you can enter the range here. You can also specify a worksheet here. If you don't, the leftmost worksheet will be used (even if it's hidden).
Docmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Test", "C:\Users\Admin\Documents\Work\Spreadsheet.xls", True, "March!A:H"
The above example will import Columns A-H of the March worksheet of the Spreadsheet.xls Workbook into a table called Test. Row 1 will be used to determine fieldnames for the table.