Get all file names and list in a worksheet with VBA

[babshire]

Hey All,

I'm using Office 365 and trying to write a macro that lists all of the excel file name in the directory "C:\Users\[username]\Desktop\49" into a worksheet

I used to have a macro that did this but can't find it now and I've searched online and found a few that use filesystemobject but can't get those to work and have no idea why (did Microsoft remove this?)

If anyone has one stuffed in an archive somewhere I'd be appreciative. Apologies if this gets frequently asked, I didn't have any luck when I searched for it.

 

[WaterGypsy]

You could try this, you'll need to define the path in sPath

sFileFound = Dir(sPath)

While sFileFound <> ""
       sFileFound = Dir()
Wend

 

[babshire]

Thank you! that worked perfectly.

Everything I found on Google was magnitudes more difficult and cumbersome than this. Appreciate it. It's been a while since I've used VBA so I'm a bit rusty.

 

[WaterGypsy]

Keep it Simple is my motto