Finding a file with part of its name

Resolved · High Priority · Version 2010

Thu 26 Nov 2015, 17:09 Delegate 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:27 Trainer 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 courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Change the default location for opening and saving spreadsheets

If 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.

Here's how:
1. Go to Tools - Options.

2. Select the General tab.

3. Enter the pathname of the folder you wish to make the default in the Default File Location box (hint: it will be easier to use Windows Explorer to navigate to this folder, then copy and paste the pathname from the address bar at the top of the Windows Explorer screen).

4. Click OK.

You have now changed the default folder for opening and saving spreadsheets.

View all Excel hints and tips