Solved

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

Posted on 2014-11-04
13
146 Views
Last Modified: 2014-11-18
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
Comment
Question by:Justincut
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40422160
Filename = SelectMatchingWindow(Format(Now, "mm.dd.yy") - Daily GMSP Balance Sheet.xlsm (sent).xlsx
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40422163
If that doesn't work try
Filename = SelectMatchingWindow(Format(Now, "mm.dd.yy") & " - Daily GMSP Balance Sheet.xlsm (sent).xlsx"
0
 

Author Comment

by:Justincut
ID: 40430335
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 48

Expert Comment

by:Martin Liss
ID: 40430348
Where will the prefix com from? User input? Call value? Or?
0
 

Author Comment

by:Justincut
ID: 40431113
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
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40431286
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
 

Author Comment

by:Justincut
ID: 40436116
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
 
LVL 48

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40436243
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
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40445872
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
 

Author Comment

by:Justincut
ID: 40448231
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
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40448276
Excel functions are not my area but I'm sure someone else will help.
0
 

Author Comment

by:Justincut
ID: 40448328
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
 

Author Comment

by:Justincut
ID: 40449182
Hi Martin, can you give me a hand with the question i just posted? Cheers Justin
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question