Using Wildcards for Folders

[Orion Cochrane]

Excel 2003

The following is stored in my PERSONAL.xls file.

I have a module with a series of macros. One of them is a Private Sub that
checks the path of the current workbook and, if the path of the current
workbook doesn't check out with what I have written, the user will get a
warning and the macro will not run. All other macros are public so I can call
them up with the Macros button, and their first line is to Call Directory
(Directory being the name of the Private Sub with the ActiveWorkbook.Path
line). The last folder of the path is a year. As opposed to manually changing
the name of the last folder every year, can I use a wildcard (*) to make it
accept any folder like 20* for the years? The only reason I am asking is that
my boss has this macro in her PERSONAL.xls (which I set up for her) and the
project is locked. Here's an example:

Change:
O:\Folder1\2008
to
O:\Folder1\20*

The reason for the Directory sub is that the macros are tailored to certain
files with the same setup, so it wouldn't work otherwise. Thanks in advance.

 

[Roger Govier]

Hi

You could use something like the following
Dim spath As String, lpos As Long

spath = Thisworkbook.Path
lpos = InStrRev(spath, "\")
If Mid(spath, lpos + 1, 2) <> "20" Then
MsgBox "Wrong Folder"
End If

 

[Orion Cochrane]

Here is the code for Directory:
Private Sub Directory()
'Checks filepath
If ActiveWorkbook.Path = "C:\Folder1\[Folder2] - 2008" Or _
ActiveWorkbook.Path = "C:\Folder1\[Folder3] - 2008" Then
Application.Run (Macro)
Else
MsgBox [msg]
End
End If
End Sub

Where

[Folder1] is the first folder in the drive
[Folder2] is a sub-folder within [Folder1] that has 4 characters and then
the " - 2008"
[Folder3] is a sub-folder within [Folder1] that has 10 characters and then
the " - 2008"

[Folder2] and [Folder3] do not contain spaces before the " - 2008" suffix.

I tried what you said and it didn't work. I wasn't too clear as to the
format of the current folder name. Can you modify your code so I can use it?
Thanks.

 

[Orion Cochrane]

That worked. What I had to do was break up my folders so the last folder
would be the year. It all works. Thanks a lot!
--
Please rate posts so we know when we have answered your questions. Thanks.

Hi

Private Sub Directory()
'Checks filepath
Dim spath As String, lpos As Long

spath = ActiveWorkbook.Path
lpos = InStrRev(spath, "\")
If Mid(spath, lpos + 1, 2) = "20" Then
Application.Run (Macro)
Else
MsgBox [msg]
End
End If
End Sub
--
Regards
Roger Govier

Here is the code for Directory:
Private Sub Directory()
'Checks filepath
If ActiveWorkbook.Path = "C:\Folder1\[Folder2] - 2008" Or _
ActiveWorkbook.Path = "C:\Folder1\[Folder3] - 2008" Then
Application.Run (Macro)
Else
MsgBox [msg]
End
End If
End Sub

Where

[Folder1] is the first folder in the drive
[Folder2] is a sub-folder within [Folder1] that has 4 characters and then
the " - 2008"
[Folder3] is a sub-folder within [Folder1] that has 10 characters and then
the " - 2008"

[Folder2] and [Folder3] do not contain spaces before the " - 2008" suffix.

I tried what you said and it didn't work. I wasn't too clear as to the
format of the current folder name. Can you modify your code so I can use
it?
Thanks.
--
Please rate posts so we know when we have answered your questions. Thanks.

Hi

You could use something like the following
Dim spath As String, lpos As Long

spath = Thisworkbook.Path
lpos = InStrRev(spath, "\")
If Mid(spath, lpos + 1, 2) <> "20" Then
MsgBox "Wrong Folder"
End If


--
Regards
Roger Govier

message
Excel 2003

The following is stored in my PERSONAL.xls file.

I have a module with a series of macros. One of them is a Private Sub
that
checks the path of the current workbook and, if the path of the current
workbook doesn't check out with what I have written, the user will get
a
warning and the macro will not run. All other macros are public so I
can
call
them up with the Macros button, and their first line is to Call
Directory
(Directory being the name of the Private Sub with the
ActiveWorkbook.Path
line). The last folder of the path is a year. As opposed to manually
changing
the name of the last folder every year, can I use a wildcard (*) to
make
it
accept any folder like 20* for the years? The only reason I am asking
is
that
my boss has this macro in her PERSONAL.xls (which I set up for her) and
the
project is locked. Here's an example:

Change:
O:\Folder1\2008
to
O:\Folder1\20*

The reason for the Directory sub is that the macros are tailored to
certain
files with the same setup, so it wouldn't work otherwise. Thanks in
advance.

 

[Orion Cochrane]

I realized something about when I copied your original code after I changed
the folder structure. I think I need a LEFT function to verify the first
folder ([Folder1]) and second (there are 2 secondary folders; the last one is
the year folder) because there are folders elsewhere I work with where the
last folder will be a year. How would I implement the left function?

eg "C:\[Folder1]\[Folder2]" or "C:\[Folder1]\[Folder3]"
Where:
[Folder1] has 12 characters (with 1 space in that 12)
[Folder2] comes after [Folder1] and has 4 characters
[Folder3] comes after [Folder1] and has 10 characters

I would still use the mid function you gave me for the year, but I think I
would need something to verify the folders leading up to the year folder.
Thanks Roger.
--
Please rate posts so we know when we have answered your questions. Thanks.

Hi

I'm sorry, I hadn't noticed that you had changed the folder nomenclature
between your first and second posting.
If you wanted to keep
"C:\Folder1\[Folder2] - 2008"

then just change the code
lpos = InStrRev(spath, "\")
If Mid(spath, lpos + 1, 2) = "20" Then

to
lpos = InStrRev(spath, "-")
If Mid(spath, lpos + 2, 2) <> "20" Then


--
Regards
Roger Govier

That worked. What I had to do was break up my folders so the last folder
would be the year. It all works. Thanks a lot!
--
Please rate posts so we know when we have answered your questions. Thanks.

Hi

Private Sub Directory()
'Checks filepath
Dim spath As String, lpos As Long

spath = ActiveWorkbook.Path
lpos = InStrRev(spath, "\")
If Mid(spath, lpos + 1, 2) = "20" Then
Application.Run (Macro)
Else
MsgBox [msg]
End
End If
End Sub
--
Regards
Roger Govier

message
Here is the code for Directory:
Private Sub Directory()
'Checks filepath
If ActiveWorkbook.Path = "C:\Folder1\[Folder2] - 2008" Or _
ActiveWorkbook.Path = "C:\Folder1\[Folder3] - 2008" Then
Application.Run (Macro)
Else
MsgBox [msg]
End
End If
End Sub

Where

[Folder1] is the first folder in the drive
[Folder2] is a sub-folder within [Folder1] that has 4 characters and
then
the " - 2008"
[Folder3] is a sub-folder within [Folder1] that has 10 characters and
then
the " - 2008"

[Folder2] and [Folder3] do not contain spaces before the " - 2008"
suffix.

I tried what you said and it didn't work. I wasn't too clear as to the
format of the current folder name. Can you modify your code so I can
use
it?
Thanks.
--
Please rate posts so we know when we have answered your questions.
Thanks.


:

Hi

You could use something like the following
Dim spath As String, lpos As Long

spath = Thisworkbook.Path
lpos = InStrRev(spath, "\")
If Mid(spath, lpos + 1, 2) <> "20" Then
MsgBox "Wrong Folder"
End If


--
Regards
Roger Govier

message
Excel 2003

The following is stored in my PERSONAL.xls file.

I have a module with a series of macros. One of them is a Private
Sub
that
checks the path of the current workbook and, if the path of the
current
workbook doesn't check out with what I have written, the user will
get
a
warning and the macro will not run. All other macros are public so I
can
call
them up with the Macros button, and their first line is to Call
Directory
(Directory being the name of the Private Sub with the
ActiveWorkbook.Path
line). The last folder of the path is a year. As opposed to manually
changing
the name of the last folder every year, can I use a wildcard (*) to
make
it
accept any folder like 20* for the years? The only reason I am
asking
is
that
my boss has this macro in her PERSONAL.xls (which I set up for her)
and
the
project is locked. Here's an example:

Change:
O:\Folder1\2008
to
O:\Folder1\20*

The reason for the Directory sub is that the macros are tailored to
certain
files with the same setup, so it wouldn't work otherwise. Thanks in
advance.

 

[Orion Cochrane]

I tried the left function I just discussed and it worked. Thanks again, Roger.
--
Please rate posts so we know when we have answered your questions. Thanks.

I realized something about when I copied your original code after I changed
the folder structure. I think I need a LEFT function to verify the first
folder ([Folder1]) and second (there are 2 secondary folders; the last one is
the year folder) because there are folders elsewhere I work with where the
last folder will be a year. How would I implement the left function?

eg "C:\[Folder1]\[Folder2]" or "C:\[Folder1]\[Folder3]"
Where:
[Folder1] has 12 characters (with 1 space in that 12)
[Folder2] comes after [Folder1] and has 4 characters
[Folder3] comes after [Folder1] and has 10 characters

I would still use the mid function you gave me for the year, but I think I
would need something to verify the folders leading up to the year folder.
Thanks Roger.
--
Please rate posts so we know when we have answered your questions. Thanks.

Hi

I'm sorry, I hadn't noticed that you had changed the folder nomenclature
between your first and second posting.
If you wanted to keep
"C:\Folder1\[Folder2] - 2008"

then just change the code
lpos = InStrRev(spath, "\")
If Mid(spath, lpos + 1, 2) = "20" Then

to
lpos = InStrRev(spath, "-")
If Mid(spath, lpos + 2, 2) <> "20" Then


--
Regards
Roger Govier

That worked. What I had to do was break up my folders so the last folder
would be the year. It all works. Thanks a lot!
--
Please rate posts so we know when we have answered your questions. Thanks.


:

Hi

Private Sub Directory()
'Checks filepath
Dim spath As String, lpos As Long

spath = ActiveWorkbook.Path
lpos = InStrRev(spath, "\")
If Mid(spath, lpos + 1, 2) = "20" Then
Application.Run (Macro)
Else
MsgBox [msg]
End
End If
End Sub
--
Regards
Roger Govier

message
Here is the code for Directory:
Private Sub Directory()
'Checks filepath
If ActiveWorkbook.Path = "C:\Folder1\[Folder2] - 2008" Or _
ActiveWorkbook.Path = "C:\Folder1\[Folder3] - 2008" Then
Application.Run (Macro)
Else
MsgBox [msg]
End
End If
End Sub

Where

[Folder1] is the first folder in the drive
[Folder2] is a sub-folder within [Folder1] that has 4 characters and
then
the " - 2008"
[Folder3] is a sub-folder within [Folder1] that has 10 characters and
then
the " - 2008"

[Folder2] and [Folder3] do not contain spaces before the " - 2008"
suffix.

I tried what you said and it didn't work. I wasn't too clear as to the
format of the current folder name. Can you modify your code so I can
use
it?
Thanks.
--
Please rate posts so we know when we have answered your questions.
Thanks.


:

Hi

You could use something like the following
Dim spath As String, lpos As Long

spath = Thisworkbook.Path
lpos = InStrRev(spath, "\")
If Mid(spath, lpos + 1, 2) <> "20" Then
MsgBox "Wrong Folder"
End If


--
Regards
Roger Govier

message
Excel 2003

The following is stored in my PERSONAL.xls file.

I have a module with a series of macros. One of them is a Private
Sub
that
checks the path of the current workbook and, if the path of the
current
workbook doesn't check out with what I have written, the user will
get
a
warning and the macro will not run. All other macros are public so I
can
call
them up with the Macros button, and their first line is to Call
Directory
(Directory being the name of the Private Sub with the
ActiveWorkbook.Path
line). The last folder of the path is a year. As opposed to manually
changing
the name of the last folder every year, can I use a wildcard (*) to
make
it
accept any folder like 20* for the years? The only reason I am
asking
is
that
my boss has this macro in her PERSONAL.xls (which I set up for her)
and
the
project is locked. Here's an example:

Change:
O:\Folder1\2008
to
O:\Folder1\20*

The reason for the Directory sub is that the macros are tailored to
certain
files with the same setup, so it wouldn't work otherwise. Thanks in
advance.