create an acess report from multiple exel files

[p16458]

Hello guys,

I have billing statements that are all done in exel with a template and
stored in folders by month order.
At the end of the month, I need to make a report giving: name, account
number, payment due. This information is stored in the same cell in each bill
as a template is used.
I am doing it manually by opening each individual exel file and copy
information and paste to another spreadsheet to create my report.

Is it possible to extract some data from multiple exel files and create a
report?
Does anyone have any idea how to work around this? I really appreciate your
time and help.

Kim

 

[Jeff Boyce]

Kim

I can't be sure from your description, but it sounds like the "folders" and
Excel files have different names.

You could use an Access .mdb file to "link" to each of those, but if the
names change, you'd have to "link" to the new files/folders each time (this
might be automated, but it is a not-insignificant programming effort).

If you have an Access .mdb file with links to each/all the spreadsheets, you
might consider using UNION queries to pile all the data together (since each
month's data is apparently in a different place).

You could consider using automation from within Access to "gather together"
the data from all those Excel files, placing it in a well-normalized table
structure (individual monthly Excel files is not a well-normalized
structure).

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[p16458]

Jeff,

Thank you very much for your message.

Let me explain a little bit more about this. I have about 200 bills (200
exel files) for 200 borrowers each month. Each exel file named by borrower's
name. And I save all those exel files in 1 folder name by month (eg: 12-2007,
1-2008). So the folders and exel files have different names.

So, for January's report I need to pull up some information from each exel
file in 1-2008 folder and create my report.

Could you explain more about UNION queries? Actually I just knew about
access 5 days ago. So I do not know a lot about that. If you could give me
some ideas about code/macro to pile all the data together I would REALLY
APPRECIATED. It will help me a lot because I am doing manually right now by
copying and pasting.

Thanks so much for your time Jeff

Regards,

Kim

 

[Jeff Boyce]

Access has a bit of a steep learning curve, and is NOT just a beefy
spreadsheet.

Whatever data structure you might have been using for Excel (?!one file per
customer per payment?!), you will probably need to spend time learning about
relational database design and normalization. If these terms are
meaningless, you will find that both you and Access will struggle to do
things that would be otherwise simple, if your data were well-normalized.

If you have no/limited experience with Access, I'm wondering why you want to
switch from something you already know and use...?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/