Automatic import button

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey everyone -

I am working with Access 2000 and trying to code a button that will allow users to import information from an Excel Spreadsheet, into 2 different tables. There are 3 named ranges in the Excel file, 2 going to one table, and one going to the toher. I want to code it so that all the other variables will be filled in for the user (which are made up of mostly beginning computer users) so that all they have to do is select the file to imported. Is there any way to code this using either the Transfer Spreadsheet or Run Command Import function?

Any help would be appreciated!! Thanks!!
 
Dubs70 said:
I am working with Access 2000 and trying to code a button that will allow users to import information from an Excel Spreadsheet, into 2 different tables. There are 3 named ranges in the Excel file, 2 going to one table, and one going to the toher. I want to code it so that all the other variables will be filled in for the user (which are made up of mostly beginning computer users) so that all they have to do is select the file to imported. Is there any way to code this using either the Transfer Spreadsheet or Run Command Import function?
Click to expand...


You can probably do it with TransferSpreadsheet, but I
prefer to execute an SQL statement:

Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb()
strSQL = "INSERT INTO yourtable " _
& "SELECT * " _
& "FROM [namedrange] " _
& "IN '' [Excel 9.0;HDR=NO;DATABASE=pathtoxlsfile]"
db.Execute strSQL
Set db = Nothing

Note the two ' marks after the IN keyword. You might need
to change the 9.0 to the version number of Excel that you're
using and, depending on your ranges, you may also want to
use HDR=YES.
 
Well, I think I figured it all out anyway!! Thanks again for all your help!! I can't say enough how awesome these news groups are. Thanks all you gurus for helping others a long!

Sara
NGIT
 
Back
Top