hi,

i need to create a weekly report pulling data from a
series of 6 daily reports which each exist in their
own .xls document. i want to create a macro to do this
automatically

what's the best way for me to access the data? should i
copy it into hidden tabs in my weekly report, or call it
directly from the existing files.

i would like to be able to enter the week ending date
(let's say "i"), and then have excel automatically gather
the week's data (i-0,1,2,3,4,5)which will populate the
formulas i create on the weekly page.

i'm just beginning my vba foray and would greatly
appreciate any help. thanks

Jay-
Will the source data files always be directly accessable from their "active"
location (e.g. on a server or your personal machine) or will copies of those
workbooks be sent to you as updates, for example by email?

If they are in a live location, and someone has it open when you are ready
to run your report, do you want to grab the data as it was when the file was
last saved, or wait until the next time the file is closed (assuming the
active user remembers to close it)?

Are the daily reports dependent on other processes (other files, systems,
etc) or could you potentially keep them permanently in your workbook, and
only expose the weekly report when you need to run it?

:-)
Keith

"Jay" wrote in message
...
hi,

i need to create a weekly report pulling data from a
series of 6 daily reports which each exist in their
own .xls document. i want to create a macro to do this
automatically

what's the best way for me to access the data? should i
copy it into hidden tabs in my weekly report, or call it
directly from the existing files.

i would like to be able to enter the week ending date
(let's say "i"), and then have excel automatically gather
the week's data (i-0,1,2,3,4,5)which will populate the
formulas i create on the weekly page.

i'm just beginning my vba foray and would greatly
appreciate any help. thanks

Thanks for the quick response Keith,

The files will always be in their active location.
However,I think it's better to import the data to the
weekly report and hide those tabs.

i recorded a macro which involved opening each file (by
pointing to it), selecting all the data in the appropriate
tab, copying it, pasting it into my "active" tab, then
returning to the daily file and closing it. I repeated
the process 5 times. It works perfectly

the source files are named by date ("DFR.040223.xls) where
the first two digits are the year, the middle two digits
are the month and the last two digits the day. i would
like to designate the month/day the week ends, then have
excel loop through the days including the week end day and
the five days previous to populate the six (hidden) tabs
in my weekly sheet.

possible?

thanks



-----Original Message-----
Jay-
Will the source data files always be directly accessable
from their "active"
location (e.g. on a server or your personal machine) or
will copies of those
workbooks be sent to you as updates, for example by email?

If they are in a live location, and someone has it open
when you are ready
to run your report, do you want to grab the data as it
was when the file was
last saved, or wait until the next time the file is
closed (assuming the
active user remembers to close it)?

Are the daily reports dependent on other processes (other
files, systems,
etc) or could you potentially keep them permanently in
your workbook, and
only expose the weekly report when you need to run it?

:-)
Keith

"Jay" wrote in message
...
hi,

i need to create a weekly report pulling data from a
series of 6 daily reports which each exist in their
own .xls document. i want to create a macro to do this
automatically

what's the best way for me to access the data? should i
copy it into hidden tabs in my weekly report, or call it
directly from the existing files.

i would like to be able to enter the week ending date
(let's say "i"), and then have excel automatically
gather
the week's data (i-0,1,2,3,4,5)which will populate the
formulas i create on the weekly page.

i'm just beginning my vba foray and would greatly
appreciate any help. thanks


.

Possible? = Yes but...

The file that you will be pinting to in your macro for each day will need to
built using a string that incorporates the year, month and day functions but
can you rely on this approach to gathering the data, I asssume that the user
creating the daily file enters the name?, what if it is not valid or entered
differently?

I would suggest you consider creating an application that each user accesses
that allows them though a programme to enter the data and for this data to
be stored and create your weekly summaries. This would not be a trivial
development but would ensure you gain control of a potentially difficult
data management task otherwise.

Sorry I cannot give you a packaged solution.

Cheers
Nigel


wrote in message
...
Thanks for the quick response Keith,

The files will always be in their active location.
However,I think it's better to import the data to the
weekly report and hide those tabs.

i recorded a macro which involved opening each file (by
pointing to it), selecting all the data in the appropriate
tab, copying it, pasting it into my "active" tab, then
returning to the daily file and closing it. I repeated
the process 5 times. It works perfectly

the source files are named by date ("DFR.040223.xls) where
the first two digits are the year, the middle two digits
are the month and the last two digits the day. i would
like to designate the month/day the week ends, then have
excel loop through the days including the week end day and
the five days previous to populate the six (hidden) tabs
in my weekly sheet.

possible?

thanks



-----Original Message-----
Jay-
Will the source data files always be directly accessable
from their "active"
location (e.g. on a server or your personal machine) or
will copies of those
workbooks be sent to you as updates, for example by email?

If they are in a live location, and someone has it open
when you are ready
to run your report, do you want to grab the data as it
was when the file was
last saved, or wait until the next time the file is
closed (assuming the
active user remembers to close it)?

Are the daily reports dependent on other processes (other
files, systems,
etc) or could you potentially keep them permanently in
your workbook, and
only expose the weekly report when you need to run it?

:-)
Keith

"Jay" wrote in message
...
hi,

i need to create a weekly report pulling data from a
series of 6 daily reports which each exist in their
own .xls document. i want to create a macro to do this
automatically

what's the best way for me to access the data? should i
copy it into hidden tabs in my weekly report, or call it
directly from the existing files.

i would like to be able to enter the week ending date
(let's say "i"), and then have excel automatically
gather
the week's data (i-0,1,2,3,4,5)which will populate the
formulas i create on the weekly page.

i'm just beginning my vba foray and would greatly
appreciate any help. thanks


.





----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---