Solved

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

Posted on 2014-10-30
14
2,243 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 

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
 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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 Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

820 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