programmatic linking to external tables

B

Ben

Hi all,

I have a directory structure that is like:

/year/month

and within this structure, there are files with date suffixes like:
filename_MMDDYYY.xls

I need to extract a subset of the data in a particular tab each day.
I like to ask, if there's an programmatic way to link and query each day's
data, since the directory structure is static and the file naming convention
is consistent. I would like to like to the file dynamically and query it and
append the new data from each day into a table and then unlink it the file.

Can you please share some ideas/ codes snippets? Thank you in advance.

Ben

--
 
C

Chaim

Ben,

Although it is recommended that one link to external DB files, there is SQL
syntax that looks like:

IN "c:\documents\xldata.xls" "EXCEL 5.0;"


where the first argument is the name of the file and the second is the
generator of the file. I have not used this and I don't know how Excel
stores its worksheets, so I can't tell you how to set this up to get an
individual days worth of data.

But hope this points you in a direction to explore. Follow the links in
Access Help for the "IN Clause" (not IN operator).

Good Luck!
 
J

John Gray

Hi,

You might want to have a look at the "TransferSpreadsheet" action in the VBA Help.

If you combine that with this FileOpen Dialog code,
http://www.mvps.org/access/api/api0001.htm
you will have a semi-automated process.

I use this to bring in daily sales data from an Excel file to a temp table. There it
gets massaged, then appended to the Sales table. Works really well

hth,
John

---------------------


"Ben" wrote

[...]
 
Top