Solved

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

Posted on 2014-10-30
14
1,675 Views
Last Modified: 2014-11-04
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
0
Comment
Question by:Justincut
  • 6
  • 6
  • 2
14 Comments
 
LVL 15

Expert Comment

by:ChloesDad
ID: 40414742
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
 

Author Comment

by:Justincut
ID: 40415328
Yep. That is correct. What alternatives are there?
0
 
LVL 15

Expert Comment

by:ChloesDad
ID: 40415402
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
 

Author Comment

by:Justincut
ID: 40415993
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40416092
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
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40416112
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
 
LVL 15

Expert Comment

by:ChloesDad
ID: 40416343
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:Justincut
ID: 40416705
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
 
LVL 15

Accepted Solution

by:
ChloesDad earned 500 total points
ID: 40417033
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
 

Author Comment

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

Author Comment

by:Justincut
ID: 40420467
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
 
LVL 15

Expert Comment

by:ChloesDad
ID: 40420476
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
 

Author Comment

by:Justincut
ID: 40421330
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
 
LVL 15

Expert Comment

by:ChloesDad
ID: 40422423
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

757 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now