98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Finding a file with part of its name
Finding a file with part of its name
Resolved · High Priority · Version 2010
Thu 26 Nov 2015, 17:09Delegate Freddie said...
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
Fri 27 Nov 2015, 12:27Trainer Jens said...
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
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Change the default location for opening and saving spreadsheetsIf you are always opening spreadsheets from and/or saving documents to a specific location that is not My Documents, save time by setting this folder as the default for opening files from and saving files to. |
