Application.filesearch Replacement For Office 2007

  • In the following thread, richphillips wonders about Application.Filesearch


    http://www.ozgrid.com/forum/showthread.php?t=68191


    There was no answer to his question.


    Is there a replacement for this? I have several macros that use Application.Filesearch
    and I would like it to work not only with Excel 2003 but also with Excel 2007.


    This is the function



    Thanks and regards.


    John

  • Re: Application.filesearch Replacement For Office 2007


    Hi John


    Filesearch has indeed been removed but the (very) old Dir remains, so you can use this instead, an example of which follows:



    This doesn't search subfolders but that functionality could no doubt be introduced. In the current spec you have to pass in the filepath to search.


    Richard


    EDIT: amended function so Do Loop doesn't error

  • Re: Application.filesearch Replacement For Office 2007


    For some reason I can't seem to edit my last message. I have changed the return type if no files are found (it simplifies the code):


  • Re: Application.filesearch Replacement For Office 2007


    Not too sure that it has been "removed" or M$ stuffed up and forgot to add it. Perhaps it will be added back after fixing the gazillion other bugs?

  • Re: Application.filesearch Replacement For Office 2007


    Parsnip, Roy, ByTheCringe2 and Dave, thanks for replying.
    I put the suggested replacement code in from both Parsnip and Roy but I can't get things to work. Due to my ignorance of VB I have to say. I have looked and tried to understand the suggested code but I can't see where I need to change/add things to make it work as the file does in Excel 2003. I neglected to add the Sheet1 code to my first thread entry.
    This is it.



    I don't even know if you need it for the replies.


    I have put Parsnip's and Roy's code in but because of my very limited VB knowledge, can't get it to work.
    With Parsnip's code, I get either 'No files found" or nothing happens at all, depending on the changes I make.


    In Roy's code it says "do something" but I don't even know what to enter at this point.


    If someone has the time and would be willing to try the attached file in Excel 2003, he/she will see how it works and maybe tell me what to change where.


    The code in the attached file is obviously not written by me.


    Your help is greatly appreciated.


    Thanks and regards.


    John

  • Re: Application.filesearch Replacement For Office 2007


    Hi Richard.
    Thank you very much for doing this. I downloaded the amended workbook and quickly tried it. Fantastic that it now also works on the 2007 system. I'll have to see what you did and maybe, this is a big maybe, I can understand. I'll have to bring this computer in for some repairs and than I'll be going out of town for a couple weeks so it'll be a while before I can get to it. Thank you very much again though.


    Regards


    John

  • Re: Application.filesearch Replacement For Office 2007


    Hi Richard (Parsnip).
    I am still around so I worked with the amended workbook. It's even faster than the 2003 version but one thing I would like to ask you. In the "original" version it printed the path included. Would that be possible in the workbook you amended. The reason is that I keep workbooks as a database with a find button where I can find certain files. It is therefor handy to have the path included.
    Thanks again Richard.
    John


    Dave.
    I must have had the code from Parsnip in the wrong place because I did change the test3 path but it still did not work. Believe it or not, this was kind of obvious even to me.
    Thanks though Dave.
    John

  • Re: Application.filesearch Replacement For Office 2007


    I recently faced a similar probem with missing Application.filesearch in Office 2007. To workaround the problem I have developped the simple "Dir" functionality a little further - and wrote a recursive function that searches also through all subfolders. It also returns full pathnames of all found files, which is more practical.
    Viktor

  • Re: Application.filesearch Replacement For Office 2007


    I know this is an old post, but you are expanding on the original query. However, please add code tags around your code - it makes it much easier to read.

    I might add that those static arrays ...

    Code
    Dim files(10000) As String
    Dim dirs(10000) As String

    might be a little inadequate. My bare bones, low specced work machine (admittedly with Oracle and TOAD installed) has 30,000+ files and 12,000+ directories...

    Dynamic arrays would be so much better if this code were to be used in a production environment.

  • Re: Application.filesearch Replacement For Office 2007


    Hello viktor,


    Welcome to Ozgrid. Thanks for posting a solution.


    However, please note:


    All VBA code posted in the forum must be wrapped in code tags which you omitted. I've added the tags for you this time only. Be sure to use them in future posts.


    How to use code tags


    [noparse]

    Code
    [/noparse]
    [COLOR="blue"]your code goes between these tags[/COLOR]
    [noparse]

    [/noparse]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!