How to adapt a SelectMatchingWindow Function code to cope with Prefixes on paths?

Hi Guys, I have a SelectMatchingWindow function, which copes well where the Variable is at the End of the path, but I have a Excel file which has a variable at the Prefix which changes on a daily basis. How can I adapt the code for this path: Variable part is the "10.29.14" which is the date

 Filename = SelectMatchingWindow("10.29.14 - Daily GMSP Balance Sheet.xlsm (sent).xlsx



Sub SelectMatchingWindow(MatchingFilename As String)

Dim NumberOfWindows As Integer
Dim Count1 As Integer
Dim MatchedWindowNumber As Integer
Dim WindowName As String

NumberOfWindows = Windows.Count

For Count1 = 1 To NumberOfWindows

    WindowName = Windows(Count1).Caption
   
    If InStr(WindowName, MatchingFilename) = 1 Then
        MatchedWindowNumber = Count1
    End If

Next Count1

If MatchedWindowNumber > 0 Then
    Windows(MatchedWindowNumber).Activate
End If



End Sub
JustincutAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Filename = SelectMatchingWindow(Format(Now, "mm.dd.yy") - Daily GMSP Balance Sheet.xlsm (sent).xlsx
0
Martin LissOlder than dirtCommented:
If that doesn't work try
Filename = SelectMatchingWindow(Format(Now, "mm.dd.yy") & " - Daily GMSP Balance Sheet.xlsm (sent).xlsx"
0
JustincutAuthor Commented:
Hi Martin, is there a way you can adapt the code so it can have any prefix, then "-Daily GMSP Balance Sheet.xlsm(sent).xlsx" like a Wildcard *?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Martin LissOlder than dirtCommented:
Where will the prefix com from? User input? Call value? Or?
0
JustincutAuthor Commented:
No, the prefix is the previous business date, but if the file get opened 2 days afterwards, its going to not pick up the correct date.
0
Martin LissOlder than dirtCommented:
Okay, here something that I modified from the web. It assumes that there's a sheet named Holidays that contains a list of holidays (don't include weekends).

Add this function
Function PreviousWorkday() As String
PreviousWorkday = WorksheetFunction.WorkDay(Date, -1, Sheets("Holidays").Range("A:A"))
End Function

Open in new window

and then replace Now in the previous solution with PreviousWorkday.
0
JustincutAuthor Commented:
Hi Martin, The Function includes the code "Instr" so how come it works like a wildcard when the ending changes but not when the prefix changes? Justin
0
Martin LissOlder than dirtCommented:
I'm not sure what you're asking but your If InStr(WindowName, MatchingFilename) = 1 Then says "If the value in MatchingFilename is found starting in position 1 of the value in WindowName then do something". If you want to know if a value is anywhere in the value to be searched then do  If InStr(WindowName, MatchingFilename) > 0 Then.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
JustincutAuthor Commented:
Hi Martin, I have just posted another question. Can you answer it? It's similar to this one. I need to do a Vlookup from one spreadsheet to another one using the Select Matching window function.
0
Martin LissOlder than dirtCommented:
Excel functions are not my area but I'm sure someone else will help.
0
JustincutAuthor Commented:
Hi Martin, its VBA. I am in a workbook called "WTD Commentary", using the Select Matching Windows Function, I then go into a spreadsheet called "Daily GSFI", I need to create a Range from A3 to I35 (name it "Region"), name it, then view -> Windows -> "WTD Commentary", put in a Formula F45 to F66 with "=Vlookup(B45, Region, 8, False). How do you do this?
0
JustincutAuthor Commented:
Hi Martin, can you give me a hand with the question i just posted? Cheers Justin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.