Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Jim,

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

    C:\Data\Excel*.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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    That is a neat little trick. Am I correct in presuming that FILES is an old XL macro function?

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts
    [quote name='pieterse' post='762788' date='02-Mar-2009 13:21']Hi Jim,

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

    C:\Data\Excel*.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.[/quote]


    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

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='762856' date='02-Mar-2009 17:50']That is a neat little trick. Am I correct in presuming that FILES is an old XL macro function?[/quote]
    Exactly. I use it very frequently.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •