CODEWITHSUNDEEP
vbaexcel
Eoin2211
Eoin2211

Reputation: 911

VBA Open a file with changing title

I need to to open a file using VBA that has a changing title. The file name is called Membership_Sales_Figures_for_14082017-20082017_104942.xls. My code is producing an error where it can't find the file. It's referring to the file as Weekly Control\Membership.xls

Sub Test()



Const fpath As String = "Weekly Control\Membership\"
Dim fname As String
Dim lname As String

' Below gives you the Monday of the week
fname = Format(Date - (Weekday(Date) + 6) + 1, "ddmmyyyy")
lname = Format(Date - (Weekday(Date)) + 1, "ddmmyyyy")
fname = "Membership_Sales_Figures_for_" & fname & "-" & lname & "*" & ".xls"
fname = fpath & fname
fname = fpath & Dir(fname)




Dim wb As Workbook
Set wb = Workbooks.Open(fname)
If wb Is Nothing Then MsgBox "File does not exist": Exit Sub

End Sub

Upvotes: 0

Views: 405

Answers (2)

user8285860
user8285860

Reputation:

You are looking for this file. "Membership_Sales_Figures_for_14082017-20082017_104942.xls"

But your code adds an "*" at the end of the file.

fname = "Membership_Sales_Figures_for_" & fname & "-" & lname & "*" & ".xls"

Remove the & "*"

Upvotes: 0

Erik A
Erik A

Reputation: 32682

Just use dir(fname) to find a file that matches your expression. Note that it returns the first found file.

fname = "Membership_Sales_Figures_for_" & fname & "-" & lname & "*" & ".xls" 
fname = fpath & fname
fname = fpath & dir(fname)

Upvotes: 3

Related Questions