Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
creating many individual files using a template from one spreadshe
I'm trying to determine if I can do this in Excel. Here's the project: I
need to create separate profiles for 225 individual schools using data that is stored in one spreadsheet. The schools are listed in rows with corresponding data organized in columns. I have a template of tables and charts that will be the same for each school, with only the data points being imported that are differenet for each school. Myt question is, is it possible to write a set of commands in Excel that will generate 225 individual files with the data I want in tables and graphs? Or, do I need to set-up each of the 225 files separately and cut and paste or link it? My client assumes that Excel has a data-merge feature that is similiar to mail merge. I've never used Excel to generate this many individual reports and I can't determine how to do it without manually configuring each new file and creating each pie chart or table separately. Suggestions? Are there other programs that I should look into? Thank you, Meagan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
creating many individual files using a template from one spreadshe
Do you really need 225 WORKBOOKS, or just the ability to generate any of 225
different reports? In other words, are these file going to be sent out by your client, or does the client simply need to be able to see the template populated with a particular school's data? If it's the former, you'll need to use VBA code to, one line at a time, copy each line to the template, save it with the appropriate name, then open a new copy of the template, copy the next line, ... well, you get the picture. If it's the latter, use VLOOKUP to pull the appropriate data out of the database and into the template. "meaganbz" wrote: I'm trying to determine if I can do this in Excel. Here's the project: I need to create separate profiles for 225 individual schools using data that is stored in one spreadsheet. The schools are listed in rows with corresponding data organized in columns. I have a template of tables and charts that will be the same for each school, with only the data points being imported that are differenet for each school. Myt question is, is it possible to write a set of commands in Excel that will generate 225 individual files with the data I want in tables and graphs? Or, do I need to set-up each of the 225 files separately and cut and paste or link it? My client assumes that Excel has a data-merge feature that is similiar to mail merge. I've never used Excel to generate this many individual reports and I can't determine how to do it without manually configuring each new file and creating each pie chart or table separately. Suggestions? Are there other programs that I should look into? Thank you, Meagan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
creating many individual files using a template from one sprea
Thanks for the response. I need to end up with a product of 225 individual
profiles that I will likely transfer to Word or Pagemaker. These will go into a statewide report with each school having one page of tables and charts and school descriptives. From what I understand you're saying, this would have to be done manually by copying each school's data into a new template file and making charts for each individual school. I was hoping to be be able to create a do-sheet like in a stats program and run and sort the numbers based on a template. But no, eh? Thanks, Meagan "Duke Carey" wrote: Do you really need 225 WORKBOOKS, or just the ability to generate any of 225 different reports? In other words, are these file going to be sent out by your client, or does the client simply need to be able to see the template populated with a particular school's data? If it's the former, you'll need to use VBA code to, one line at a time, copy each line to the template, save it with the appropriate name, then open a new copy of the template, copy the next line, ... well, you get the picture. If it's the latter, use VLOOKUP to pull the appropriate data out of the database and into the template. "meaganbz" wrote: I'm trying to determine if I can do this in Excel. Here's the project: I need to create separate profiles for 225 individual schools using data that is stored in one spreadsheet. The schools are listed in rows with corresponding data organized in columns. I have a template of tables and charts that will be the same for each school, with only the data points being imported that are differenet for each school. Myt question is, is it possible to write a set of commands in Excel that will generate 225 individual files with the data I want in tables and graphs? Or, do I need to set-up each of the 225 files separately and cut and paste or link it? My client assumes that Excel has a data-merge feature that is similiar to mail merge. I've never used Excel to generate this many individual reports and I can't determine how to do it without manually configuring each new file and creating each pie chart or table separately. Suggestions? Are there other programs that I should look into? Thank you, Meagan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
creating many individual files using a template from one sprea
No, you misunderstood me - this is not a manual process.
Assumptions: Your template file is called SchoolReport.xls The range into which the data is copied is on Sheet1 and is named ReportData The database is in a separate workbook, on Sheet1 Copy this code into your database workbook (see David McRitchie's site for explanations about VBA code & where it goes: http://www.mvps.org/dmcritchie/excel/getstarted.htm ) WARNING - THIS WASN'T TESTED - USE IT ON A COPY OF YOUR DATA '----------------------------------------------------------------------------- Option Explicit Sub CreateSchoolReportWorkbook() ' put in your own filename and path here Const Template = "SchoolReport.xls" ' use your range name here Const TgtRange = "ReportData" ' change this to the # of columns of data in your database Const intCols = 10 ' the location to save the workbooks Const SavePath = "c:\schoolreports\" ' assumes the Filename you want to use is in the first column Const NameCol = 0 Dim rng As Range Dim wb As Workbook For Each rng In Selection Set wb = Workbooks.Add(Template) rng.Resize(1, intCols).Copy (wb.Worksheets("Sheet1").Range(TgtRange)) wb.SaveAs (SavePath & rng.Offset(0, NameCol).Text) wb.Close Next End Sub "meaganbz" wrote: Thanks for the response. I need to end up with a product of 225 individual profiles that I will likely transfer to Word or Pagemaker. These will go into a statewide report with each school having one page of tables and charts and school descriptives. From what I understand you're saying, this would have to be done manually by copying each school's data into a new template file and making charts for each individual school. I was hoping to be be able to create a do-sheet like in a stats program and run and sort the numbers based on a template. But no, eh? Thanks, Meagan "Duke Carey" wrote: Do you really need 225 WORKBOOKS, or just the ability to generate any of 225 different reports? In other words, are these file going to be sent out by your client, or does the client simply need to be able to see the template populated with a particular school's data? If it's the former, you'll need to use VBA code to, one line at a time, copy each line to the template, save it with the appropriate name, then open a new copy of the template, copy the next line, ... well, you get the picture. If it's the latter, use VLOOKUP to pull the appropriate data out of the database and into the template. "meaganbz" wrote: I'm trying to determine if I can do this in Excel. Here's the project: I need to create separate profiles for 225 individual schools using data that is stored in one spreadsheet. The schools are listed in rows with corresponding data organized in columns. I have a template of tables and charts that will be the same for each school, with only the data points being imported that are differenet for each school. Myt question is, is it possible to write a set of commands in Excel that will generate 225 individual files with the data I want in tables and graphs? Or, do I need to set-up each of the 225 files separately and cut and paste or link it? My client assumes that Excel has a data-merge feature that is similiar to mail merge. I've never used Excel to generate this many individual reports and I can't determine how to do it without manually configuring each new file and creating each pie chart or table separately. Suggestions? Are there other programs that I should look into? Thank you, Meagan |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
creating many individual files using a template from one sprea
Forgot to add that, to run the macro you need to be sure to select JUST THE
FIRST COLUMN of your database. Don't include the column header in the selection. When the appropriate cells are selected, go to Tools-Marco-Macros-and select CreateSchoolReportWorkbook from the list. "meaganbz" wrote: Thanks for the response. I need to end up with a product of 225 individual profiles that I will likely transfer to Word or Pagemaker. These will go into a statewide report with each school having one page of tables and charts and school descriptives. From what I understand you're saying, this would have to be done manually by copying each school's data into a new template file and making charts for each individual school. I was hoping to be be able to create a do-sheet like in a stats program and run and sort the numbers based on a template. But no, eh? Thanks, Meagan "Duke Carey" wrote: Do you really need 225 WORKBOOKS, or just the ability to generate any of 225 different reports? In other words, are these file going to be sent out by your client, or does the client simply need to be able to see the template populated with a particular school's data? If it's the former, you'll need to use VBA code to, one line at a time, copy each line to the template, save it with the appropriate name, then open a new copy of the template, copy the next line, ... well, you get the picture. If it's the latter, use VLOOKUP to pull the appropriate data out of the database and into the template. "meaganbz" wrote: I'm trying to determine if I can do this in Excel. Here's the project: I need to create separate profiles for 225 individual schools using data that is stored in one spreadsheet. The schools are listed in rows with corresponding data organized in columns. I have a template of tables and charts that will be the same for each school, with only the data points being imported that are differenet for each school. Myt question is, is it possible to write a set of commands in Excel that will generate 225 individual files with the data I want in tables and graphs? Or, do I need to set-up each of the 225 files separately and cut and paste or link it? My client assumes that Excel has a data-merge feature that is similiar to mail merge. I've never used Excel to generate this many individual reports and I can't determine how to do it without manually configuring each new file and creating each pie chart or table separately. Suggestions? Are there other programs that I should look into? Thank you, Meagan |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
creating many individual files using a template from one sprea
Thank you for all of your help! Right now I'm struggling through doing a
trial run macro. Yikes, Meagan "Duke Carey" wrote: Forgot to add that, to run the macro you need to be sure to select JUST THE FIRST COLUMN of your database. Don't include the column header in the selection. When the appropriate cells are selected, go to Tools-Marco-Macros-and select CreateSchoolReportWorkbook from the list. "meaganbz" wrote: Thanks for the response. I need to end up with a product of 225 individual profiles that I will likely transfer to Word or Pagemaker. These will go into a statewide report with each school having one page of tables and charts and school descriptives. From what I understand you're saying, this would have to be done manually by copying each school's data into a new template file and making charts for each individual school. I was hoping to be be able to create a do-sheet like in a stats program and run and sort the numbers based on a template. But no, eh? Thanks, Meagan "Duke Carey" wrote: Do you really need 225 WORKBOOKS, or just the ability to generate any of 225 different reports? In other words, are these file going to be sent out by your client, or does the client simply need to be able to see the template populated with a particular school's data? If it's the former, you'll need to use VBA code to, one line at a time, copy each line to the template, save it with the appropriate name, then open a new copy of the template, copy the next line, ... well, you get the picture. If it's the latter, use VLOOKUP to pull the appropriate data out of the database and into the template. "meaganbz" wrote: I'm trying to determine if I can do this in Excel. Here's the project: I need to create separate profiles for 225 individual schools using data that is stored in one spreadsheet. The schools are listed in rows with corresponding data organized in columns. I have a template of tables and charts that will be the same for each school, with only the data points being imported that are differenet for each school. Myt question is, is it possible to write a set of commands in Excel that will generate 225 individual files with the data I want in tables and graphs? Or, do I need to set-up each of the 225 files separately and cut and paste or link it? My client assumes that Excel has a data-merge feature that is similiar to mail merge. I've never used Excel to generate this many individual reports and I can't determine how to do it without manually configuring each new file and creating each pie chart or table separately. Suggestions? Are there other programs that I should look into? Thank you, Meagan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel creating temp files upon save. | Setting up and Configuration of Excel | |||
printing - creating new files on each page break | Excel Worksheet Functions | |||
creating multiple files from a spreadsheet | Excel Discussion (Misc queries) | |||
Creating PDF files from Excel. | Excel Discussion (Misc queries) | |||
Creating Individual Excel Files from Sheets | Excel Discussion (Misc queries) |