Loop to open excel files in a folder

Hiya,

I just wanted to find out if it would be possible to get a macro to loop through an entire folders files (excel) open them, copy some data over, then close them. (100's) of them

This is on the assumption you dont know the exact string name of these files.

The data is all in same format.

What i kinda need is it to list the filenames into a colum, then i can get my macro to go down the list one by one...

I have the code to open a workbook & i know how to use counters to loop through the operation, just cant think of how to get all the workbook names.

Any help would be greatly appriciated.

Cheers,

Has anybody had a similar issue?

Originally Posted by Ryan Murtagh

Use the GetOpenFilename method and allow the user to select the folder and workbooks on their own. The filenames that the user selects will be passed to an array variable, and you can then use your loop to cycle through each each element and perform whatever action you want.

See:
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
http://www.dailydoseofexcel.com/arch...topenfilename/

Ok,

Well ive got it to "get" the filenames by making it select multiple also.

Please Login or Register  to view this content.

But im unsure how to reference the "filenames" to store them into an array.

as its a singular refererence with multiple datapoints in it, im unsure as of how to proceed.

ok, got it,.

Thanks for your help!

Please Login or Register  to view this content.

If you know the folder path you could do it without user intervention assuming all the files in the folder meet your requirements - just an idea

Please Login or Register  to view this content.

Stay away from the FileSearch function because it is no longer available after excel 2003. If you write you macro using FileSearch and later upgrade, it won't work (happened to me).

abousetta

Originally Posted by Ryan Murtagh

You can also replace

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

...but I'm not sure if there's a real functional advantage to that. I think that's just me trying to impose my own personal preference on you.

Actually it really does help alot that little change, not particually for this marco, but for if i want to use two moving references based off variables i can move up/down/left & right easily (using my own style of coding)

eg (totally useless bit of code, but before i went a long way round to reference horizontal compared to the easy vertical references)

Please Login or Register  to view this content.