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?
[VBA]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
[/VBA]
probably a oneliner suffices:
[VBA]
Sub tst()
msgbox = filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir C:\Projectfoler\*. /b /s").stdout.readall, vbCrLf),activecell.value)(0)
end sub
[/VBA]
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.
How would I test the one liner for success/failure?
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?
[VBA]Quote:
Originally Posted by xltrader100
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
[/VBA]
[VBA]Quote:
Originally Posted by xltrader100
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.tx t").readall, vbCrLf)(0)
End Sub
[/VBA]
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.
[vba]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[/vba]
[vba]Sub M_snb()
MsgBox F_snb("G:\OF", "codeA")
End Sub[/vba]
[vba]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[/vba]
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?
[vba]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
[/vba]
please keep it simple (avoid redundant variables -that do not vary- )
[vba]
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
[/vba]
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 ?
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?
the less invariable variables the less scratching ( see post #17)