INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access Howto:

Importing Excel 97 Files Into Access Using VB Code by famousb
Posted: 24 May 00

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"

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close