Excel - Knowing 5 first digits, find a path to path a file using VBA and Regex

Question

I'm working in an environment with always follow this hierarchy:

F:\Client Documents\"JobCategory"\"JobNumber-ClientName"\Estimate

Being:

JobNumber always the 2 digits year follow by 3 sequential digits eg: 15255 (255th job of 2015 )
JobCategory is used to separate jobs by hundreds, eg, the above job # will be found in folder 15200
ClientName is the problem, where obviously can be any name...

So at the moment, I have to type correctly the whole job number and name and trough excel I extract the job category and so on... but in order to automatize some other macros. Is there any way where the script finds (or search/match) the first 5 digits and if it match, use that name as a path?

Eg. if I know job # 15255, F:\Client Documents\15200\15255-JohnSmith or \15255-JohnSnow will be a match?

If relevant... the VBA script I have so far to save my workbook is:

Dim JobCat As String, JobDetails As String
 JobCat = Sheet12.Range("P4").Text
  JobNumber = Sheet12.Range("P5").Text

ActiveWorkbook.SaveAs Filename:= _
    "F:\Client Documents\" & JobCat & "\" & JobDetails & "\Estimate.xlsm" _
    , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

Where in P5 I type the hole JobNumber-ClientName and P4 extract its Number category.

Any help, even to clarify if it's possible will be helpful

Please clarify: 1) What is the exact input string, 2) What is the exact output? Have a [look here](https://regex101.com/r/tU8pE0/1), please fork or update to show what you have and what you need. — Wiktor Stribiżew, Jul 10 '15 at 15:04
See this question http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba for a way to loop through files. See the second (not accepted) answer for a very speedy way. You *could* use a regex, but this method is much simpler and faster. — OpiesDad, Jul 10 '15 at 15:06

score 0 · Answer 1 · answered Jul 10 '15 at 15:15

You can use the Dir function to get the first Folder that matches your search string (if you run Dir without parameters after the first execution, you will get the next Folder that matches the search string)

This should work for you:

Public Sub saveMe()
Dim JobCat As String, JobDetails As String
Dim Path As String

JobCat = Sheet12.Range("P4").Text
JobNumber = Sheet12.Range("P5").Text

Path = Dir("F:\Client Documents\" & JobCat & "\" & JobDetails & "*", vbDirectory)

If Path <> "" Then
    ActiveWorkbook.SaveAs Filename:= _
        Path & "\Estimate.xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End If
End Sub

thanks for your help, your script can find the name string but error 1004 is returning when try to save. I am using: Path = Dir("C:\test\" & JobCat & "\" & JobNumber & "*", vbDirectory) for test in my own pc, but when I run the macro, looks like after it finds the name, it points to my user\Documents directory. Precisely: C:\Users\diego\Documents\15116-Mr Smith\FF83F380 As you can see it finds correctly the JobNumber+Client Name "15116-Mr Smith" but is trying to save on my documents.. also, don't know where it got that "FF83F380" from ? — Diego, Jul 11 '15 at 02:33

score 0 · Answer 2 · answered Jul 11 '15 at 03:17

Thanks to @Dorian , had to modify a little bit it's path so my final script is:

Public Sub saveMe()
Dim JobCat As String, JobNumber As String
Dim Path As String

JobCat = Sheet1.Range("A5").Text
JobNumber = Sheet1.Range("A4").Text

Path = Dir("C:\test\" & JobCat & "\" & JobNumber & "*", vbDirectory)

If Path <> "" Then
 ActiveWorkbook.SaveAs Filename:= _
    "C:\test\" & JobCat & "\" & Path & "\Estimate.xlsm" _
    , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End If
End Sub

sorry, i didn't mind the function only returns the folder name, but not the entire path. — Dorian, Jul 11 '15 at 12:20

Excel - Knowing 5 first digits, find a path to path a file using VBA and Regex

2 Answers2