Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: VBA: Find Folder

  1. #1
    Join Date
    13th May 2003
    Location
    Auckland, New Zealand
    Posts
    49
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th June 2003
    Location
    Maryland, USA
    Posts
    368
    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 at 10:24.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    7th February 2003
    Location
    Kewarra Beach, Cairns - TNQ
    Posts
    1,055
    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

  4. #4
    Join Date
    13th May 2003
    Location
    Auckland, New Zealand
    Posts
    49
    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.

    VB:
    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 at 10:23.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    7th February 2003
    Location
    Kewarra Beach, Cairns - TNQ
    Posts
    1,055
    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

  6. #6
    Join Date
    13th May 2003
    Location
    Auckland, New Zealand
    Posts
    49
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    13th May 2003
    Location
    Auckland, New Zealand
    Posts
    49
    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    27th January 2003
    Location
    Auckland, New Zealand
    Posts
    396
    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

  9. #9
    Join Date
    7th February 2003
    Location
    Kewarra Beach, Cairns - TNQ
    Posts
    1,055
    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

  10. #10
    Join Date
    7th February 2003
    Location
    Kewarra Beach, Cairns - TNQ
    Posts
    1,055
    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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Find Filename In Folder
    By mellojoe in forum EXCEL HELP
    Replies: 4
    Last Post: April 11th, 2007, 02:41
  2. Find And Open File In Specific Folder
    By bpepper82 in forum EXCEL HELP
    Replies: 4
    Last Post: March 13th, 2007, 02:41
  3. Find Workbook Name In Specific Folder
    By tblondet in forum EXCEL HELP
    Replies: 3
    Last Post: October 27th, 2006, 08:05
  4. Find Oldest File in Folder
    By StefanG in forum EXCEL HELP
    Replies: 5
    Last Post: July 28th, 2005, 13:48
  5. Find Value in closed Workbooks in folder
    By HarryFrohlich in forum EXCEL HELP
    Replies: 7
    Last Post: April 12th, 2005, 04:15

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