Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Folder Contents to Excell
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Folder Contents to Excell
Take a look at the following site:
http://www.ozgrid.com/forum/showthread.php?t=66389 Mark Ivey "TC Daniel" <TC wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Folder Contents to Excell
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....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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Folder Contents to Excell
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) :D regards, hector. __ OP __ 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....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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Folder Contents to Excell
trouble with your remedy, but feel it has potential. please contact me asap.
thanks tcd tcdaniel at charter dot net "Héctor Miguel" wrote: 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....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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display Contents of Folder | Excel Programming | |||
Listing the contents of a folder | Excel Discussion (Misc queries) | |||
Add contents of A1 in all workbooks within a folder | Excel Programming | |||
contents of folder | Excel Programming | |||
Move Folder Contents | Excel Programming |