open workbook using wildcard
 

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

open workbook using wildcard
 

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


"Ben" wrote:

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

open workbook using wildcard
 

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


"Ben" wrote in message
...
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

open workbook using wildcard
 

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
"JMB" wrote in message
...
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


"Ben" wrote:

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

open workbook using wildcard
 

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
on both windows (which uses the backslash "\") and Macs (which uses the colon
":"--I think!).

William Benson wrote:

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
"JMB" wrote in message
...
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


"Ben" wrote:

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


--

Dave Peterson

open workbook using wildcard
 

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

Thanks for the additional explanation.

Bill

"Dave Peterson" wrote in message
...
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
on both windows (which uses the backslash "\") and Macs (which uses the
colon
":"--I think!).

William Benson wrote:

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
"JMB" wrote in message
...
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


"Ben" wrote:

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


--

Dave Peterson

open workbook using wildcard
 

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" wrote:

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
"JMB" wrote in message
...
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


"Ben" wrote:

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

open workbook using wildcard
 

great strategies!


"JMB" wrote in message
...
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" wrote:

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
"JMB" wrote in message
...
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


"Ben" wrote:

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