Matching File Paths with WildCards

[nstratton]

I have yet another issue. I am having to adjust the code to below to compensate for two wildcard situations. First wildcard situation is looking for everything before the SupplierCode since SupplierCode is the unique identifier for the folder.

The second wildcard situation is looking for Body No within a file name.
When I originally had the code set up, I was only using wildcards around the Body No and it worked perfectly. Folder names have now changed and I am having to compensate.

When I run the code I get a Runtime Error 52. Not sure how it is possible when I copied the filepath directly from the windows dialog box. And it stops on the strFile = line

Dim sMyPath As FileDialog
Dim sPath As Variant
Dim strFolderPath As String
Dim strMessage2, strTitle2 As String
Dim fname As String
Dim strFile As String
Dim Response As String
Dim strBodyNo As String
Dim InputYear As String
Dim strSupCode As String

strSupCode = Me.SupplierCode
strBodyNo = Me.BodyNo
strMessage2 = "Would you like to open the file?"
strTitle2 = "Open Three Panel"

Set sMyPath = Application.FileDialog(msoFileDialogOpen)
strFolderPath = "R:\0.2.2 Procurement Dept - Shared\3.0 Groups\SQD\Suppliers\" & "*" & strSupCode & "\2015\FTTQ\"
Debug.Print "Looking for: " & strFolderPath & "*" & strBodyNo & "*"
strFile = Dir(strFolderPath & "*" & strBodyNo & "*")
'Debug.Print strFile
If (strFile <> "") Then
MsgBox strFolderPath
    Response = MsgBox(strMessage2, vbYesNo, strTitle2)
    If Response = vbYes Then
        Application.FollowHyperlink strFile
    End If
Else
   MsgBox "No file found", , "No File"
End If

 

[James Deckert]

i don't see this line "strFile = line"
what is the msg given for the runtime error?
what do your debug.print lines show?
do you have Option Explicit at the top to make sure all your variables are correctly typed?
I don't know when it needs to be done, but sometimes I have to change the \ to / (or vice versa) in some of my various programming languages I use. You might try it.

Upon more research, I can't find where wildcards can be used within a path name, only a file name. I can't get a wildcard to work within the immediate window for a path either. It give the 52 error.

 

[nstratton]

With that said then, since it seems like a lost cause using wildcards, what would be the recommended way to handle this? Would it require setting a file path as a field in a table and doing it that way?

 

[James Deckert]

It could be done various ways. If the folder names will change occasionally, or there is a large number of them, then the table method would work. If the folder names will stay consistent, then hard coding them in the VBA is an option. The most robust way would be to build a VBA routine which looks at all folders and checks them against your desired name and if it matches, then do the process and then look at the next folder name. If there are frequent changes to the folder names, then the robust version is the only feasible solution.
The easiest/quickest method (but maybe not the best long term solution) would be to hard code your folder names in the VBA.

 

[nstratton]

I think the biggest problem is the naming convention of the folders is not consistent (ex. Supplier_SupplierCode then the next Supplier Only) and some of the supplier have two different codes because they provide different parts. I ended up just putting them all in a table and doing it that way. There is a subfolder by year so when that changes someone will have to go in and fix it. Other than that it should be okay. They want to start using it at least by the end of the week so a quick fix method will work for now until such time a better method can be found.