Folder Contents to Excell

[TC Daniel]

How can I copy (en mass) a folders contents, to show only the names.ext of
the files.
I'll be using these names to create links in excel. I've tried the insert
hyperlink, but it is too slow for all the work here and there are toooo many
to manually type in.

ie:
\2006
\2007\
abc.pdf
def.pdf
ghi.pdf
xyz.pdf
lmn.xls

My goal is to create an index of all 1500 or so files with working links to
each file.
Additionally, it would have to be updated weekly with new files.

thanks for your help.
(e-mail address removed)

 

[Mark Ivey]

Take a look at the following site:

http://www.ozgrid.com/forum/showthread.php?t=66389

Mark Ivey

 

[Héctor Miguel]

hi, TC Daniel !

How can I copy (en mass) a folders contents, to show only the names.ext of the files.
I'll be using these names to create links in excel. I've tried the insert hyperlink
but it is too slow for all the work here and there are toooo many to manually type in.
ie:
\2006
\2007\
abc.pdf
def.pdf
ghi.pdf
xyz.pdf
lmn.xls
My goal is to create an index of all 1500 or so files with working links to each file.
Additionally, it would have to be updated weekly with new files.

following is an example to get filenames for a given folder
macros are not required and you could modify acording other needs

1) write the path and file_type in first row (say A1)
(e.g.) c:\my documents\*.xls (only excel files)
or: c:\my documents\*.* (all files)
or: ???

2) use (menu) insert / name / define...
name: (e.g.) myFiles
formula: =files(!a$1)&rept("",0*now())

3) put the following formula one row below your path (e.g. A2)
=counta(myFiles)

4) one row below (say A3) start your file list with the following formula:
=if(row(a1)>a$2,"",index(myFiles,row(a1)))
or...
=if(rows(a$3:a3)>a$2,"",index(myFiles,rows(a$3:a3)))

5) drag/copy down (same column) at least as many rows as file_list_number (in a2)

6) after this, you could use hyperlink worksheet-function (see
-> Coding a link address and friendly name using HYPERLINK Worksheet function
http://www.mvps.org/dmcritchie/excel/sheets.htm#hyperlink

7) if you need another path files list, repeat steps 1, 3 & 4 (step 2 no more) in other columns

if any doubts (or further information)... would you please comment ?
hth,
hector.

 

[papou]

Hi Héctor
What's the use of **&rept("",0*now())** in the formula?

The Excel4 function FILES seems sufficient to make this method work

Cordially
Pascal

 

[Héctor Miguel]

hi, Pascal !

What's the use of **&rept("",0*now())** in the formula?
The Excel4 function FILES seems sufficient to make this method work
Cordially
Pascal

the volatile w-f now() coerces "the name" to update if you modify the path/file_filter in row 1
(it's just in case)

regards,
hector.

__ OP __

 

[TC Daniel]

trouble with your remedy, but feel it has potential. please contact me asap.
thanks
tcd
tcdaniel at charter dot net