• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 156
  • Last Modified:

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
0
Justincut
Asked:
Justincut
  • 7
  • 6
1 Solution
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now