Activate Workbook
 

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.

Activate Workbook
 

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

Activate Workbook
 

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

Activate Workbook
 

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

Activate Workbook
 

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