VBA: Find Folder

kiwichico · Sep 25th 2003

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 · Sep 25th 2003

Hi chico,

If you are trying to verify that a folder exists?

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

.....Ralph

Pesky Weasel · Sep 25th 2003

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.

Regards
Weasel

kiwichico · Sep 25th 2003

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

Pesky Weasel · Sep 25th 2003

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 · Sep 25th 2003

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 · Sep 25th 2003

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 · Sep 25th 2003

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 · Sep 25th 2003

Ivan,

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

Regards
Weasel

Pesky Weasel · Sep 25th 2003

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

kiwichico · Sep 25th 2003

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

Ivan F Moala · Sep 26th 2003

Quote

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

Display More

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

Pesky Weasel · Sep 26th 2003

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

Ivan F Moala · Sep 26th 2003

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

cheers

kiwichico · Sep 30th 2003

Thanks guys for looking into the problem.
Unfortunately I don't have XP. I am working on 95.

Regards,
Kiwichico

Ivan F Moala · Sep 30th 2003

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.

http://download.microsoft.com/…x/1.5/W9X/EN-US/wmi9x.exe

kiwichico · Oct 1st 2003

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")

Regards,
Kiwichico

VBA: Find Folder

Participate now!

Share