Open All Files in Folder Excel for Mac (2011)

[fxgump]

As I can't use Application.FileSearch with Excel for Mac (2011) I'm wondering if anybody knows of an alternative to loop through all files in a folder.

What I'm trying to achieve is to extract data from a static sheet in all the XLS documents in the folder and paste them into the current workbook. Once data is extracted file is to be closed and we are to open the next sheet, copy the data, etc etc.

It seems like a fairly simple task, however, if anybody could direct or help me that would be greatly appreciated!

Thanks!
Ryan

 

[RoryA]

Dir is the usual workaround and should work on Macs.

 

[fxgump]

Hi Rory,

Thanks for your help. I've tried Dir("") but it only seems to return one file. Even if I try calling it multiple times it still returns the same file. I have four files in the folder for test purposes.

 

[RoryA]

How are you calling it? The first time you pass the folder and any file name filters to Dir(), thereafter you do not pass any arguments at all in order for it to return the next file name.

 

[fxgump]

Thanks Rory! Got it working now!!