creating many individual files using a template from one spreadshe

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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
 
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.
 
Thank you for all of your help! Right now I'm struggling through doing a
trial run macro.

Yikes, Meagan
 
Back
Top