I have a macro that I use to activate a couple of different workbooks. My
problem is that one of the workbooks I need to use has a dynamic name. The
name of the workbook is created by a seperate program thats I do not control
and the name is comprised of "XXX_Date of creation_Time of
Creation_Delete.xls". Is there a way to open the book using some type of
wildcard? The XXX and Delete part of the file name are constant.

Maybe you could just look for that pattern:

Option Explicit
Sub testme01()

Dim myFile As String
Dim myPath As String
Dim FoundIt As Boolean
Dim wkbk As Workbook

'change to point at the folder to check
myPath = "c:\my documents\excel"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.xls")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

FoundIt = False
Do While myFile < ""
If LCase(myFile) Like "xxx_*_delete.xls" Then
FoundIt = True
Exit Do
End If
myFile = Dir()
Loop

If FoundIt = True Then
Set wkbk = Workbooks.Open(Filename:=myPath & myFile)
End If

End Sub

You can make that pattern more complex if need be.



hshayh0rn wrote:

I have a macro that I use to activate a couple of different workbooks. My
problem is that one of the workbooks I need to use has a dynamic name. The
name of the workbook is created by a seperate program thats I do not control
and the name is comprised of "XXX_Date of creation_Time of
Creation_Delete.xls". Is there a way to open the book using some type of
wildcard? The XXX and Delete part of the file name are constant.

--

Dave Peterson

I think this is probably more complicated than I really need to make things.
I make the users open the files I need the sheet to access data from already.
I do this just because I really can't control what directory structure the
files are contained in so rather than make them to a lot of interaction I
just instruct them to open the 4 data files. Then I use an input box to grab
the number contained in the file name. After that I use the following
statement to acftivate the workbook and grab the data I need from it:

Windows(BankNum & "-UserReport.csv").Activate

The problem is I tired to use:

Windows(BankNum & * & "Delete.csv").Activate and I tried
Windows(BankNum & "*delete.csv").Activate


to activate the 4th file that had the date and time between the banknumber
and the word delete but neither of the above statements worked. If there a
simpler way of using this already open worksheet than running all of the code
you suggested?


"Dave Peterson" wrote:

Maybe you could just look for that pattern:

Option Explicit
Sub testme01()

Dim myFile As String
Dim myPath As String
Dim FoundIt As Boolean
Dim wkbk As Workbook

'change to point at the folder to check
myPath = "c:\my documents\excel"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.xls")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

FoundIt = False
Do While myFile < ""
If LCase(myFile) Like "xxx_*_delete.xls" Then
FoundIt = True
Exit Do
End If
myFile = Dir()
Loop

If FoundIt = True Then
Set wkbk = Workbooks.Open(Filename:=myPath & myFile)
End If

End Sub

You can make that pattern more complex if need be.



hshayh0rn wrote:

I have a macro that I use to activate a couple of different workbooks. My
problem is that one of the workbooks I need to use has a dynamic name. The
name of the workbook is created by a seperate program thats I do not control
and the name is comprised of "XXX_Date of creation_Time of
Creation_Delete.xls". Is there a way to open the book using some type of
wildcard? The XXX and Delete part of the file name are constant.

--

Dave Peterson

Dave,

When I run this modified code:

Option Explicit
Sub testme01()

Dim myFile As String
Dim myPath As String
Dim FoundIt As Boolean
Dim wkbk As Workbook

'change to point at the folder to check
myPath = "D:\my documents\excel"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.csv")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

FoundIt = False
Do While myFile < ""
If LCase(myFile) Like "delete.csv" Then
FoundIt = True
Exit Do
End If
myFile = Dir()
Loop

If FoundIt = True Then
Set wkbk = Workbooks.Open(Filename:=myPath & myFile)
End If

End Sub

Nothing happens. I don't get an error and the file *delete.csv doesn't open.
Is there something missing from the code?

"Dave Peterson" wrote:

Maybe you could just look for that pattern:

Option Explicit
Sub testme01()

Dim myFile As String
Dim myPath As String
Dim FoundIt As Boolean
Dim wkbk As Workbook

'change to point at the folder to check
myPath = "c:\my documents\excel"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.xls")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

FoundIt = False
Do While myFile < ""
If LCase(myFile) Like "xxx_*_delete.xls" Then
FoundIt = True
Exit Do
End If
myFile = Dir()
Loop

If FoundIt = True Then
Set wkbk = Workbooks.Open(Filename:=myPath & myFile)
End If

End Sub

You can make that pattern more complex if need be.



hshayh0rn wrote:

I have a macro that I use to activate a couple of different workbooks. My
problem is that one of the workbooks I need to use has a dynamic name. The
name of the workbook is created by a seperate program thats I do not control
and the name is comprised of "XXX_Date of creation_Time of
Creation_Delete.xls". Is there a way to open the book using some type of
wildcard? The XXX and Delete part of the file name are constant.

--

Dave Peterson

You changed the code:

If LCase(myFile) Like "xxx_*_delete.xls" Then
to
If LCase(myFile) Like "delete.csv" Then

Was there a reason?

When you changed the code, you're really only looking for delete.csv. It has to
match exactly. Is that what you wanted.

hshayh0rn wrote:

Dave,

When I run this modified code:

Option Explicit
Sub testme01()

Dim myFile As String
Dim myPath As String
Dim FoundIt As Boolean
Dim wkbk As Workbook

'change to point at the folder to check
myPath = "D:\my documents\excel"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.csv")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

FoundIt = False
Do While myFile < ""
If LCase(myFile) Like "delete.csv" Then
FoundIt = True
Exit Do
End If
myFile = Dir()
Loop

If FoundIt = True Then
Set wkbk = Workbooks.Open(Filename:=myPath & myFile)
End If

End Sub

Nothing happens. I don't get an error and the file *delete.csv doesn't open.
Is there something missing from the code?

"Dave Peterson" wrote:

Maybe you could just look for that pattern:

Option Explicit
Sub testme01()

Dim myFile As String
Dim myPath As String
Dim FoundIt As Boolean
Dim wkbk As Workbook

'change to point at the folder to check
myPath = "c:\my documents\excel"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.xls")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

FoundIt = False
Do While myFile < ""
If LCase(myFile) Like "xxx_*_delete.xls" Then
FoundIt = True
Exit Do
End If
myFile = Dir()
Loop

If FoundIt = True Then
Set wkbk = Workbooks.Open(Filename:=myPath & myFile)
End If

End Sub

You can make that pattern more complex if need be.



hshayh0rn wrote:

I have a macro that I use to activate a couple of different workbooks. My
problem is that one of the workbooks I need to use has a dynamic name. The
name of the workbook is created by a seperate program thats I do not control
and the name is comprised of "XXX_Date of creation_Time of
Creation_Delete.xls". Is there a way to open the book using some type of
wildcard? The XXX and Delete part of the file name are constant.

--

Dave Peterson


--

Dave Peterson