Copy Filenames to Excel

Topic

New Reply

I have struggled with this issue before and may have even posted a question in the forum–though I cannot find any posts via a search. I have a directory of 1,700+ files. These files were automatically created by a time and billing software and the file names are mechanically generated [alpha numeric file names—22 characters in length].

I need to create a table with additional information regarding these files. Rather than use Word, I want to use EXCEL as I figure that I’ll eventually be generating reports from this created table and I think EXCEL is better for sorting table information.

My question is there a way to copy the file names from windows explorer into Excel? When I try to do this, Windows XPsp3 thinks that I truly want to copy the files to another location on the computer—all I want is the filename to be copied to Column B in the Excel worksheet. I am fearful of many typos if I elect to type the 1700 file names into Excel. Thanks.

Reply | Quote

Replies

Hi Jim,

In cell C1, enter the full path and the file filter, e.g.:

C:DataExcel*.xls

Select Insert, Name, define from the menu and define:
Name:AllFiles
Refersto: =FILES($C$1)

In cell A1 enter:

=INDEX(AllFiles,ROW())

Drag down.

Reply | Quote

That is a neat little trick. Am I correct in presuming that FILES is an old XL macro function?

Steve

Reply | Quote

That is a neat little trick. Am I correct in presuming that FILES is an old XL macro function?

Exactly. I use it very frequently.

Reply | Quote

Hi Jim,

In cell C1, enter the full path and the file filter, e.g.:

C:DataExcel*.xls

Select Insert, Name, define from the menu and define:
Name:AllFiles
Refersto: =FILES($C$1)

In cell A1 enter:

=INDEX(AllFiles,ROW())

Drag down.

Pieterse,
Wow, many, many thanks….Your adviced saved me countless hours of key entry…and is perfect and not subject to my fat fingers making typos. Again, thank you very much!!!
JimC

Reply | Quote