How to use a wildcard * in VBA for a File Path?

Hi Guys, I have a Excel file that I am trying to copy and  paste to  from another Excel file via a Macro in which the code  I have given a wildcard as the File name ending changes at random,but I get a "Runtime Error:Subscript out of Range" message every time I run the Macro. Can anyone help? See code below:

Sheets("Daily Commentary").Select
    Windows("BluesFinal-EMEAMortgages" & "*.xlsx").Activate

    Sheets("B7_CONS-MORTMP_EMEA_MP, CB, SP").Select

    ActiveSheet.Outline.ShowLevels RowLevels:=3

 

    Range("M8").Select

    Range(Selection, Selection.End(xlDown)).Select

    Range(Selection, Selection.End(xlToLeft)).Select

    Selection.SpecialCells(xlCellTypeVisible).Select

    Selection.Copy

    Windows("Mortgages Commentary*.xlsx").Activate

    Range("A11").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Windows("BluesFinal-EMEAMortgages*.xlsx").Activate

    ActiveWindow.SmallScroll ToRight:=7

    Range("U8").Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.SpecialCells(xlCellTypeVisible).Select

    Application.CutCopyMode = False

    Selection.Copy

    Windows("Mortgages Commentary*.xlsx").Activate
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.

ChloesDadCommented:
The error is here

Windows("Mortgages Commentary*.xlsx").Activate

You can't select a window with a wildcard as if there was more than one your code would not know which to select.

Will there only be one window that matches this file name?
0
JustincutAuthor Commented:
Yep. That is correct. What alternatives are there?
0
ChloesDadCommented:
You can iterate through the windows and check the name of each one, if one matches what you are after then select that one.

Here is a subroutine that you call by passing in the filename that you want to look for, it works by finding the last window that it finds with a caption that matches the entered text from the start of the caption.

SelectMatchingWindow ("BluesFinal-EMEAMortgages")

Open in new window


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

Open in new window

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.

JustincutAuthor Commented:
Hi, this is too complicated. There is only 2 files with Wildcard in my Code:

  Windows("Mortgages Commentary*.xlsx), which is the Excel file with the Macro in and
Windows("BluesFinal-EMEAMortgages" & "*.xlsx"), which is the Excel file we are copying from.


Can you simplify the code?
0
Glenn RayExcel VBA DeveloperCommented:
ChloesDad is correct:  you cannot have a wildcard character in a filename, nor reference a file in that manner.

It sounds like you have version numbers (or dates) appended to the base names of the workbooks in-question. You need to capture the exact name of the workbooks immediately upon opening and save them to a variable.  Then you can call them up as needed.

At the start of the macro, you'd add:
Dim strMortComm as String
Dim strBluesFinal as String
.
.
strMortComm = ActiveWorkbook.Name

Open in new window


Then,immediately after the "BluesFinal..." workbook is opened (presumably from the macro), you'd assign a variable to that name:
strBluesFinal = ActiveWorkbook.Name

Open in new window


When you want to activate either workbook, you'd use these commands instead:
Windows(strMortComm).Activate
.
.
.
Windows(strBluesFinal).Activate

Open in new window


Regards,
-Glenn
1
Glenn RayExcel VBA DeveloperCommented:
Here's a revised sample of your code with the above variables added in context:
Sub sample()
    Dim strMortComm, strBluesFinal As String
    
    'insert this statement at the very top of the macro
    strMortComm = ActiveWorkbook.Name
    
    Sheets("Daily Commentary").Select
    
    'immediately after opening the BluesFinal-EMEAMortgages... workbook add this statement:
    strBluesFinal = ActiveWorkbook.Name
    
    Windows(strBluesFinal).Activate
    Sheets("B7_CONS-MORTMP_EMEA_MP, CB, SP").Select
    ActiveSheet.Outline.ShowLevels RowLevels:=3
    
    Range("M8", Range("M8").End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Windows(strMortComm).Activate
    Range("A11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    Windows(strBluesFinal).Activate
    Range("U8", Range("U8").End(xlDown)).SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    
    Windows(strMortComm).Activate
End Sub

Open in new window

0
ChloesDadCommented:
Using a function is the correct way to go, you call the function twice in your code replacing the lines where you use the wildcards

Windows("BluesFinal-EMEAMortgages" & "*.xlsx").Activate

becomes

SelectMatchingWindow ("BluesFinal-EMEAMortgages")

Open in new window


and

Windows("Mortgages Commentary*.xlsx").Activate

becomes


SelectMatchingWindow ("Mortgages Commentary")

Open in new window

0
JustincutAuthor Commented:
Hi, the file called "BluesFinal-EMEAMortgages" has a randon number at the end which changes every day (thus the wildcard) and only recieved via e-mail so I never know the full name of the file so when I click the macro it will be already open everyday so how can I assign a variable to its name without a wildcard? Any ideas? Justin
0
ChloesDadCommented:
So you need the actual file name, then using the function, that I supplied makes this very easy.

Filename =SelectMatchingWindow ("BluesFinal-EMEAMortgages")

Open in new window


and change the function to

Function SelectMatchingWindow(MatchingFilename As String) as String

Dim NumberOfWindows As Integer
Dim Count1 As Integer
Dim MatchedWindowNumber As Integer
Dim WindowName As String
dim FoundName as string

NumberOfWindows = Windows.Count

For Count1 = 1 To NumberOfWindows

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

Next Count1

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

Return FoundName

End Function

Open in new window

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
JustincutAuthor Commented:
Ok, the file name today is called   BluesFinal-EmeaMortgages_31_Oct_2014_865841515.xlsx" and the last 9 numerals change on a daily basis and the other Excel file I have to copy from is called "BluesWIPandBvGVariance-EMEAMortgages_30_Oct_2014_182639827" where the last 9 numerals also change on a dilay basis. They are both opened via Outlook so they have no path. The File that I paste them to and where the Function will reside is called "Mortages Commentary YYYY MM NN. So how will the code be adapted for this?
0
JustincutAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Justincut's comment #a40419288

for the following reason:

Excellent
0
ChloesDadCommented:
If both files are open in excel then you don't need to know the filename, the function that I supplied will set the active window to the required window.

Is there more to your original question than just activating a window using a wildcard.
0
JustincutAuthor Commented:
Hi, they work fine, but I have 1 more problem. One more file I need to copy and paste from, the prefix is variable and thus the Function does not work. Can you adapt the function so this path works:

'Windows("10.29.14 - Daily GMSP Balance Sheet.xlsm (sent).xlsx").Activate

eg. "10.29.14" is Variable and the "Daily GMSP Balance Sheet.xlsm (sent).xlsx" is the ever present name in the path?
0
ChloesDadCommented:
In the function it checks for the string position being 1, if you change this to > 0 then it will find those where the variable part is at the start of the string rather than at the end. Just make the call as before with the parameter "Daily GMSP Balance Sheet"
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.