CODEWITHSUNDEEP
excelvba
MCSythera
MCSythera

Reputation: 55

Skipping files in a directory with same name from inputbox

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.

Upvotes: 2

Views: 51

Answers (2)

brettdj
brettdj

Reputation: 55672

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

Upvotes: 2

David912
David912

Reputation: 396

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...

Upvotes: 0

Related Questions