automating multiple excel spreadsheet import into Access

[bsteagal]

Good morning!

I have a task that I require some help on... I've already read some of
the other posts concerning similar types of situations where multiple
spreadsheets are imported into an access database... and I can
understand some of it, but not all (newbie here) also, my
requirements are a little different than some of the others.

I have basically 200+ personal inventory spreadsheets that I have to
import into an access database. The information needed from these
spreadsheets are as follows:

1.- cell b10 holds the name of the person... this information is the
same on all forms - which will be used as a key field in the
database. Now, there might be more than one form for each person, as
different types of personal property are segregated into different
forms....

2- Row 21 holds the names of the fields such as serial#, asset code#
etc... from a to g
3.- Row 22 on down holds the data itself...

I understand you can use TransferSpreadsheet method to import the
files with a dir function to go through each of the files in a
specific directory... and there is another switch were you can specify
a range of cells to import from, but how do you (or can you) specify
that the information from a particular cell goes to a specific field?

So basically, I need a script that will take the information in cell
b10 to create the master key field, (name of individual) read the
information on rows 22 on down to add the information for that
individual, go on to the next excel spreadsheet and create a new
record.... so on..

I know this will not be easy, but if anyone could shed some info on
how to approach this, I will be a happy person...

Thanks in advance to all..

Bryan

 

[Klatuu]

Your requirements are a bit more complex than can be handled with a straight
forward TransferSpreadsheet, but not impossible. There actuall two different
ways you could do this.

The first would be to use Automation. That is using VBA to open the Excel
file, read in the data, and create record in the table. This, however,
requires a moderatly advanced knowledge of VBA.

The second can be done with TransferSpreadsheet, but takes a bit of
manipulation.
Based on your post, it would take two TransferSpreadsheets per file. Both
would involve using the Range argument of the TransferSpreadsheet method. It
would also take some VBA to accomplish.

First, you would need to import the value of B10 into a temporary table.
Then you would have to import starting with row 21 and use True as the
argument for Has Field names in the Transferspreadsheet. This should also go
in a temporary table. Then, you would need a query that would update the
table with the data with the key field. And the last step would be to append
the newly created data to your production table.