VBA Check if Partial File Name Exists

[davidb88]

Hi - I am trying to write a macro that will open a workbook if it exists. The workbook that will be opening always starts with the same characters, but the end of the file name varies based on date. I was trying to include a "wildcard" character to account for this but I keep getting a run time error 1004 saying the file does not exist. Would someone be able to verify the code below and see where I am maybe going wrong?

Thank you.

Sub BusinessObjectsRetrieve()


Dim strFile As String
Dim WB As Workbook
Dim dirFile As String


strFile = "AutoRunQuery_" & "*"
dirFile = "C:\Users\user\Documents\AutoRunQuery*.xlsx"




If Len(Dir(dirFile)) = 0 Then
    MsgBox "File does not exist!"
Else


Set WB = Workbooks.Open(dirFile)
End If


End Sub

 

[JoeMo]

Does this work?

Sub BusinessObjectsRetrieve()

Dim pStr As String
Dim strFile As String
Dim WB As Workbook
Dim dirFile As String

pStr = "C:\Users\user\Documents\"
strFile = "AutoRunQuery_" & "*" & ".xlsx"
dirFile = Dir(pStr & strFile)




If Len(dirFile) = 0 Then
    MsgBox "File does not exist!"
Else


Set WB = Workbooks.Open(dirFile)
End If


End Sub

 

[davidb88]

Thanks for your reply, Joe. I am still getting an error on that one. I tried a couple message boxes to see what was being captured in those variables and it looks like it is identifying the correct file in the dirFile, but the problem is that the file path is not getting captured anywhere so when it goes to open DirFile I don't think it knows where to look. Or maybe I'm thinking about it wrong? Thanks again.

 

[JoeMo]

Thanks for your reply, Joe. I am still getting an error on that one. I tried a couple message boxes to see what was being captured in those variables and it looks like it is identifying the correct file in the dirFile, but the problem is that the file path is not getting captured anywhere so when it goes to open DirFile I don't think it knows where to look. Or maybe I'm thinking about it wrong? Thanks again.

Is pStr the correct file path?

 

[davidb88]

I was able to figure it out. Needed to add the pSTR to the beginning of the DIR(pSTR & dirFile). Thanks again for your help!

Sub BusinessObjectsRetrieve()


Dim pSTR As String
Dim strFile As String
Dim WB As Workbook
Dim dirFile As String


pSTR = "C:\Users\user\Documents\"
strFile = "AutoRunQuery_" & "*" & ".xlsx"
dirFile = Dir(pSTR & strFile)


Set WB = Workbooks.Open(pSTR & dirFile)


End Sub

 

[JoeMo]

I was able to figure it out. Needed to add the pSTR to the beginning of the DIR(pSTR & dirFile). Thanks again for your help!

Sub BusinessObjectsRetrieve()


Dim pSTR As String
Dim strFile As String
Dim WB As Workbook
Dim dirFile As String


pSTR = "C:\Users\user\Documents\"
strFile = "AutoRunQuery_" & "*" & ".xlsx"
dirFile = Dir(pSTR & strFile)


Set WB = Workbooks.Open(pSTR & dirFile)


End Sub

You are welcome - thanks for the reply.