How to import Excel data into a table

[DetRich]

Hello All,

I would like do read certificate data from a spreadsheet and insert the data
into an existing table. Spreadsheet filename will be a hostname.

1. promprt user for path/filename.
2. Assign hostname/filename to a variable. This will be inserted into an
existing table along with each row from the spreadsheet.
3. Open that spreadsheet file, read a row and insert data from each row
into an existing table. The hostname/servername is inserted at the same time.

CAn anyone help me figure out how to do this? It can't be that hard, but I
am stumped. I just don't know enough about Access to code it. I'd like the
process to be triggered by clicking a command button.

When reading from the spreadsheet, can each row be inserted by itself, or
will I have to insert all rows at one time? Is it better to have all the
necessay code behind the command button, or is it better to put all the code
in a module, and call that module from the command button_On_Click?

If anyone can point me to a good online resource so I can better
learn/understand how to do this, that would be great!

TIA,
Rich

 

[DetRich]

One more thing....

I can open the spreadsheet and add a column representing the
hostname/filename prior to importing into Access.

 

[Jack Leach]

1. promprt user for path/filename.

2. Assign hostname/filename to a variable. This will be inserted into an
existing table along with each row from the spreadsheet.

Obtaining a filename from a user can be done using the API found here:

http://mvps.org/access/api/api0001.htm

Putting this into a variable should be fairly self explainitory.


I can't advise how to import a specific row(s) from excel (I think it can be
done, just not sure how myself), but an alternative may be to import the
entire spreadsheet into a temp table, and work with the data from there. To
do this, check out the DoCmd.TransferSpreadsheet method.

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Ken Snell [MVP]]

These two articles will show you the code for the different things that you
want to do (you'll need to use code from both to get to your final result):

Browse to a single EXCEL File and Import Data from that EXCEL File via
TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpBrowseFile

Write Data From an EXCEL Worksheet into a Recordset using Automation (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#WriteFileRst