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

Topic

New Reply

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

Reply | Quote

Replies

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:

[COLOR=”#006400″]’YOUR CODE[/COLOR]
     Company = FindFile(“C:UsersMaudibeDesktopExcels”) [COLOR=”#006400″]’CHANGE TO YOUR PATH[/COLOR]
[COLOR=”#006400″]’YOUR CODE[/COLOR]

Code:

Public Function FindFile(Path)
[COLOR=”#006400″]’——————————————————————–
‘DECLARE AND SET VARIABLES[/COLOR]
 Application.ScreenUpdating = False
    Set MyObject = New Scripting.FileSystemObject
    Set SrcFld = MyObject.GetFolder(Path)
    Application.ScreenUpdating = False
[COLOR=”#006400″]’——————————————————————–
‘FIND PREFIXED XLSX FILE[/COLOR]
    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
45712-MSscripting-Runtime

Reply | Quote

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

Reply | Quote

P.S. You do not need the + “.xlsx”. It is included in the variable Company

Reply | Quote

Wouldn’t Dir be simpler as it accepts wildcards already (unless you’re on a Mac)?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote