Importing Data From an Excel SS using VBA (Access 2000 SR1)

Topic

New Reply

Hello,

I am trying to write a procedure that would allow the user to navigate out to an excel spreadsheet and import it into the DB. The spreadsheet has a consistent format that matches the table that it will be imported into. What is the best way to do this.

In Excel, you can use the “Application.GetOpenFilename” function to navigate and select files.

Any suggestions will be appreciated.

Regards,

Reply | Quote

Replies

There is a TransferSpreadsheet command which should do what you want.
Pat

Reply | Quote

Access didn’t use to have the equivalent of GetOpenFilename (I think I read somewhere that Access XP has one, but I’m not sure). I use API calls to present an “Open” dialog to the user. See for instance post 188202.

Reply | Quote

I often use the File Import function to import a spreadsheet into Access. I always give it the same name. (ok) Then run an append Query to attach it to the existing table.
Is this anything like what you are trying to do?

Reply | Quote

Yes, this is exactly what I am trying to do. But I want to provide an interface so that the user can navigate to the file that will be imported. Once the file is selected, the Import should be automatic.

Thanks,

Reply | Quote