[rjr]

Hello,



I have Excel 2003 and have this problem that I hope someone can assist me
with.

I have to export approximately 20 worksheets daily from a mainframe computer
and they are exported in CSV format. Each worksheet is data for 1 day of the
month.



I have 5 centers (or groups of people) that have their own statistics. So I
have to export daily at least 100 worksheets grouped by each individual
centers. The centers are Center 1; Center 2; Center 3; Center 4; & Center 5.



I'm thinking that I will make a Master Center file folder containing up to
31 (depending on how many days in the month) individual folders (based on
the day of the week). I would download the exported information and for each
day of the week place them in the individual folders and always follow
through with the same name all the time, just change the folders.



Once the worksheets are downloaded I will have a report inside the daily
folder that will be linked to these individual worksheets for a summary of
each days activity. Since the information will vary from day to day I have
no need to have a monthly summary at the end of the month..



Is there and easier way to assemble the information that I've outlined above
in excel and/or does anyone have any comments based on their skills that
could help me make it better.



Thanks so much to all that help with these message boards and I hope someone
has some ideas?



BobReynolds

[[email protected]]

Bob,

You haven't described the structure of the data you're downloading
per Center/day. Is it one column or many? Is it always the same set of
columns per day, and/or per center?
Generally it would be far easier to let the mainframe store the
data and just use Excel to query the data you need when you need it. A
critical question thus becomes: what kind of database software is
running on the mainframe? While it would take a bit of VBA magic for a
front-end UserForm to specify the data you want downloaded and how you
want it summarized (in order to generate the required SQL statement)
assuming your mainframe software allows an interface to VBA, it would
be whole bunches easier over the long term to do it that way than
create and maintain large number of Excel worksheets or files to hold
the data which already exists somewhere else.This type of adhoc
query/front-end structure would also let your Master Center numbers
automatically inherit any data eidts/changes done to the mainframe data
after the initial download -- something virtually impossible to do if
you're maintaining a separate, parallel datastore in Excel files. And
if both your data and your summary needs are simple enough, you might
even look to Access (gasp!) as a front-end.
Assuming worst case -- the only way you'll ever get data from the
mainframe is static CSV data loaded into Excel -- do you need to store
them separately after you've summarized the data into your Master
Center file? (Law of the universe: parallel databases aren't). If all
you need is summary data, or if you can re-create the mainframe
extracts on demand, you might seriously think of dumping a complex
directory/file structure in favor of cumulating each center/days'
extracts into a Master file on a daily basis, then dumping the raw
mainframe extracts.
Which I guess is really a long way of asking for just a few more
details about what you're trying to accomplish here. I'm particularly
curious when you write "the information will vary from day to day". Is
this substance (assumed) or structure?

= Marchand =




rjr wrote:
Hello,



I have Excel 2003 and have this problem that I hope someone can assist me
with.

I have to export approximately 20 worksheets daily from a mainframe computer
and they are exported in CSV format. Each worksheet is data for 1 day of the
month.



I have 5 centers (or groups of people) that have their own statistics. So I
have to export daily at least 100 worksheets grouped by each individual
centers. The centers are Center 1; Center 2; Center 3; Center 4; & Center 5.



I'm thinking that I will make a Master Center file folder containing up to
31 (depending on how many days in the month) individual folders (based on
the day of the week). I would download the exported information and for each
day of the week place them in the individual folders and always follow
through with the same name all the time, just change the folders.



Once the worksheets are downloaded I will have a report inside the daily
folder that will be linked to these individual worksheets for a summary of
each days activity. Since the information will vary from day to day I have
no need to have a monthly summary at the end of the month..



Is there and easier way to assemble the information that I've outlined above
in excel and/or does anyone have any comments based on their skills that
could help me make it better.



Thanks so much to all that help with these message boards and I hope someone
has some ideas?



BobReynolds

[rjr]

Marchand,
Thank you for responding and I really appreciate it. I've managed to get
together with the IT person at work and am letting them work out something
for me. That will be a preformatted report that I just push a button. So I
can't see a reason to continue the way I was looking at. Your questions
caused me pause and since he can do, so be it.
But again thanks so much
BOB REYNOLDS

wrote in message
oups.com...
Bob,

You haven't described the structure of the data you're downloading
per Center/day. Is it one column or many? Is it always the same set of
columns per day, and/or per center?
Generally it would be far easier to let the mainframe store the
data and just use Excel to query the data you need when you need it. A
critical question thus becomes: what kind of database software is
running on the mainframe? While it would take a bit of VBA magic for a
front-end UserForm to specify the data you want downloaded and how you
want it summarized (in order to generate the required SQL statement)
assuming your mainframe software allows an interface to VBA, it would
be whole bunches easier over the long term to do it that way than
create and maintain large number of Excel worksheets or files to hold
the data which already exists somewhere else.This type of adhoc
query/front-end structure would also let your Master Center numbers
automatically inherit any data eidts/changes done to the mainframe data
after the initial download -- something virtually impossible to do if
you're maintaining a separate, parallel datastore in Excel files. And
if both your data and your summary needs are simple enough, you might
even look to Access (gasp!) as a front-end.
Assuming worst case -- the only way you'll ever get data from the
mainframe is static CSV data loaded into Excel -- do you need to store
them separately after you've summarized the data into your Master
Center file? (Law of the universe: parallel databases aren't). If all
you need is summary data, or if you can re-create the mainframe
extracts on demand, you might seriously think of dumping a complex
directory/file structure in favor of cumulating each center/days'
extracts into a Master file on a daily basis, then dumping the raw
mainframe extracts.
Which I guess is really a long way of asking for just a few more
details about what you're trying to accomplish here. I'm particularly
curious when you write "the information will vary from day to day". Is
this substance (assumed) or structure?

= Marchand =




rjr wrote:
Hello,



I have Excel 2003 and have this problem that I hope someone can assist me
with.

I have to export approximately 20 worksheets daily from a mainframe
computer
and they are exported in CSV format. Each worksheet is data for 1 day of
the
month.



I have 5 centers (or groups of people) that have their own statistics. So
I
have to export daily at least 100 worksheets grouped by each individual
centers. The centers are Center 1; Center 2; Center 3; Center 4; & Center
5.



I'm thinking that I will make a Master Center file folder containing up
to
31 (depending on how many days in the month) individual folders (based on
the day of the week). I would download the exported information and for
each
day of the week place them in the individual folders and always follow
through with the same name all the time, just change the folders.



Once the worksheets are downloaded I will have a report inside the daily
folder that will be linked to these individual worksheets for a summary
of
each days activity. Since the information will vary from day to day I
have
no need to have a monthly summary at the end of the month..



Is there and easier way to assemble the information that I've outlined
above
in excel and/or does anyone have any comments based on their skills that
could help me make it better.



Thanks so much to all that help with these message boards and I hope
someone
has some ideas?



BobReynolds

[[email protected]]

Bob,

Glad your IT person could give you one of those 'magic buttons' we
all find so useful. Keep track for folks like that!

= Marchand =


rjr wrote:
Marchand,
Thank you for responding and I really appreciate it. I've managed to get
together with the IT person at work and am letting them work out something
for me. That will be a preformatted report that I just push a button. So I
can't see a reason to continue the way I was looking at. Your questions
caused me pause and since he can do, so be it.
But again thanks so much
BOB REYNOLDS

wrote in message
oups.com...
Bob,

You haven't described the structure of the data you're downloading
per Center/day. Is it one column or many? Is it always the same set of
columns per day, and/or per center?
Generally it would be far easier to let the mainframe store the
data and just use Excel to query the data you need when you need it. A
critical question thus becomes: what kind of database software is
running on the mainframe? While it would take a bit of VBA magic for a
front-end UserForm to specify the data you want downloaded and how you
want it summarized (in order to generate the required SQL statement)
assuming your mainframe software allows an interface to VBA, it would
be whole bunches easier over the long term to do it that way than
create and maintain large number of Excel worksheets or files to hold
the data which already exists somewhere else.This type of adhoc
query/front-end structure would also let your Master Center numbers
automatically inherit any data eidts/changes done to the mainframe data
after the initial download -- something virtually impossible to do if
you're maintaining a separate, parallel datastore in Excel files. And
if both your data and your summary needs are simple enough, you might
even look to Access (gasp!) as a front-end.
Assuming worst case -- the only way you'll ever get data from the
mainframe is static CSV data loaded into Excel -- do you need to store
them separately after you've summarized the data into your Master
Center file? (Law of the universe: parallel databases aren't). If all
you need is summary data, or if you can re-create the mainframe
extracts on demand, you might seriously think of dumping a complex
directory/file structure in favor of cumulating each center/days'
extracts into a Master file on a daily basis, then dumping the raw
mainframe extracts.
Which I guess is really a long way of asking for just a few more
details about what you're trying to accomplish here. I'm particularly
curious when you write "the information will vary from day to day". Is
this substance (assumed) or structure?

= Marchand =




rjr wrote:
Hello,



I have Excel 2003 and have this problem that I hope someone can assist me
with.

I have to export approximately 20 worksheets daily from a mainframe
computer
and they are exported in CSV format. Each worksheet is data for 1 day of
the
month.



I have 5 centers (or groups of people) that have their own statistics. So
I
have to export daily at least 100 worksheets grouped by each individual
centers. The centers are Center 1; Center 2; Center 3; Center 4; & Center
5.



I'm thinking that I will make a Master Center file folder containing up
to
31 (depending on how many days in the month) individual folders (based on
the day of the week). I would download the exported information and for
each
day of the week place them in the individual folders and always follow
through with the same name all the time, just change the folders.



Once the worksheets are downloaded I will have a report inside the daily
folder that will be linked to these individual worksheets for a summary
of
each days activity. Since the information will vary from day to day I
have
no need to have a monthly summary at the end of the month..



Is there and easier way to assemble the information that I've outlined
above
in excel and/or does anyone have any comments based on their skills that
could help me make it better.



Thanks so much to all that help with these message boards and I hope
someone
has some ideas?



BobReynolds