VBA - Use wildcard in path name

[bshawnr]

Hello,

I am trying to open a file using VBA, but the path can be different
The pathname is long, but the basic Idea is I have a path "g:\Admin\1234_Client name\2015reports.xls"
1234 is the client's identification number, and varies for each client.
"Client Name" is the clients actual name

I want to find the file by using the identification number, and then use a wildcard for the plan name, the code I'm trying is

ASCpath = "g:\admin\" & ClientNumber & "_*\"
planspecfilename = "*PlanSpecs.xls"
planspecfullname$ = ASCpath & planspecfilename


  If Dir(planspecfullname$) > "" Then
  Workbooks.Open Filename:=planspecfullname$, UpdateLinks:=0
  Set pswb = ActiveWorkbook
  ASCTieback.Worksheets("PlanSpecImport").Range("a1:z100").Value = pswb.Worksheets("Sheet1").Range("A1:z100").Value
  ASCTieback.Activate
  pswb.Close

  Else


  MsgBox "The plan specs file does not exist.  Please check your Contract Number and Plan Number."
  GoTo TryAgain

  End If

If the only wildcard is in the filename, it works, but if the wildcard is in the Path, then it doesn't. I don't want the user to have to type in the name of the plan.

Please help,
Thanks.

 

[Debaser]

Use Dir once with just the path and wildcard there to find the actual folder name. You can then use that with Dir and the wildcard in the file name to find the file.

 

[bshawnr]

Could you provide an example of the code to use for that? After using Dir once for the path, how do you change your variable to use again for the second Dir?

 

[Debaser]

Something like this:

ASCpath = Dir("g:\admin\" & ClientNumber & "_*", vbDirectory)
If ASCPath <> vbnullstring then 
ASCpath = Dir(ASCPath & "\*PlanSpecs.xls")