using array is a folder path --> index doesn't return the files names.

Question

Dear all,

I'm trying to build a list of documents inside an Excel spreadsheet.

To do so, I'm using the index function.

INDEX("folder path,1) to get the name of the first file in the folder.

My issue is the folder path.

Microsoft window users will have something like c:\xxx\xxxx\

on mac it could be HD:xxx:xxx:

if I use the function cell("filename") excel will return /xxx/xxxx/

However I've been trying the different possible ways to write the path, but apparently when it come to attribute it to an array, Excel doest understand.

Thanks for your eventual feedback.

Answer 1

Ok, FILES is indeed working, and working properly in Excel 2016. The problem is the file nomenclature that is needed fro Excel 2016. The Apple HFS (using colons as node separators) file system in effect up through Office 2011 has been deprecated by Apple and is no longer supported in Office 2016. You must use the POSIX file notation when specifying files in Office 2016, which you are doing.

There is a bug in Excel 2016 where Sandboxing prevents FILES from returning the directory contents. What should happen is that the OS should present a user dialog asking permission to access that directory. A bug is filed within Microsoft about this issue. It remains to be seen when, or if, it will be addressed.

Answer 2

It looks like FILES is not working in Excel 2016. It seems to work properly in Excel 2011. I will report it to microsoft. you should do the same using the smiley face in the upper right of Excel's menu bar.