Generating reports from multiple excel files

[jimteacher]

Hi!
I'm a teacher at a high school with 1800 students.

The attendance lady types up an excel file each day
listing the tardies and early outs, etc. for each student
who comes in on that day for a pass.

This would end up as 180 excel files a year.

She would like to be able to search for a particular
student's name throughout all files to generate a
report which would tell her how many early outs, etc.
(stuff that isn't in the regular absence report scanned
in once a day) that one particular student
has had. Now she has to do this manually, looking
through paper files for each student.

Can one search be done through multiple files with
a macro?

Or can all this info be transferred each day to another
excel file (combining the info into one file), cumulatively,

or can it be put into an access file so that it can be
queried?

She isn't into complexities, so it has to be pretty easy,
which is why come kind of a macro would help.

Thanks for any help you can give us, as it's a royal
pain to go though hundreds of paper files.

Jimteacher

 

[Roger Govier]

Hi

I have to go out right now, so I don't have time to write the code, but
if you go to Ron de Bruin's site there are plenty of examples under the
section Copy/Paste?Merge examples of how to combine data from several
workbooks.
http://www.rondebruin.nl/tips.htm

If each file contains 1800 rows, one for each pupil, then you would not
be able to import it into one Excel file (other than XL2007) as there is
a limit of 65536 rows per sheet.

However, even if your files are set this way, there would be less than
10% of pupils that had entries for any day so after each import, you
should sort by the column containing entries, so all the blank ones fall
to the end. Delete the "empty" rows before importing the next file to
the first available blank row.

You would need to amend some of Ron's code to carry out this task before
the next import.
For each file that you read in, add the date of that file as an extra
column, if the date hasn't already been recorded as part of the data
imported.

All 180 files, should easily fit to one Excel sheet if you follow this
pattern, and you can then filter by student name to se a list of all
absences, tardiness etc.

 

[Otto Moehrbach]

jimteacher
Yes, that can be done with VBA (a macro). But why is she doing this in
a new file each day? I would suggest one file with maybe 12 sheets, one for
each month. The file could have the year as part of its name. A 13th sheet
would be an Input/Query sheet. She would input the necessary data for one
student and click on a button. VBA would then place that data in the proper
sheet. She would repeat that for each student. The same sheet can be used
for a Query sheet. She would enter the student's name and click on another
button and what she wants would be displayed.
VBA can also be used to build such a file from her 180 files if she
wants to consolidate all those files. Post back with specifics about the
data layout and specifics about what you would like to do. HTH Otto

 

[Lori]

Need more info as to the layout, but if you have all files in a 2006
folder then you could start by:

1. Choose Data > Consolidate and then:

- Click browse, select the 2006 folder so it is current then click
cancel.
- Enter the range (with a * for all excel files) e.g.
'[*.xls]Sheet1'!$A$1:$G$60.
- Select function: Count, Create links to Source Data: Check other
options un checked

This at least brings all data into one place but it is summarised and
in outline format, so you then need to remove summary lines by:

2. Edit > Relace Find: "Counta(" Replace by: "Subtotal(3,"
3. Data > Subtotals > Remove All

Finally reorder data by date (if there is a date column) and make any
further changes necessary to make the layout tabular so you can set up
filters and pivot tables.

 

[jimteacher]

Thanks so much for so many options!
I'll get a copy of her file and try out your suggestions.
I'll get back to you if I need more good advice.
It'll be fun to see which one fits the best.
You're great!
Jim

 

[jimteacher]

Hi!
Here's the file. It's just little. There might be 40 or 50 students who
get early outs.
I don't know how to insert tables in usenet group postings.

The only one she needs to draw reports from is the F and G columns, and
maybe H, though she has nothing in this one: She needs reports on Early
outs: names and times out.

She puts these up as separate files in a folder on the server for
others to see at the end of each day, so the search for the report on
the number of times a student has gotten an early out must be through
all of the little files, perhaps up to 180.

A B C D E F G
TARDIES 11-14-06 SUSPENSIONS EARLY OUTS OUT


Axman, KATIE 8:51 TE Biggers, MARIANO BRACKLEY, PATRICK 1:55
Beringer, CHELSEA 8:12 TU Campbell, George BOSCOE, ANTHONY 1:35

She wants to be able to search through all of the files to get a report
on an individual student, and maybe even to get a complete report of
all the early outs by all students to see if there are patterns.

Thanks for any help you can give me.
Jim

 

[jimteacher]

Hi!
Please check out my addition with file details.
I'd be grateful for suggestions.
Jim

 

[jimteacher]

Hi!
I've put a copy on the file up in another reply.
Please check. I'd be very grateful for help.

Need more info as to the layout, but if you have all files in a 2006
folder then you could start by:

1. Choose Data > Consolidate and then:

- Click browse, select the 2006 folder so it is current then click
cancel.
- Enter the range (with a * for all excel files) e.g.
'[*.xls]Sheet1'!$A$1:$G$60.
- Select function: Count, Create links to Source Data: Check other
options un checked

This at least brings all data into one place but it is summarised and
in outline format, so you then need to remove summary lines by:

2. Edit > Relace Find: "Counta(" Replace by: "Subtotal(3,"
3. Data > Subtotals > Remove All

Finally reorder data by date (if there is a date column) and make any
further changes necessary to make the layout tabular so you can set up
filters and pivot tables.

Hi!
I'm a teacher at a high school with 1800 students.

The attendance lady types up an excel file each day
listing the tardies and early outs, etc. for each student
who comes in on that day for a pass.

This would end up as 180 excel files a year.

She would like to be able to search for a particular
student's name throughout all files to generate a
report which would tell her how many early outs, etc.
(stuff that isn't in the regular absence report scanned
in once a day) that one particular student
has had. Now she has to do this manually, looking
through paper files for each student.

Can one search be done through multiple files with
a macro?

Or can all this info be transferred each day to another
excel file (combining the info into one file), cumulatively,

or can it be put into an access file so that it can be
queried?

She isn't into complexities, so it has to be pretty easy,
which is why come kind of a macro would help.

Thanks for any help you can give us, as it's a royal
pain to go though hundreds of paper files.

Jimteacher

 

[Roger Govier]

Hi Jim

I have looked at your other post, but because of wrapping in the NG
posts it is difficult to see what the data really looks like.
(Posting files direct to NG's is not desirable, and few people will open
such postings).

As Otto said in his response, and I should have added in mine,
consolidating the data to a single file can be done with VBA, but this
should really be a one off task. All data entry for the future should
then only be to the one file each day for the whole of that academic
year, then start a new file for the next year.
With only 40 - 50 entries per day we are only talking of around 9000
lines for the year, which will all sit quite nicely on one sheet (up to
65536 rows available)

On a separate sheet, I would suggest a full list of all students and
forms, which could then be used as source data for dropdowns to make
data entry easier, and ensure consistency in spelling of student names.

The task of looking at the information for any pupil than becomes a very
simple filtering task.

If you would like to mail direct to me, copies of say 3 daily files, I
would be happy to write some code for you which you could then use to
consolidate, and set up the file as I would suggest for future use.

To email direct, remove NOSPAM from my email address.