Search for a folder and return full path.

[jkaufman]

I know the folder name that I am looking for and I know the parent directory name. The folder I am looking for is several sub folders deep in an array of sub folders beneath the parent directory. With VBA code I would like to be able to enter the parent directory, enter the sub directory name and recieve the resultant full path to that folder location.

Thanks in advance for your help.

[mancubus]

like this?


Sub FullPathName()
'http://vbaexpress.com/forum/showthread.php?t=39864

Dim NumFolder As Integer
Dim FullPath As String

On Error Resume Next
NumFolder = InputBox(Prompt:="Total Number of Parent & Subfolders, Start With Drive Letter", _
Title:="SUB FOLDER LEVEL")

If NumFolder = False Then Exit Sub 'Cancelled
On Error GoTo 0

For i = 1 To NumFolder
FullPath = FullPath & InputBox(Prompt:="Drive (add ':' after drv letter) or Folder Name", _
Title:="FULL PATH") & "\"
Next

Range("A1").Value = FullPath 'to cell
'OR
MsgBox FullPath 'to msgbox
'OR
Debug.Print FullPath 'to immediate window in VBE

End Sub

[jkaufman]

I have a folder under the J: drive called "jobs". Within the "jobs" directory are all the folders to projects in numerical order by job number. The are ordered numerically in groups first in folders by 1000 jobs and then in subfolders by 100 jobs. So the tree to job number "012021" is:

J:\Jobs\12000-12999\12000-12099\012021

I know that all jobs are uner the J:\Jobs and I know the folder title "012021". I am struggling with how to navigate to that specific folder within the tree. I need to access a specific file in the job folder within the code.

Thanks again for your help.

[mancubus]

i'm not sure i'm understanding your requirement.

1- you know the full path to file which is "J:\Jobs\12000-12999\12000-12099\012021"


strPath = "J:\Jobs\12000-12999\12000-12099\012021"
strFile = "specificFile.xlsx"

MsgBox strPath & "\" & strFile
'Workbooks.Open strPath & "\" & strFile


2- you know the part of the full path to file which is "J:\Jobs\.....-.....\.....-.....\012021" and look for a way that returns values for dots.
to do so, you need a rule.

perhaps:

Dim strPath As String, strFile As String, strParPath As String
Dim strUltPath As String, strMid As String, strMidPath As String

strParPath = "J:\Jobs\"
strUltPath = "012021" '6 dgt num as text
strFile = "specificFile.xlsx"

strMid = Mid(strUltPath, 2, 2) 'return "12" from "012021"
strMidPath = strMid & "000-" & strMid & "999\" & strMid & "000-" & strMid & "099\"

strPath = strParPath & strMidPath & strUltPath

MsgBox strPath & "\" & strFile
'Workbooks.Open strPath & "\" & strFile



3- you want something else... :dunno :dunno

[jkaufman]

"2- you know the part of the full path to file which is "J:\Jobs\.....-.....\.....-.....\012021" and look for a way that returns values for dots.
to do so, you need a rule. "

It did produce the path for the example I gave you. Which is perfect. I think you are the exact right path. The next job number, "010777" should return a path of "J:\Jobs\10000-10999\10700-10799\010777" but returned
"J:\Jobs\10000-10999\10000-10099\010777"

I will play with your code and see if I can't get to this result. Thanks so much for your help.

Jeff

[mancubus]

ok.

you may use strMid2 = Mid(strUltPath, 2, 2) to return 10
and strMid3 = Mid(strUltPath, 2, 3) to return 107




... not a good coding... for sure... :whistle:

[mancubus]

strMid2 = Mid(strUltPath, 2, 2) 'return "10" from "010777"
strMid3 = Mid(strUltPath, 2, 3) 'return "107" from "010777"
strMidPath = strMid2 & "000-" & strMid2 & "999\" & strMid3 & "00-" & strMid3 & "99\"

[jkaufman]

Got it! Works great. Thanks a ton.

Jeff

[mancubus]

you're wellcome.
pls mark the thread as solved from thread tools dropdown which is above the first message.