Macro for uploading data from other Excel files

[igorin]

I hope that you can help me:

I have daily reports for the past 6 years, i.e. over 2000 reports.

1) Each report has 4 sheets with a lot(!!!) of different data in them.
2) All the reports have the same format and structure. This means that the
data in the daily report for Oct 14th, 2006 is in the same cells as the same
data in the daily report for Nov 29th, 2009. This means that, for example,
the data for the volume is in cell B3 of sheet 2, in all of the daily reports.
3) All the daily reports have the same name structure, for example: Daily
Report 2007-05-28; but each one is named after it's corresponding date.
4) All the sheets in all of the daily reports are protected with a password
(I have the password).

I need to create a database where all the data from all of the daily reports
will be collected in columns and tables. For example: The data for the
volumes from all of the daily reports will be in a single column with it's
corresponding date in the first column; etc.

Can you, please, help me create a macro to import the data from each daily
report into the each one of the corresponding columns of the database without
me having to open each one of the two thousand daily reports that I have?

Thank you very very very much!!!

 

[Ron de Bruin]

Hi igorin

Start with this add-in
http://www.rondebruin.nl/merge.htm

And post back if this is what you need
Bed time for me so I will reply tomorrow

 

[ryguy7272]

Are these different files in different folders or all in the same folder?
Look here:
http://www.rondebruin.nl/tips.htm

You need the 'Copy/Paste/Merge examples'

I would think that you probably need to copy all the files in a folder into
one large workbook (2,000 pages, phew!). Then, copy all sheets into one
master sheet:
http://www.rondebruin.nl/copy2.htm

That should get you pretty far...maybe all the way. Try it and post back
with specific questions.

 

[igorin]

I will try it out today and let you know.

I will most probably have some questions. If you could help me further it
would be great!

Thank you very much, Ron!

 

[igorin]

Today I will start trying this out and then post the results and any
questions I might have.

Thank you very much for the help, Ryan!!!

 

[igorin]

Ron,

It works great. The only thing is that I still need to figure out how to
select and import the files because I need to have them in a specific order
in the database (for example, transpose some data, etc.) and so it will be a
little tricky with some of the data. But it sure helps a LOT(!!!) to be able
to get them all together. I'm sure that I will figure it out.
If I need anything else I will browse through your webpage or post a message
for you.
Thanks a million!!! You have been a great help!!!