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

nope, thank you anyway :)

Answer 3

Hi Bob,

No i'm not using any VBA.

i want to use an excel sheet to list all the files present in a folder.

in my first cell A1 i will have the path of the files.

To this cell A1 i will apply a name "filenamelist" and for select range of cell i will write the following formula =FILES('DOCLIST!$A$1)

using this formula when creating a name will tell to excel that this name is a path to a folder

to generate automatically my path in cell A1, i'm using the cell("filename") function (=CONCATENATE(LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1),"*") witch return a path like /Users/remyveillat/Desktop/*

the function files i've been describing previously doesn't understand the path the way it is return.

it work on windows machin but not on Mac. i believe the codification of the path may be the reason.

Answer 4

What, exactly, are you trying to do? The title says you are looking for file names. Your description says you are looking for worksheet names in a workbook. What is it really? Are you using VBA? What do you expect to do with the array? Where are you expecting to keep this array and use it from?

One way is to define a name (any name say xxxx) and in the refers to enter: =get.workbook(1).

Now, in any cell in that workbook, you can enter the formula: =index(xxxx,2) to retrun the name of the second worksheet in that workbook.

If this is not what you want, then explain in more detail exactly what you have in mind.

Answer 5

I don't know if this will help or not. But in Mac the equivalent to /xxx/yyy/zzz, is (or was) **::xxx::yyy::**zzz

__________

Disclaimer:

The questions, discussions, opinions, replies & answers I create, are solely mine and mine alone, and do not reflect upon my position as a Community Moderator.