Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Importing Excel 97 Files Into Access Using VB Code

Access Howto:

Importing Excel 97 Files Into Access Using VB Code

by  famousb  Posted    (Edited  )
VB for importing of Excel 97 files from a static location, when the file name will not change:

The following names would need to be added or changed depending upon your database, but the punctuation would remain:

AccessTableName = The name of the table in your database to which you wish to add the records.
FileAndPathName = The full file and path name, including extensions, of the file you wish to import.
StartofRange = The first cell in the range of cells to import.
EndofRange = The last cell in the range of cells to import.
SheetName = The name of the specific worksheet from the Excel spreadsheet to import.

To import Excel 97 files the following code can be used if the entire first worksheet is to be imported and there are no Field Names on the worksheet. If there are Field Names, change the last "0" to "-1":
DoCmd.TransferSpreadsheet acImport, 8, "AccessTableName", "FileAndPathName", 0

If you wish to import a specified range from the first worksheet this formula should be used (same conditions apply for field names)
DoCmd.TransferSpreadsheet acImport, 8, "AccessTableName", "FileAndPathName", 0, "StartofRange:EndofRange"

For importing from a sheet other than the first, and using a specified range of cells, use this formula (same conditions apply for field names)
DoCmd.TransferSpreadsheet acImport, 8, "AccessTableName", "FileAndPathName", 0, "SheetName!StartofRange:EndofRange"
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top