Freddie has attended:
Excel VBA Introduction course
Finding a file with part of its name
Hi Guys
I need to create a macro that is able to open up prior month's file based on having the following words within its name - "BAD Posting File"
What I have currently is the below:
FileYear = Year(Date)
strFileMonth = Format(Date - 1, "YYYYMM")
FileDate = Format(Date - 1, "dd.mm.yyyy")
FilePath = "L:\ClientInterest\CI shared\~ Month End Interest Process ~\3) LDN\LDN Month End\" & FileYear & "\" & strFileMonth & "\*BAD Posting*.xls"
Workbooks.Open (FilePath)
End Sub
It seems to work up until the "*BAD Posting*.xls" part as it is unable to recognise this. What am I doing wrong here?
Many thanks.
Freddie
RE: Finding a file with part of its name
HI Freddy,
Thank you for the forum question.
You cannot use asterisk (wildcard) in a path, but you can use the Dir function. The Dir function will find the file name and then you can put it in the path.
Try this:
FileYear = Year(Date)
strFileMonth = Format(Date - 1, "YYYYMM")
FileDate = Format(Date - 1, "dd.mm.yyyy")
Filename=dir("L:\ClientInterest\CI shared\~ Month End Interest Process ~\3) LDN\LDN Month End\" & FileYear & "\" & strFileMonth & "\*BAD Posting*.xls")
FilePath = "L:\ClientInterest\CI shared\~ Month End Interest Process ~\3) LDN\LDN Month End\" & FileYear & "\" & strFileMonth & "\" & Filename
Workbooks.Open (FilePath)
I hope this will do the job.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
