VBA: Find Folder

[kiwichico]

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.

[rem1224]

Hi chico,

If you are trying to verify that a folder exists?

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

.....Ralph

[Pesky Weasel]

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

[kiwichico]

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 
    .NewSearch 
     'Change path to suit
    .LookIn = FolderName 
    .FileType = msoFileTypeExcelWorkbooks 
    .Execute 
End With 

[Pesky Weasel]

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

[kiwichico]

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.

[kiwichico]

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

[Ivan F Moala]

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

[Pesky Weasel]

Ivan,

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

Regards
Weasel

[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