Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
Linking daily forms to Master Summary Form
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 |
#2
Posted to microsoft.public.excel.links
|
|||
|
|||
Linking daily forms to Master Summary Form
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 |
#3
Posted to microsoft.public.excel.links
|
|||
|
|||
Linking daily forms to Master Summary Form
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 |
#4
Posted to microsoft.public.excel.links
|
|||
|
|||
Linking daily forms to Master Summary Form
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Linking information form one worksheet to another | Excel Discussion (Misc queries) | |||
Linking One worksheet to Two different workbooks with Daily Dates. | Excel Discussion (Misc queries) | |||
Same cell added to master summary sheet | Excel Worksheet Functions | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) |