VBA: Find Folder

  • Hi All,
    Just like to know if anyone knows how to search for a folder instead of a file so that I can assign the name of the path as a string variable. Thanks in advance.

  • Wow! This is a toughie!

    Ralphs suggestion will tell if the folder exists but for that you need the path.

    To actually search for a folder and then return the path is much tougher than I imagined. I have dug up code that will reurn the path of special folders no matter where they are (eg: MyDocuments) and I have looked at SearchScope property which you can apparently add items to (as in additional folders) to be incuded in a search of folders - but I havent figured out how to do it :pcangry:

    I will keep trying while I have time and I look foward to anyone else coming up with a solution to this harder than it sounds problem.


  • Thanks for your speedy reply. At the moment I have a piece of code that can find a file but I need to see if it can find a folder only. The reason for this is because people have a tendency to move the folder and it contents to different locations in the drive.
    I am trying to write some code that can open several files from locating a specified folder. Below is a snippet of code that finds all the excel files. Can I change this to look for a folder. The foldername is the root directory.

    Set wbCodeBook = ThisWorkbook
        With Application.FileSearch
             'Change path to suit
            .LookIn = FolderName
            .FileType = msoFileTypeExcelWorkbooks
    End With
  • Will the folder you are looking for always contain a certain file.

    ie Folder named BOB
    will always have a spreadsheet named JOHN.xls inside?


  • Ivan,

    Cant get that example to return a positve result. Could you eloborate on what it is supposed to do?


  • OK - I got working some of the time.

    However, you need to know the file path in order to see if it exists.
    What the OP would like to do is enter a folder name and have excel search to see if the folder exists similar to the windows Find Files & Folders utility.
    Can this be accessed and controlled through Excel using API calls?


  • Hey thanks Weasel for taking the time to look into this.

    I guess I should explain the broader picture of what I am trying to achieve. The reason why I am trying to find a folder is because I want to find the pidlroot of where the folder is located. Once I know the id number I can then run the GetFolderName code of allowing the user to select a folder from a specified folder instead of having to search for a folder from the root directory. For example instead of the GetFolderName showing a dialog like


    I want it look like

    so that the user can only see 2003 and can select a specified month.

    Once again thanks for looking into it.

  • If the OP needs to find a folder then he must know something of the Dir/Drive structure it
    was saved or created as eg

    c:\2003 or what ever.....thats all you need to test for....if he doesn't know the drive then
    thats simple enough to get, but he must know something of the structure UNLESS he
    has not created this and the user has placed this somewhere else ?
    In which case yes he will (unfortunately) need to Enumerate through ALL Folders .......

    I only say unfortunate as Enumerating through all folders can take a bit of time...and the Folder structure should really be set-up before hand.
    Any way....you can do this via WMI and a Query search so prerequisites are WinXp

    This should get you the Folder

    1) Folder name is case sensitive
    2) Finds 1st instance and exits! ie If more then one folder then will need to amend

    BTW, you don't need the PIDLROOT as this gets the Folder.....all you have to do is substitute this in the vRootFolder variable if you are Using the Shell browse.

    EDIT: SORRY OP did state users may have in other Dir structure....my appologies !!

    <pre&gt;<FONT COLOR="#00007F"&gt;Option</FONT&gt; <FONT COLOR="#00007F"&gt;Explicit</FONT&gt;<p&gt;<br&gt;<FONT COLOR="#00007F"&gt;Function</FONT&gt; FindFolder()<br&gt;<FONT COLOR="#007F00"&gt;'// This routine may take a while as</FONT&gt;<br&gt;<FONT COLOR="#007F00"&gt;'// it Enumerates through ALL avail</FONT&gt;<br&gt;<FONT COLOR="#007F00"&gt;'// Folders AND Drives!!</FONT&gt;<br&gt;<FONT COLOR="#00007F"&gt;Dim</FONT&gt; strFolderToFind <FONT COLOR="#00007F"&gt;As</FONT&gt; <FONT COLOR="#00007F"&gt;String</FONT&gt;<br&gt;<FONT COLOR="#00007F"&gt;Dim</FONT&gt; strComputer <FONT COLOR="#00007F"&gt;As</FONT&gt; <FONT COLOR="#00007F"&gt;String</FONT&gt;<br&gt;<FONT COLOR="#00007F"&gt;Dim</FONT&gt; objWMIService <FONT COLOR="#00007F"&gt;As</FONT&gt; <FONT COLOR="#00007F"&gt;Object</FONT&gt;<br&gt;<FONT COLOR="#00007F"&gt;Dim</FONT&gt; colFolders <FONT COLOR="#00007F"&gt;As</FONT&gt; <FONT COLOR="#00007F"&gt;Object</FONT&gt;<br&gt;<FONT COLOR="#00007F"&gt;Dim</FONT&gt; objFolder <FONT COLOR="#00007F"&gt;As</FONT&gt; <FONT COLOR="#00007F"&gt;Object</FONT&gt;<br&gt;<FONT COLOR="#00007F"&gt;Dim</FONT&gt; blnFound <FONT COLOR="#00007F"&gt;As</FONT&gt; <FONT COLOR="#00007F"&gt;Boolean</FONT&gt;<br&gt;<br&gt;blnFound = <FONT COLOR="#00007F"&gt;False</FONT&gt;<br&gt;strComputer = "."<br&gt;<FONT COLOR="#007F00"&gt;'// Note case sensitive...Name of Folder</FONT&gt;<br&gt;strFolderToFind = "a"<br&gt;<br&gt;<FONT COLOR="#00007F"&gt;Set</FONT&gt; objWMIService = GetObject("winmgmts:" _<br&gt; & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")<br&gt;<FONT COLOR="#00007F"&gt;Set</FONT&gt; colFolders = objWMIService.ExecQuery("Select * from Win32_Directory")<br&gt;<br&gt;<FONT COLOR="#00007F"&gt;For</FONT&gt; Each objFolder In colFolders<br&gt; <FONT COLOR="#00007F"&gt;If</FONT&gt; FolderNameOnly(objFolder.Name) = strFolderToFind <FONT COLOR="#00007F"&gt;Then</FONT&gt;<br&gt; blnFound = <FONT COLOR="#00007F"&gt;True</FONT&gt;<br&gt; <FONT COLOR="#00007F"&gt;Exit</FONT&gt; <FONT COLOR="#00007F"&gt;For</FONT&gt;<br&gt; <FONT COLOR="#00007F"&gt;End</FONT&gt; <FONT COLOR="#00007F"&gt;If</FONT&gt;<br&gt;<FONT COLOR="#00007F"&gt;Next</FONT&gt;<br&gt;<br&gt;<FONT COLOR="#00007F"&gt;If</FONT&gt; blnFound <FONT COLOR="#00007F"&gt;Then</FONT&gt;<br&gt; MsgBox objFolder.Name & " was found!"<br&gt;<FONT COLOR="#00007F"&gt;Else</FONT&gt;<br&gt; MsgBox "Folder named [" & strFolderToFind & "] NOT FOUND!"<br&gt;<FONT COLOR="#00007F"&gt;End</FONT&gt; <FONT COLOR="#00007F"&gt;If</FONT&gt;<br&gt;<br&gt;<FONT COLOR="#007F00"&gt;'// clean-up</FONT&gt;<br&gt;<FONT COLOR="#00007F"&gt;Set</FONT&gt; objWMIService = <FONT COLOR="#00007F"&gt;Nothing</FONT&gt;<br&gt;<FONT COLOR="#00007F"&gt;Set</FONT&gt; colFolders = <FONT COLOR="#00007F"&gt;Nothing</FONT&gt;<br&gt;<br&gt;<FONT COLOR="#00007F"&gt;End</FONT&gt; <FONT COLOR="#00007F"&gt;Function</FONT&gt;<br&gt;<br&gt;<FONT COLOR="#00007F"&gt;Function</FONT&gt; FolderNameOnly(MyFolder <FONT COLOR="#00007F"&gt;As</FONT&gt; <FONT COLOR="#00007F"&gt;String</FONT&gt;) <FONT COLOR="#00007F"&gt;As</FONT&gt; <FONT COLOR="#00007F"&gt;String</FONT&gt;<br&gt;<FONT COLOR="#007F00"&gt;'// gets Folder Name ONLY</FONT&gt;<br&gt;<FONT COLOR="#007F00"&gt;'// Reverse the string</FONT&gt;<br&gt;MyFolder = StrReverse(MyFolder)<br&gt;<FONT COLOR="#007F00"&gt;'// <FONT COLOR="#00007F"&gt;Get</FONT&gt; the string to the left of the first \ and reverse it</FONT&gt;<br&gt;MyFolder = StrReverse(Left(MyFolder, InStr(MyFolder, "\") - 1))<br&gt;<br&gt;FolderNameOnly = MyFolder<br&gt;<br&gt;<FONT COLOR="#00007F"&gt;End</FONT&gt; <FONT COLOR="#00007F"&gt;Function</FONT&gt;<br&gt;<br&gt;</pre&gt;

  • Ivan,

    I will go through that code more thoroughly at home in a vain attempt to understand it :)
    I was wondering though - could us the code to convert objFolder.Name & stFolderToFind both to uppercase thus eliminating the case sensitve issue?


  • For those running Windows 95 OSR 2, Windows 98 or Microsoft® Windows NT® 4.0, a WMI installation package can be downloaded from Microsoft® MSDN® which offers similar functionality as WMI in Windows 2000, Windows XP, and Windows Me.


  • Thanks Ivan
    I have downloaded the application but it still does not let me run your program I get the following error that it does not identify the class name. Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")


Participate now!

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