Announcement

Collapse
No announcement yet.

VBA: Find Folder

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    Hi chico,

    If you are trying to verify that a folder exists?

    If I missed the question, try it again :wink2:

    .....Ralph
    Last edited by Dave Hawley; May 4th, 2010, 10:24.

    Comment


    • #3
      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 cangry:

      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.

      Regards
      Weasel
      Kind Regards
      Pesky Weasel
      "I have a plan so cunning, you could put a tail on it and call it a Weasel." EB
      Eagles may soar, but Weasels don't get sucked into jet engines.
      Templates and Calculators
      The Way of the Weasel
      Download Ivan & Colo's HTML Maker Here
      101 Excel Hacks - Great New Book
      Huge Savings on Excel Training

      Comment


      • #4
        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.

        Code:
        Set wbCodeBook = ThisWorkbook
         
            With Application.FileSearch
                .NewSearch
                 'Change path to suit
                .LookIn = FolderName
                .FileType = msoFileTypeExcelWorkbooks
        .Execute
        End With
        Last edited by Dave Hawley; May 4th, 2010, 10:23.

        Comment


        • #5
          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?

          Weasel
          Kind Regards
          Pesky Weasel
          "I have a plan so cunning, you could put a tail on it and call it a Weasel." EB
          Eagles may soar, but Weasels don't get sucked into jet engines.
          Templates and Calculators
          The Way of the Weasel
          Download Ivan & Colo's HTML Maker Here
          101 Excel Hacks - Great New Book
          Huge Savings on Excel Training

          Comment


          • #6
            Yes that's correct John will always be in Bob. Basically any John, Susan or Marys that exist in Bob are to be dealt to.

            Comment


            • #7
              Whoops sorry Weasel I might have mislead you. Bob doesn't require a certain John. The name of the files change. Thanks for your reply,
              Regards,
              Kiwichico

              Comment


              • #8
                Sub tester()
                Const FolderToFind As String = "C:\A\"

                If Len(Dir(FolderToFind)) = 0 Then
                MsgBox FolderToFind & " doesn't exits"
                Else
                MsgBox FolderToFind & " exits"
                End If

                End Sub
                Kind Regards,
                Ivan F Moala From the City of Sails

                http://www.xcelfiles.com

                Comment


                • #9
                  Ivan,

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

                  Regards
                  Weasel
                  Kind Regards
                  Pesky Weasel
                  "I have a plan so cunning, you could put a tail on it and call it a Weasel." EB
                  Eagles may soar, but Weasels don't get sucked into jet engines.
                  Templates and Calculators
                  The Way of the Weasel
                  Download Ivan & Colo's HTML Maker Here
                  101 Excel Hacks - Great New Book
                  Huge Savings on Excel Training

                  Comment


                  • #10
                    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?

                    Regards
                    Weasel
                    Kind Regards
                    Pesky Weasel
                    "I have a plan so cunning, you could put a tail on it and call it a Weasel." EB
                    Eagles may soar, but Weasels don't get sucked into jet engines.
                    Templates and Calculators
                    The Way of the Weasel
                    Download Ivan & Colo's HTML Maker Here
                    101 Excel Hacks - Great New Book
                    Huge Savings on Excel Training

                    Comment


                    • #11
                      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

                      -C
                      +2002
                      -2003
                      +July
                      +Aug
                      +D

                      I want it look like
                      -2003
                      +July
                      +Aug

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

                      Once again thanks for looking into it.
                      Regards,
                      Kiwichico

                      Comment


                      • #12
                        Originally posted by Pesky Weasel
                        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?

                        Regards
                        Weasel

                        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

                        Notes:
                        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;&#039;// This routine may take a while as</FONT&gt;<br&gt;<FONT COLOR="#007F00"&gt;&#039;// it Enumerates through ALL avail</FONT&gt;<br&gt;<FONT COLOR="#007F00"&gt;&#039;// 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;&#039;// 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;&#039;// 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;&#039;// gets Folder Name ONLY</FONT&gt;<br&gt;<FONT COLOR="#007F00"&gt;&#039;// Reverse the string</FONT&gt;<br&gt;MyFolder = StrReverse(MyFolder)<br&gt;<FONT COLOR="#007F00"&gt;&#039;// <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;
                        Kind Regards,
                        Ivan F Moala From the City of Sails

                        http://www.xcelfiles.com

                        Comment


                        • #13
                          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?

                          Regards
                          Weasel
                          Kind Regards
                          Pesky Weasel
                          "I have a plan so cunning, you could put a tail on it and call it a Weasel." EB
                          Eagles may soar, but Weasels don't get sucked into jet engines.
                          Templates and Calculators
                          The Way of the Weasel
                          Download Ivan &amp; Colo's HTML Maker Here
                          101 Excel Hacks - Great New Book
                          Huge Savings on Excel Training

                          Comment


                          • #14
                            It&#039;s OS dependant in that some Folders may display as Upper case but give a lower case result?? I haven&#039;t worked through this thoroughly.....try it out and see what you get......I&#039;ll have a further look...

                            cheers
                            Kind Regards,
                            Ivan F Moala From the City of Sails

                            http://www.xcelfiles.com

                            Comment


                            • #15
                              Thanks guys for looking into the problem.
                              Unfortunately I don&#039;t have XP. I am working on 95.

                              Regards,
                              Kiwichico

                              Comment

                              Working...
                              X