CODEWITHSUNDEEP
excelvba
PedroB
PedroB

Reputation: 77

Open a Workbook with dynamic name

I am working in a project of VBA, and I need to open some Workbooks, but the name is kind of dynamic, but only the last name. Sometimes the name comes like "OnHand 066 May" and sometimes with "OnHand 006 Jun"

Dim Dir As String
Dir = ActiveWorkbook.Path

Upvotes: 0

Views: 248

Answers (1)

braX
braX

Reputation: 11755

Do not use Dir as a variable name. It is a reserved function name.

Use a wildcard with the Dir function to find a file that matches.

The Dir function will not return a full path, so you have to append it again if you want to actually open the file.

Let's assume there is only one file in that folder that matches, as you did not specify that in your question.

Dim sFile As String

sFile = Dir(ActiveWorkbook.Path & "\OnHand*.xls*")

If sFile <> "" Then
  ' if you get an error on the next line, someone else may have it open already
  Debug.Print "About to open: " & ActiveWorkBook.Path & "\" & sFile
  WorkBooks.Open ActiveWorkBook.Path & "\" & sFile
Else
  MsgBox "Cannot find a file like that"
End If

Upvotes: 1

Related Questions