+ Reply to Thread
Results 1 to 7 of 7

DIR command gives unexpected / unwanted results

  1. #1
    Registered User
    Join Date
    02-26-2019
    Location
    Schiedam, Netherlands
    MS-Off Ver
    Offie 365 Pro Plus
    Posts
    3

    DIR command gives unexpected / unwanted results

    I am creating a macro-enabled worksheet. In it I want to list only the .xls files to perform some actions on them. I know they are all in the old Excel format, so extension is .xls. I use the "DIR" command to loop through all files in the folder using the "*.xls" filter:
    Please Login or Register  to view this content.
    However, this gives me also all .xlsx, .xlsm etc files. How can I change this illogical and unwanted behaviour?

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658

    Re: DIR command gives unexpected / unwanted results

    That surprised me as well.

    This worked:

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    02-26-2019
    Location
    Schiedam, Netherlands
    MS-Off Ver
    Offie 365 Pro Plus
    Posts
    3

    Re: DIR command gives unexpected / unwanted results

    Thanks for your reply.

    My workaround is similar:

    Please Login or Register  to view this content.
    Point is not that I do not know how to filter out the unwanted files. Point is that this should not be necessary. It is remarkable that in Windows the Command Prompt shows the same behaviour.

    dir *.xl gives no results (as expected)
    dir *.xls also gives results like ...xlsm, ...xlsx, ...xlsq (the latter isn't even an existing excel extension)

    I consider this a bug, or a judgement error from Microsoft. It is illogical, because if I wanted those files I would issue a "Dir *.xls?" command. This kind of behaviour makes scripting and programming unreliable. Not to mention the time and energy needed to check and correct older scripts.

    But this is another kind of discussion. Just was curious if there is a setting in Excel / VBA to change this behaviour.

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,933

    Re: DIR command gives unexpected / unwanted results

    I imagine Dir was developed at a time when extensions weren't longer than 3 characters.
    Rory
    I drink, and I know things

  5. #5
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,679

    Re: DIR command gives unexpected / unwanted results

    Or this one maybe.
    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  6. #6
    Registered User
    Join Date
    02-26-2019
    Location
    Schiedam, Netherlands
    MS-Off Ver
    Offie 365 Pro Plus
    Posts
    3

    Re: DIR command gives unexpected / unwanted results

    Rorya

    That is correct, but that shouldn't matter. Point is that if my searchpattern is *.xls, it should not be interpreted as *.xls?, or even *.xls*. Again, I consider this a bug: it should not happen. But probably Microsoft would remark: "This is by design.". Well, bad designer . He should be fired. (Just kidding, but he could have done better.)

    I'll mark this thread as solved.

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,933

    Re: DIR command gives unexpected / unwanted results

    I don't disagree that it's bad design, but it's not necessarily illogical if you've been told that all file extensions are only three characters that you might not worry about anything that comes after that.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA producing unexpected results
    By trandle in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-20-2017, 09:43 AM
  2. [SOLVED] range.value = var ...unexpected results.
    By excel_joe in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-22-2016, 02:59 PM
  3. [SOLVED] Unexpected results from MSQuery
    By j_Southern in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-10-2014, 11:09 AM
  4. Unexpected results from CurrentRegion
    By ajgully in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-29-2011, 02:55 PM
  5. VLOOKUP with unexpected N/A results
    By cgjones in forum Excel General
    Replies: 2
    Last Post: 03-15-2011, 04:39 PM
  6. Unexpected HLOOKUP results
    By scaffdog845 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-06-2009, 03:01 PM
  7. Unexpected Results with CurrentRegion
    By avveerkar in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-06-2007, 09:57 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1