asked on

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

ChloesDad

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?

Justincut

ASKER

Yep. That is correct. What alternatives are there?

ChloesDad

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

Justincut

ASKER

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?

Glenn Ray

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

Glenn Ray

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

ChloesDad

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

Justincut

ASKER

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

ASKER CERTIFIED SOLUTION

ChloesDad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Justincut

ASKER

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?

Justincut

ASKER

I've requested that this question be closed as follows:

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

for the following reason:

Excellent

ChloesDad

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.

Justincut

ASKER

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?

ChloesDad

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"