evaluate file before import in MS Access

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Hi, maybe someone can help me with the following: on a weekly basis I
need to import 3 Excel files into an existing empty table (I run a
delete query on this table before the import). The Excel files are
stored in the same folder every week. Each file has a field showing
[Current Year/Week No]. The three files show the current week for this
year, previous year and the year before the previous year. How can you
instruct MS Access (2003) to look up this field and to import only the
approrpiate files? I do have a calender function from which the user
can select the date (and deduct the 2 previous dates). I should be
able to incorporate that into the code (with a little help...).
Any ideas? Let me know if you need more info, thanks a million, Paul
 
Are you saying that each week 3 new files are added to the folder and those
files stay in the folder indefinitely?
How are the files named? Is it possible you could use a naming convention
that you could use to programmatically determine the names of the files to
import?

If it is possible to use a naming convention that would include the year and
week of the data it would make it very easy; otherwise, what you will have to
do is use Automation. That is, you will have to create an exel application
object, open each file and check the value of the cell, then close the file.
If you are continually adding 3 files per week, that would be 156 files per
year and over a 3 year period it would be 468 files. It would be time
consuming to interate through that many files.
 
Are you saying that each week 3 new files are added to the folder and those
files stay in the folder indefinitely?
How are the files named? Is it possible you could use a naming convention
that you could use to programmatically determine the names of the files to
import?

If it is possible to use a naming convention that would include the year and
week of the data it would make it very easy; otherwise, what you will have to
do is use Automation. That is, you will have to create an exel application
object, open each file and check the value of the cell, then close the file.
If you are continually adding 3 files per week, that would be 156 files per
year and over a 3 year period it would be 468 files. It would be time
consuming to interate through that many files.
Click to expand...

Hi Dave, thanks very much for your reply. Actually, the report makes
use of 8 files a week, but I do intend to clear the folder about once
every month.The 3 files I need I can select by (part of) the name.
Within this selection I still need to distinguish. Would the 'excel
application object' be feasible for 30 odd files?
The problem with the naming convention is that I do not always produce
these reports myself, so it is error prone. Within the company I am
the only one with a bit of Access knowledge so we might have a problem
when I'm not in the office.

On another note (group ethics): I'm new to this group thing (though
very excited about it) and I deleted this question to post it in
another group (microsoft.public.access). I was not assuming you (or
anyone) would reply that soon. Since you did (for which I'm thankful)
I need to tell you that I also got a reply from Lance. Now can I bring
you guys together somehow or how does that work? I copied this answer
to the other group now, the subject there is " Conditional import of
excel Sheets".
Thanks very much again, Paul
 
With 30 files, it wouldn't be too bad and if you can't control the naming,
then it would not be too bad.

Create an Excel Application object.
Use the Dir function to loop through all the excel files.
Open each file and query the value in the cell.
Close the file (workbook object)
Import it if it meets the criteria.
Close the Excel Applicatioin.
 
Hi Dave, thanks again. You got me thinking this over again: the files
I do not need all have in common that they were downloaded on a
different date than the files I do need, which will always be
downloaded the same day. Could I use the date criteria somehow as a
condition to import or not? Thanks, paul
 
You can use a combination of the Dir function and the FileDateTime function.
Use the Dir function to loop through the files in the folder and use the
FileDateTime function to test each file.
 
Back
Top