Create auto updating list of folder contents

[lorne17]

Hello there,

I am working on Excel 2008 for Mac. I am wanting to create a macro that will automatically pull in folder contents and continuously update whenever I open the file.

I've successfully done this with Word on PC in the past, but that was a really long time ago. So I don't remember.

Any ideas?

Thanks,
Lorne

[Domski]

Excel 2008 for the Mac doesn't support VBA I'm afraid.

Dom

[lorne17]

Well shoot.

How might I do this on PC?

Thanks,
lorne

[Domski]

Something like this:

Please Login or Register  to view this content.

Which could then be called from a workbook open event:

Please Login or Register  to view this content.

The first bit of code should go in a regular module and the second on the ThisWorkbook code page.

Dom

[lorne17]

Awesome, thank you. I'll take a stab at it when I get on my PC.

Lorne

[Whizbang]

Oops. I walked away from my PC and a whole conversation took place before I hit "submit". Oh well. Here is my code.

Please Login or Register  to view this content.

[romperstomper]

This should work in XL2008 since it uses the old FILES XLM function. As with all XLM functions, you cannot use it directly in a worksheet cell - you have to use it in a defined name and then use that name in the cell.

So, to set up the name:

In 2003 or earlier:
Select Insert - Name - Define from the menu, enter FileList in the 'Names in workbook:' box, then in the 'Refers to:' box enter:
=FILES(Sheet1!$A$1)
then press OK.

This name will now use the contents of A1 on Sheet1 as the path and name to use for the file list. So, for example, you can enter:
C:\Test\*.*
in A1 to return a list of all files in C:\test, or:
C:\Test\*.xls
to return a list of workbooks in that folder.

To use this in the worksheet, enter:
=INDEX(FileList, row())
in B1 and copy down. The ROW() function will return 1,2,3,4 etc as you copy down so that the value returned in the cells is the 1st then 2nd then 3rd item in the FileList array.