Solved: Getting the path to a folder from just the folder name

[xltrader100]

The user has just clicked on a cell with the name of a folder "FolderName" as its cell text. I'm trying to get the full path to FolderName. I know it's somewhere in ProjectFolder that has 5 levels of subfolders. I've been trying to loop through the folders in ProjectFolder using Dir and getAttr and vbDirectory, but I can't find the right syntax.

How do I find the FolderName path?

[Bob Phillips]

Sub FindFolder()
Dim FSO As Object
Dim folder As Object
Dim path As String

Set FSO = CreateObject("Scripting.FilesystemObject")
Set folder = FSO.Getfolder("C:\ProjectFolder")

If NextLevel(folder, path) Then

MsgBox path
End If

Set folder = Nothing
Set FSO = Nothing
End Sub

Private Function NextLevel(ByRef folder As Object, ByRef path As String) As Boolean

NextLevel = False
For Each folder In folder.subFolders

If folder.Name = ActiveCell.Value Then

path = folder.path
NextLevel = True
Exit For
Else

If NextLevel(folder, path) Then

path = folder.path
NextLevel = True
Exit For
End If
End If
Next folder
End Function

[snb]

probably a oneliner suffices:


Sub tst()
msgbox = filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir C:\Projectfoler\*. /b /s").stdout.readall, vbCrLf),activecell.value)(0)
end sub

[xltrader100]

Indeed, the one liner does work. Amazing! Both solutions work, but with xld's I understand what's going on, and with the one liner I don't. It will join the other code snippets that I use without fully grokking them. Thanks to you both.

[xltrader100]

How would I test the one liner for success/failure?

[xltrader100]

Also, every time the one liner gets called it flashes the Windows Cmd window up on the screen for about 2 seconds. How can I suppress that?

[snb]

How would I test the one liner for success/failure?


Sub M_snb()
sn = filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir C:\Projectfolder\*. /b /s").stdout.readall, vbCrLf),activecell.value)
if ubound(sn)>-1 then msgbox sn(0)
End Sub

[snb]

Also, every time the one liner gets called it flashes the Windows Cmd window up on the screen for about 2 seconds. How can I suppress that?


Sub M_snb()
Shell "cmd /c Dir G:\OF\*. /b /s > G:\OF\snb.txt"

Do
DoEvents
Loop Until Dir("G:\OF\snb.txt") <> ""

Do
DoEvents
Loop Until FileLen("G:\OF\snb.txt") > 0


MsgBox Split(CreateObject("scripting.filesystemobject").opentextfile("G:\OF\snb.txt").readall, vbCrLf)(0)
End Sub

[xltrader100]

I'm trying to make a general purpose function out of this because it could be quite useful, but I'm having trouble passing in the name of the ProjectFolder as an argument. It works fine if I hard code it. I can pass in the folder name I'm looking for, but not name of the containing Project Folder. How do I do that? I have another question about suppressing the Cmd window, but I want to get past this first.

Sub TEST_findPathToFolder()
Dim findFolder As String ' the name of the folder being searched for
Dim rootFolderPath As String ' this folder contains findFolder at some level
Dim foundFolderPath As String ' the path to findFolder, starting at rootFolder
findfolder = "FolderPix" ' find the path to the folder named "FolderPix"
rootFolderPath = "C:\Gridder" ' "Gridder" is the name of the Project Folder

Call findPathToFolder(rootFolderPath, findFolder, foundFolderPath)
MsgBox foundFolderPath
End Sub

Sub findPathToFolder(ByVal rootFolder As String, ByVal findFolder As String, foundFolderPath As String)
Dim V
V = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir C:\Gridder\*. /b /s").StdOut.ReadAll, vbCrLf), findFolder) ' works fine
' V = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir rootFolder\*. /b /s").StdOut.ReadAll, vbCrLf), findFolder) '<== error: file not found
If UBound(V) > -1 Then foundFolderPath = V(0)
End Sub

[snb]

Sub M_snb()
MsgBox F_snb("G:\OF", "codeA")
End Sub

Function F_snb(ParamArray sq())
sn = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & sq(0) & "\*. /b /s").StdOut.ReadAll, vbCrLf), sq(1))
If UBound(sn) > -1 Then F_snb = sn(0)
End Function

[xltrader100]

Thanks snb, that fixed the problem and this is working very well now, except for the flashing Cmd window. I'm having a hard time melding your fix for that into my code. Could you take a crack at adding the Cmd window fix into this code?

Sub TEST_getPathToFolder()
Dim findFolder As String ' the name of the folder being searched for
Dim ProjectFolder As String ' this folder contains findFolder at some level
findFolder = "folderPix" ' find the path to the folder named "folderPix"
ProjectFolder = "C:\Gridder" '
MsgBox getPathToFolder(ProjectFolder, findFolder)
End Sub

Function getPathToFolder(ParamArray sq()) As Variant
Dim V
V = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & sq(0) & "\*. /b /s").StdOut.ReadAll, vbCrLf), sq(1))
If UBound(V) > -1 Then
getPathToFolder = V(0)
Else
getPathToFolder = False
End If
End Function

[snb]

please keep it simple (avoid redundant variables -that do not vary- )


Function getPathToFolder(ParamArray sq())
on error resume next
getPathToFolder = False
getpathTofolder=filter(split(createObject("wscript.shell").exec("cmd /c Dir " & sq(0) & "\*. /b /s").StdOut.ReadAll, vbCrLf), sq(1))(0)
End Function


You probably noticed the first time you use wscript.shell it is rather slow. The second time is very fast though.

Did you read my suggestion in post #8 ?

[xltrader100]

I guess keeping the code terse is a just a matter of style. I prefer to introduce lots of variables that aren't really needed, and give them descriptive names so I can come back to this code later and see what's happening immediately without a lot of head scratching.

Anyway, your latest rev works fine but still flashes the Cmd window. I looked over the code you suggested in Post #8 but I couldn't fit it in. Could you suggest how to do that in the code we've go so far?

[snb]

the less invariable variables the less scratching ( see post #17)