Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Using formulas to return a file list

    Hi all

    In Excel you can create a file list for a specified folder (and filespec) using formulas.
    (I know you can also do this in vba, I'm just using another method here)
    See attached Excel2003 example file. Enter your own foldername and filespec in cell [A1]

    To do this:
    define a name fileList
    In the Refers to: box enter
    =FILES($A$1)

    This uses the old Excel4macro function FILES.

    Now, in cell [A1] enter your required folder and filespec, e.g. "E:\MyBooks\R*.pdf"
    (This will create a list of files beginning with "R" with a file extension of .pdf, from the specified drive and folder)

    Suppose you want to list the matching files in column [A], starting at row 11, with a header in cell [A10] as "Book List" (for example).
    You now use this formula in cell [A11], (and copying this formula down say, 20 rows to return up to 20 files):
    =INDEX(fileList,ROW()-10)

    (the formula returns #N/A if no files are found, or if fewer files found than number of formula cells etc etc)

    Now, what I want to do is to sort the returned values in alphabetic order.
    So my question is, what is the best way to do this????
    I guess it requires sorting the array fileList?

    zeddy
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I was playing around with your example Workbook with some thoughts in mind to address your question and, curiously, I could only get files lists sorted alphabetically by filename.

    Try as I might - adding and removing files in the target destination, renaming them, mixing numeric and alpha characters - I only ever get a sorted list. The list remains sorted by filename even if I have differing extensions in the mix . . . ?

  3. The Following User Says Thank You to MartinM For This Useful Post:

    zeddy (2015-01-18)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Martin

    ..that's very interesting.
    Mine are definitely not sorted alaphabetically.
    Perhaps my folder is 'sorted' in file date order.
    I will do some tests.
    Perhaps it is dependant on the default folder sort order i.e. when you use the file Explorer in Windows.

    zeddy

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy & Martin,

    I noticed the same behavior in my testing, I thought it was just me! Of course if you want a Z-A sort you're out of luck as far as I can tell. I tried to access the array in VBA w/o success. The only possibility I could think of was to load the found files into a VBA array along with their row number then sort the array the way you want it then rewrite the formulas using the associated row number from the original order. Clear as MUD right? HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    zeddy (2015-01-18)

  7. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi RG

    I believe I can use another array formula, say in column [B], which uses the returned results from column [A] and sorts them.
    But it's a bit of a hairy formula.
    Still working on it.

    zeddy

  8. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I'm still working on trying to get the file list not sorted, which may yield a clue as to why your list isn't sorted.

    Changing the sort order in Windows (XP) Explorer makes no difference, neither does creating the files in a random order to see if the file timestamps had any influence.

    I'll continue trying to break it, whilst watching the finals of the snooker Masters with the other eye

  9. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Martin

    ..how about you trying to reverse the order then???

    zeddy

  10. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I can't - whatever I do it comes up in alpha order . . . still trying though.

  11. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Martin

    ..according to this Dir help file at this link:
    https://support.office.com/en-us/art...rs=en-US&ad=US

    Tip Because file names are retrieved in no particular order, you may want to store returned file names in an array, and then sort the array.
    zeddy

  12. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Martin

    ..I'm still working on a formula solution.
    For example, in my sample file, I can place these formulas and copy down:
    Code:
    [B11] =COUNTIF(A:A,"<="&A11)
    [C11] =INDEX(A:A,MATCH(ROW()-10,B:B,FALSE))
    ..this gives me an alphabetic sorted list in column [C] based on the returned values in column [A]
    ..but I get a couple of mysterious #N/A scattered in my column [C] results.

    zeddy

  13. #11
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Yes - I saw that. Are you sure that vba is using the Dir function when it executes Files() ? I'm not.

    I've now tried it on a W7 PC - same result: I can only get sorted results (alpha, ascending).

  14. The Following User Says Thank You to MartinM For This Useful Post:

    zeddy (2015-01-18)

  15. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Martin

    oops. Yes, I remember now I'm not using the Dir function, I'm using the Excel4 FILES function!

    I'm using Windows7, accessing an external solid state drive E:

    zeddy

  16. #13
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Our posts crossed.

    Curious about the odd scattering of #N/As . . . thinking . . .

    Got it: copy everything from row 11 down two columns to the right and all is well. "A:A" is picking up the stuff in rows 1 and 2 that you don't want to be included. Only moving it by one column still includes the stuff in cells B1 and B2 in your ranking system.

    Or of course in B11 =COUNTIF(A$11:A$30,"<="&A11) and copy down.

    Truly bizarrely, though, in the example I am using (a folder with 5 files), below the sorted list I get 9 #N/A, then a lone #REF! followed by 5 more #N/A !!

    PS Going off at a tangent, there's a full list of Excel4Macro functions here: http://www.44lbs.net/ilia/Excel4macro.zip
    Last edited by MartinM; 2015-01-18 at 18:37.

  17. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Martin,

    Could the #REF be either the . or .. directories? HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  18. #15
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    RG,

    This is peripheral to Zeddy's original request and I suppose that the precise error shown is unimportant in the context of what he's doing.

    That said, there are no sub-directories and, even odder, if I remove one of the files in the target list the lone #REF! moves down one place in the sorted list ! That's undocumented functions for you . . .

    Martin

  19. The Following User Says Thank You to MartinM For This Useful Post:

    zeddy (2015-01-19)

Page 1 of 2 12 LastLast

Posting Permissions

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