Using queries to import data from multiple spreadsheets

[carmenlisa]

I need to import data from spreadsheets into Access on an ongoing basis. I know how to set up the queries themselves. But there are 2 things I do not know how to do.

1. I would normally tell the query where to look for its data, that is the name and location of the spreadsheet. But I will be given spreadsheets of unknown names, sometimes more than one at a time. Is there an easier way that taking the spreadsheets one by one, give each of them a pre-set name, run the queries, change the name of the spreadsheet, then rename the next spreadsheet with the pre-set name and keep cycling this way through the spreadsheets? Is there a way to tell the queries to run on all spreadsheets at a certain location, or with a certain date, or with part of the file name that is maybe the same?

Second, is there a way to tell a query to import the data from a spreadsheet only if, say, cell A1 in sheet1 equals 0? That is, this supposes that I already know how to import from all spreadsheets in a certain folder, or whatever, but that I only want to get data from those will the correct entry in a specific cell in the file?

 

[carmenlisa]

I would like to add to my original queston. In short, I want to run append queries in Access that will get data from an ongoing string of excel spreadsheets. By ongoing, I mean that they will continue to arrive over a period of time.

In time order, here is what I foresee happening:
1. On day 1 there will be a number of spreadsheets submitted. There will be all sorts of data in various tabs on the spreadsheets, all of which will be imported into (appended into) existing Access tables. We will save these spreadsheets in a specific folder on the computer. These Excel files will all have similar but not identical names.

2. My thought is that in some cell in the spreadsheets there could be an entry of zero (or whatever) that indicates that this spreadsheet has not yet been processed.

3. A series of Access append queries will look at all the spreadsheets we received, make sure there is a zero in the predetermined cell, and then bring across the data. We need to import all data from all spreadsheets, but it requires splitting it up to put it into a variety of Access tables and fields. The imported data will never go into a new table, will always go into existing tables.

4. Then we will change those zero's into number 1's to indicate that these spreadsheets are not processed.

5. Then on day 2 more spreadsheets will arrive and we will begin the process again.

Issues:
We could change the designated cells from 0 to 1 manually in the cell that indicates what spreadsheets have been processed. But having a person do this begs for a problem sooner or later.

We might want to put unprocessed spreadsheets into one folder and move them to a different folder once processed. It seems like this might be easier than using a cell to denote this information. Plus, it would avoid the process checking a whole of of files that have the number 1 in our cell and do not need to be imported.

So, my questions are:
How do I tell the append queries to run on all spreadsheets in a folder (or the more complex task of running it on all spreadsheets in a folder that have the zero in a certain cell)? And I guess a subquestion is that I have never done this without knowing specifically was the Excel file name is. (I really want to do this with append queries, not code).

How do I use an Access query to either change that cell value (from zero to 1 in my example) or to move the files from the folder for unprocessed files into the folder for previously processed files? Again, with the issue of not knowing in advance exactly what each of the files names is. Or is there a better way to make sure all spreadsheets get processed while also ensuring that none get processed twice?

My thought was to use a series of append queries, each getting specific data into specific Access tables, and then have a macro or small piece of code that runs these queries one after another, processing all of them for one spreadsheet, then moving on to the next spreadsheet are repeating the process. And stopping when there are no more spreadsheets to process.

I especially do not want to use the new process in Access as of the 2007 version that allows you to import data (an option in the ribbon) and then memorize the transaction. In my experience, you cannot edit these memorized imports. I do not want to rely on any processes that cannot be easily edited.