Multi-Import Excel files into Access

[Scott Reed via AccessMonster.com]

Here's what I have.
I have around 50 excel spreadsheets (all have nearly the same formated
columns) and I need to import and amend 1 table in access.

Now before you say anything, I know what your thinking, why not just take the
time to right-click, import and amend manually?

because....I need to do this procedure everyday.

part 2: after all of the files are amended into 1 table, I need to filter
out the table for certain criteria (this criteria changes daily, but remains
the same as far as the the data in the spreadsheet) for instance, the data
being imported has dates and times attached, so I only need 1 certain time
for each day.
Tables being imported:
Heading heading heading heading.....
datetime data data data....
(fairly simple layout on the spreadsheets being imported)

I tried to create a macro and use the "transferspreadsheet" function, but for
some reason, it doesn't recognize the headings after the first import...(even
though their identicle)

 

[Klatuu]

I would approach the problem a little differently.
First, I would use VBA because it is much more flexible than macros.
You can create a loop to import all the spreadsheets using the Dir function.

Rather than import, then manipulate, then append, etc. I would suggest you
link to the spreadsheets and create append and/or update queries to move the
data from the linked spreadsheets to your Aceess table.

 

[Scott Reed via AccessMonster.com]

Sounds good. Now all I have to do is Learn VBA.

I would approach the problem a little differently.
First, I would use VBA because it is much more flexible than macros.
You can create a loop to import all the spreadsheets using the Dir function.

Rather than import, then manipulate, then append, etc. I would suggest you
link to the spreadsheets and create append and/or update queries to move the
data from the linked spreadsheets to your Aceess table.

Here's what I have.
I have around 50 excel spreadsheets (all have nearly the same formated

[quoted text clipped - 22 lines]

import...(even
though their identicle)

 

[Klatuu]

It isn't as hard as you might think.
One way to get started is to wrte a macro to do as much as it will, then
convert it to VBA. You will then be able read how the VBA does it and gain
some understanding.

Also, there is a pretty good primar here:

http://www.accessmvp.com/Strive4Peace/Index.htm

Sounds good. Now all I have to do is Learn VBA.

I would approach the problem a little differently.
First, I would use VBA because it is much more flexible than macros.
You can create a loop to import all the spreadsheets using the Dir
function.

Rather than import, then manipulate, then append, etc. I would suggest
you
link to the spreadsheets and create append and/or update queries to move
the
data from the linked spreadsheets to your Aceess table.

Here's what I have.
I have around 50 excel spreadsheets (all have nearly the same formated

[quoted text clipped - 22 lines]

import...(even
though their identicle)