Combining Data from multiple excel pages

[M Thran]

Hi,
I have 150 excel 2007 worksheets with historic weather data, one for each
worksheet's region for the current month. All the worksheets are formatted
alike. I would like to combine the last seven days data from each worksheet
on an additional worksheet, and always have the last seven days data on the
combined page. I believe this would involve a formula, or array to search for
dates (now minus 1 to now minus 7) on each worksheet, and to copy the row's
contents to the new worksheet. Typically the data has a region id, date, then
the balance of the weather data. The data is from column "C" to column "P"
Each day the data is updated, adding yesterday's historic data to the bottom
of the columns (so the row is variable.) At the end of the month, the table
is replace with the new month, starting the data collected over again. I
would appreciate any advice you may be able to provide.

 

[BruceS]

Mike,

Are you trying to import this data into Access, or do this in Excel? If
it's just Excel, try the Excel "programming" discussion group. I've used it
in the past and there are some great people with even better answers to
questions.

If you're trying to use Access to get the data out of Excel and into Access
to store or manipulte it, then repost here with the details and maybe someone
can help.

Bruce

 

[Piet Linden]

Hi,
I have 150 excel 2007 worksheets with historic weather data, one for each
worksheet's region for the current month. All the worksheets are formatted
alike. I would like to combine the last seven days data from each worksheet
on an additional worksheet, and always have the last seven days data on the
combined page. I believe this would involve a formula, or array to searchfor
dates (now minus 1 to now minus 7) on each worksheet, and to copy the row's
contents to the new worksheet. Typically the data has a region id, date, then
the balance of the weather data. The data is from column "C" to column "P"
Each day the data is updated, adding yesterday's historic data to the bottom
of the columns (so the row is variable.) At the end of the month, the table
is replace with the new month, starting the data collected over again.  I
would appreciate any advice you may be able to provide.

If it were me, I would import all the data to an Access table and make
sure it's indexed properly. Then you wouldn't need Excel at all...
You can use something like
SELECT ...
FROM HistoryData
BETWEEN DateAdd(-7,Date(), "d") AND DateAdd(-1, Date(), "d")

then you don't have to worry about months or anything else.

Check out Ken Snell's website on how to import the spreadsheets...
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

Otherwise, you have to keep finding the file, linking to it (or
modifying your SQL on the fly), unioning all that together so you get
one dataset, and then query it... YUCK. Just lots of opportunities
for things to screw up and fail. If you import all the data, though,
all the problems should go away ...