Skipping files in a directory with same name from inputbox

Question

I am creating a code that opens and imports data from a file that is selected based on a client's name. Each time a client is opened, a file is saved for them with their name and date of birth (without slashes).

A sample file would look like C:\Data\Clients\John Doe 01011900.xlsx. An InputBox on button-click provides a client's name, but what I'm getting stuck on is if there are 2 or more John Does in the folder.

Check = Application.InputBox(prompt:="What is your client's first and last name?", Type:=2)
FilePath = "C:\Data\Clients\" & Check & "*.xlsx"
If Dir(FilePath, vbDirectory) = "" Then
    Exit Sub
End If
DOB = MsgBox("Is this your client's date of birth? " & "   " & Chr(149) & " " & Mid(Dir(FilePath), Len(Dir(FilePath)) - 12, 2) & "/" & Mid(Dir(FilePath), Len(Dir(FilePath) - 10, 2) & "/" & Mid(Dir(FilePath), Len(Dir(FilePath) - 8, 4) & " " & Chr(149), vbYesNoCancel)
If DOB = vbYes Then
    Workbooks.Open (FilePath)
    'Transfer Data
    ActiveWorkbook.Close False
ElseIf DOB = vbNo Then
    'Try again.
ElseIf DOB = vbCancel Then
    'Do nothing.
End If

My confusion is occurring at the DOB = vbNo, when someone says the date of birth does not match (meaning the next client with the same name needs to be selected). Everything else works great so far, so I just need help with the re-selection of the next file with the same name.

score 2 · Accepted Answer · edited May 23 '17 at 11:59

2

You can loop through all the matches by adding a strDir = Dir at the end of your loop, which will exit after the match is met and not accepted (as the length of StrDir will be 0)

update

I realise the code looks a little strange but this is the way Dir works, ie each time it is called it looks for the same match as the initial Dir until it reaches the end of the list. See Loop through files in a folder using VBA?

ie

Do While Len(strDir) > 0
DOB = MsgBox("Is this your client's date of birth?", vbYesNoCancel)

If DOB = vbCancel Then Exit Do

If DOB = vbYes Then
    Workbooks.Open (filepath)
    ActiveWorkbook.Close False
Exit Do
End If

strDir = Dir

Loop

edited May 23 '17 at 11:59

Community

1
1

answered May 05 '16 at 06:18

brettdj

54,857
16
114
177

That was an exceptionally simple solution, though I'm not sure I understand how it works through the files. The resulting code simply substitutes strDir for the previous FilePath, includes your chunk of code, and does all the necessary steps in order. I've already tried it on a test copy and works wells. How does that code determine the next file in line to select/display? – MCSythera May 05 '16 at 16:02
@MCSythera have added a short explanation above - this is how Dir works by design. – brettdj May 06 '16 at 00:50

score 0 · Answer 2 · answered May 04 '16 at 18:07

0

This is what I would do: First, use string comparison to find all the files in the directory that starts with John Doe and store them in a dynamic array.

Use For...Each statement to go through the files, and use Dir(FilePath) LIKE "John Doe*" to find your candidates.

Then use a Do...While loop to go through the files in the array until you find your match.

I could write the entire code for you but then you'd miss all the fun...

answered May 04 '16 at 18:07

David912

378
1
2
11

I want to try this method, though it seems like a lot of blocks of code required compared to the other method given. I'll take some time to piece together a format that uses this and see how it works. – MCSythera May 05 '16 at 16:05

Skipping files in a directory with same name from inputbox

2 Answers2