open workbook using wildcard

[Ben]

Hello, I'd like a line of code to open a workbook whose middle characters
change daily. The first 4 characters remain constant. Let's say the first
four characters of the file are "data" and this info is in a cell named
"sourcefile". Can I use wildcards for the rest of the filename with
WORKBOOKS.OPEN FILENAME
Thank you

 

[JMB]

You could loop through the files in a specified folder and use the like
operator to find the one you want. You'd need to change the Path constant to
whatever you need. You could also prompt the user for the filename using
GetOpenFileName method (might be better choice if the path is subject to
change or there could be multiple filenames that match your criteria). This
example only opens the first file it finds that matches the criteria.


Sub test()
Const Path = "C:\temp\test"
Dim FName As String
Dim FSO As Object
Dim Folder As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder(Path)
FName = Range("SourceFile").Value

For Each x In Folder.Files
If UCase(x.Name) Like UCase(FName & "*" & ".xls") Then
Workbooks.Open (Path & Application.PathSeparator _
& x.Name)
Exit For
End If
Next x

End Sub

 

[William Benson]

Hi, here is one solution perhaps, pls read the comment lines...

Sub OpenWild()
Dim Wk As Workbook
Dim WkToOpen As String

Set Wk = ActiveWorkbook

'Have stored the path in range PathLastSaved and
'Have stored the changing characters in range Dynamic_Characters
'Prefix and Suffix shown below are for example only

WkToOpen = _
Range("PathLastSaved").Cells(1, 1).Value _
& "Prefix_" _
& Range("Dynamic_Characters").Cells(1, 1).Value _
& "_Suffix.xls"
On Error Resume Next
Workbooks.Open Filename:=WkToOpen

On Error GoTo 0
End Sub

 

[William Benson]

JMB, I like this solution if Ben wanted to open all files, but I thought he
said that "the" filename was changing daily so I didn't bother with a loop.
Anyway, why is there an object like 'application.pathseparator' in place of
just "\" ? Just curious, never saw it used, thanks

 

[Dave Peterson]

I'm not JMB, but that code opens the first workbook it finds that matches that
pattern--then it hits that "exit for" and leaves the loop.

Application.pathseparator can be used when you're developing code that will run

 

[William Benson]

And I'm not Lisa (irrelevant, but a nice oldie) ... thanks for pointing that
out!

Thanks for the additional explanation.

Bill

 

[JMB]

I was assuming the dynamic part of the filename was unknown. Otherwise, why
not type in the entire filename in SourceRange cell? Or, if you had to
browse through windows explorer to figure out the dynamic portion, why not
open the file while you're in explorer?

As I mentioned, wildcards aren't always a good idea (especially if there's a
possibility of multiple files matching the criteria - in which case, I
wouldn't use it unless I loaded the filenames matching the pattern into a
combobox to allow the user to select the file they needed). Could also get
the filename via inputbox or getopenfilename method or filesearch method
(which supports wildcards, not sure to what extent).

Of course, if the dynamic part of the file name is known prior to run time,
it would be better (and less coding) to use the actual file name.

 

[William Benson]

great strategies!

I was assuming the dynamic part of the filename was unknown. Otherwise,
why
not type in the entire filename in SourceRange cell? Or, if you had to
browse through windows explorer to figure out the dynamic portion, why not
open the file while you're in explorer?

As I mentioned, wildcards aren't always a good idea (especially if there's
a
possibility of multiple files matching the criteria - in which case, I
wouldn't use it unless I loaded the filenames matching the pattern into a
combobox to allow the user to select the file they needed). Could also
get
the filename via inputbox or getopenfilename method or filesearch method
(which supports wildcards, not sure to what extent).

Of course, if the dynamic part of the file name is known prior to run
time,
it would be better (and less coding) to use the actual file name.