Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Oct 2016
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use of Wildcard in File Name with Workbooks.Open in VBA Excel 2013

    I currently have code that includes the line:
    Workbooks.Open Filename:=Company + ".xlsx",ReadOnly:=True, UpdateLinks:=0

    Company is a string that is fed in from a different part of the code.

    For many years, that filename string has not changed and my code has worked perfectly. However, the department providing the files is changing their naming convention and the names will change monthly. The first two characters of the file name - the actual company number - will not change. For example, file name that used to be ABCCompany.xlsx could now be 12-ABCCompany_0916_Revised.xlsx one month and 12_ABCCcompany_1016.xlsx the next. There will only be one file for company 12 in the source directory and it will always start with 12. The same for all the other companies. We have no control over what will come after the number.

    Is there a way to feed 12* as Company in the variable shown above or some other way to get a wild card in there? I tried 12* without + ".xlsx" and that did not work.

    I do not want to either rename the source files every month or rewrite my code to account for the varying names.

    Thank you for your suggestions.

    Nancy

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Hi Nancy,

    Welcome to the forum.

    A simple way to do this is to add one line of code to your existing macro. The code line calls a function called FindFile that passes a parameter that is your path string (assuming this doesn't change according to your description). The function then cycles through the files in the folder until it finds a file name that begins with "12". The function then returns the filename back to your code assigned to the Company variable. You do not need the + ".xlsx". It is included in the variable's value.

    You must reference the Microsoft Scripting Runtime.

    HTH,
    Maud

    Code:
    'YOUR CODE
         Company = FindFile("C:\Users\Maudibe\Desktop\Excels") 'CHANGE TO YOUR PATH
    'YOUR CODE
    Code:
    Public Function FindFile(Path)
    '--------------------------------------------------------------------
    'DECLARE AND SET VARIABLES
     Application.ScreenUpdating = False
        Set MyObject = New Scripting.FileSystemObject
        Set SrcFld = MyObject.GetFolder(Path)
        Application.ScreenUpdating = False
    '--------------------------------------------------------------------
    'FIND PREFIXED XLSX FILE
        For Each file In SrcFld.Files
            If Left(file.Name, 2) = "12" And UCase(Right(file.Name, 4)) = "XLSX" Then
                FindFile = file.Name
                GoTo skip
            End If
         Next file
        MsgBox "No file 12*.xlsx found"
    skip:
    Application.ScreenUpdating = True
    End Function
    VB Editor > Tools > References
    MSscripting Runtime.png
    Last edited by Maudibe; 2016-10-06 at 18:55.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Wouldn't Dir be simpler as it accepts wildcards already (unless you're on a Mac)?
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    New Lounger
    Join Date
    Oct 2016
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maudibe,

    Thank you for the suggestion. The path does change monthly, and I do not want to have to hard code that in.

    The files will always be there, so that's not a problem. We will not run the process until all have been created for the month.

    Nancy

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Code:
    Dim sFilename as String
    sFilename = Dir("C:\some path\" & Company & "*.xlsx")
    if sFilename <> vbNullString then
    Workbooks.Open Filename:=sFilename,ReadOnly:=True, UpdateLinks:=0
    ... rest of code
    End If
    where the Company variable has your company number in it.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    New Lounger
    Join Date
    Oct 2016
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Perfect!

    I added a variable to pull in the current path, and it works great.

    Thank you so much, Rory.

    I appreciate your fast response.

    Nancy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •