Macro to go thru directory of Excel files

Looking for someway to go thru a directory (with or without sub folders) and for every Excel file it finds, open the file and Copy - Paste Special Values each worksheet in the workbook. I wouldn't know how many sheets any given workbook may have until it opened. Can this be done? Copy and paste part is easy enough but hoping not to have to have someone open each one and then run a copy paste macro for each sheet and workbook.

Hi,

Try this:

Please Login or Register  to view this content.

Change the directory to search as required.

Dom

Hi,

You can use this, this doesn't take into account subfolder, so you may have to loop threw directories, but adjust the code arcordingly.

Please Login or Register  to view this content.

Be aware if you have many files it will be a heavy procedure.

Steffen Thomsen

snb posted examples of how to loop through directories here which includes code to take account of subfolders:

http://www.excelforum.com/tips-and-t...ernatives.html

Dom

Hi stvgarner

I was able to adapt Leith Ross' code from this link to do something similar to what you describe
http://www.excelforum.com/excel-prog...ml#post2474970

Hi stvgarner
Glad to have been of help (if I was).