I have a procedure, shown below (in part), where I want to jump to another
part of the code when the Directory is invalid. ie I want to be able to run
the code on 3 different computers without having to amend the code each
time. Maybe there's a better way to do this, but I would have thought that
what I have done should work! ie in the part of the code that's headed
'work, if there is no such dir, then the code jumps to the line OFFICE:
without a hitch, BUT even though there is no valid dir in the OFFICE: part
of the code, I get a run-time error 76 "Path not Found" at the line "ChDir
dir", and it doesn't jump to the next part of the code (ie HOME:).

Why doesn't the On Error command work at that point?
If there is a better way to do this please advise.

Rob

Dim drive As String
Dim dir As String

'work
WORK:
On Error GoTo OFFICE
dir = "H:\SupporterStats Construction\"
drive = "H"
ChDir dir
GoTo Proceed

'Office
OFFICE:
On Error GoTo HOME
dir = "K:\2007\common\Supporter Stats\"
drive = "K"
ChDir dir
GoTo Proceed

'home
HOME:
dir = "C:\Documents and Settings\USER\My
Documents\Fola\UnderConstruction\"
drive = "C"
ChDir dir

Proceed:

With only two folders to check, I'd use:

Option Explicit
Sub testme01()

Dim TestStr As String
Dim myDir As String
Dim myHomeFolder As String
Dim myWorkFolder As String
Dim UseThisFolder As String

'needs the final \
myHomeFolder = "K:\2007\common\Supporter Stats\"
myWorkFolder = "H:\SupporterStats Construction\"

UseThisFolder = ""
TestStr = ""
On Error Resume Next
TestStr = dir(myWorkFolder & "nul")
On Error GoTo 0

If TestStr < "" Then
UseThisFolder = myWorkFolder
Else
'not found, check for home
On Error Resume Next
TestStr = dir(myHomeFolder & "nul")
On Error GoTo 0

If TestStr < "" Then
UseThisFolder = myHomeFolder
End If
End If

If UseThisFolder = "" Then
MsgBox "both locations not available"
Else
ChDrive UseThisFolder
ChDir UseThisFolder
End If

End Sub

=====
If you start having to add more locations, then I'd build an array of those
folder names and loop until I found a good location.



RobN wrote:

I have a procedure, shown below (in part), where I want to jump to another
part of the code when the Directory is invalid. ie I want to be able to run
the code on 3 different computers without having to amend the code each
time. Maybe there's a better way to do this, but I would have thought that
what I have done should work! ie in the part of the code that's headed
'work, if there is no such dir, then the code jumps to the line OFFICE:
without a hitch, BUT even though there is no valid dir in the OFFICE: part
of the code, I get a run-time error 76 "Path not Found" at the line "ChDir
dir", and it doesn't jump to the next part of the code (ie HOME:).

Why doesn't the On Error command work at that point?
If there is a better way to do this please advise.

Rob

Dim drive As String
Dim dir As String

'work
WORK:
On Error GoTo OFFICE
dir = "H:\SupporterStats Construction\"
drive = "H"
ChDir dir
GoTo Proceed

'Office
OFFICE:
On Error GoTo HOME
dir = "K:\2007\common\Supporter Stats\"
drive = "K"
ChDir dir
GoTo Proceed

'home
HOME:
dir = "C:\Documents and Settings\USER\My
Documents\Fola\UnderConstruction\"
drive = "C"
ChDir dir

Proceed:

--

Dave Peterson

Thanks Dave,

Very helpful!

Any idea why my code wouldn't work?

Rob

"Dave Peterson" wrote in message
...
With only two folders to check, I'd use:

Option Explicit
Sub testme01()

Dim TestStr As String
Dim myDir As String
Dim myHomeFolder As String
Dim myWorkFolder As String
Dim UseThisFolder As String

'needs the final \
myHomeFolder = "K:\2007\common\Supporter Stats\"
myWorkFolder = "H:\SupporterStats Construction\"

UseThisFolder = ""
TestStr = ""
On Error Resume Next
TestStr = dir(myWorkFolder & "nul")
On Error GoTo 0

If TestStr < "" Then
UseThisFolder = myWorkFolder
Else
'not found, check for home
On Error Resume Next
TestStr = dir(myHomeFolder & "nul")
On Error GoTo 0

If TestStr < "" Then
UseThisFolder = myHomeFolder
End If
End If

If UseThisFolder = "" Then
MsgBox "both locations not available"
Else
ChDrive UseThisFolder
ChDir UseThisFolder
End If

End Sub

=====
If you start having to add more locations, then I'd build an array of
those
folder names and loop until I found a good location.



RobN wrote:

I have a procedure, shown below (in part), where I want to jump to
another
part of the code when the Directory is invalid. ie I want to be able to
run
the code on 3 different computers without having to amend the code each
time. Maybe there's a better way to do this, but I would have thought
that
what I have done should work! ie in the part of the code that's headed
'work, if there is no such dir, then the code jumps to the line OFFICE:
without a hitch, BUT even though there is no valid dir in the OFFICE:
part
of the code, I get a run-time error 76 "Path not Found" at the line
"ChDir
dir", and it doesn't jump to the next part of the code (ie HOME:).

Why doesn't the On Error command work at that point?
If there is a better way to do this please advise.

Rob

Dim drive As String
Dim dir As String

'work
WORK:
On Error GoTo OFFICE
dir = "H:\SupporterStats Construction\"
drive = "H"
ChDir dir
GoTo Proceed

'Office
OFFICE:
On Error GoTo HOME
dir = "K:\2007\common\Supporter Stats\"
drive = "K"
ChDir dir
GoTo Proceed

'home
HOME:
dir = "C:\Documents and Settings\USER\My
Documents\Fola\UnderConstruction\"
drive = "C"
ChDir dir

Proceed:

--

Dave Peterson

You have to be more careful when you're in the middle of the error handling.

Check Chip Pearson's site:
http://www.cpearson.com/excel/ErrorHandling.htm

As a personal preference, if I know that a line I use may cause an error, I'll
use:

on error resume next
line that may cause an error
'check the error
if err.number < 0 then
'there was an error
err.clear
end if
on error goto 0

Or if I can, I'll use a object:

set testwks = nothing
on error resume next
set testwks = worksheets("name that may not exist")
on error goto 0

if testwks is nothing then
'not there
else
'it is there
end if

Or use a string (like in the other code).

But these are with specific lines that may cause errors--it isn't for generic
error handling. (See Chip's site for that.)

ps. Without the "on error goto ..." line, using Dir as a variable would have
been seen as an error, too.

It's usually (always!) best not to use VBA's keywords for your variable names.


RobN wrote:

Thanks Dave,

Very helpful!

Any idea why my code wouldn't work?

Rob

"Dave Peterson" wrote in message
...
With only two folders to check, I'd use:

Option Explicit
Sub testme01()

Dim TestStr As String
Dim myDir As String
Dim myHomeFolder As String
Dim myWorkFolder As String
Dim UseThisFolder As String

'needs the final \
myHomeFolder = "K:\2007\common\Supporter Stats\"
myWorkFolder = "H:\SupporterStats Construction\"

UseThisFolder = ""
TestStr = ""
On Error Resume Next
TestStr = dir(myWorkFolder & "nul")
On Error GoTo 0

If TestStr < "" Then
UseThisFolder = myWorkFolder
Else
'not found, check for home
On Error Resume Next
TestStr = dir(myHomeFolder & "nul")
On Error GoTo 0

If TestStr < "" Then
UseThisFolder = myHomeFolder
End If
End If

If UseThisFolder = "" Then
MsgBox "both locations not available"
Else
ChDrive UseThisFolder
ChDir UseThisFolder
End If

End Sub

=====
If you start having to add more locations, then I'd build an array of
those
folder names and loop until I found a good location.



RobN wrote:

I have a procedure, shown below (in part), where I want to jump to
another
part of the code when the Directory is invalid. ie I want to be able to
run
the code on 3 different computers without having to amend the code each
time. Maybe there's a better way to do this, but I would have thought
that
what I have done should work! ie in the part of the code that's headed
'work, if there is no such dir, then the code jumps to the line OFFICE:
without a hitch, BUT even though there is no valid dir in the OFFICE:
part
of the code, I get a run-time error 76 "Path not Found" at the line
"ChDir
dir", and it doesn't jump to the next part of the code (ie HOME:).

Why doesn't the On Error command work at that point?
If there is a better way to do this please advise.

Rob

Dim drive As String
Dim dir As String

'work
WORK:
On Error GoTo OFFICE
dir = "H:\SupporterStats Construction\"
drive = "H"
ChDir dir
GoTo Proceed

'Office
OFFICE:
On Error GoTo HOME
dir = "K:\2007\common\Supporter Stats\"
drive = "K"
ChDir dir
GoTo Proceed

'home
HOME:
dir = "C:\Documents and Settings\USER\My
Documents\Fola\UnderConstruction\"
drive = "C"
ChDir dir

Proceed:

--

Dave Peterson

--

Dave Peterson

A very helpful comprehensive answer.....Thanks!

Rob

"Dave Peterson" wrote in message
...
You have to be more careful when you're in the middle of the error
handling.

Check Chip Pearson's site:
http://www.cpearson.com/excel/ErrorHandling.htm

As a personal preference, if I know that a line I use may cause an error,
I'll
use:

on error resume next
line that may cause an error
'check the error
if err.number < 0 then
'there was an error
err.clear
end if
on error goto 0

Or if I can, I'll use a object:

set testwks = nothing
on error resume next
set testwks = worksheets("name that may not exist")
on error goto 0

if testwks is nothing then
'not there
else
'it is there
end if

Or use a string (like in the other code).

But these are with specific lines that may cause errors--it isn't for
generic
error handling. (See Chip's site for that.)

ps. Without the "on error goto ..." line, using Dir as a variable would
have
been seen as an error, too.

It's usually (always!) best not to use VBA's keywords for your variable
names.


RobN wrote:

Thanks Dave,

Very helpful!

Any idea why my code wouldn't work?

Rob

"Dave Peterson" wrote in message
...
With only two folders to check, I'd use:

Option Explicit
Sub testme01()

Dim TestStr As String
Dim myDir As String
Dim myHomeFolder As String
Dim myWorkFolder As String
Dim UseThisFolder As String

'needs the final \
myHomeFolder = "K:\2007\common\Supporter Stats\"
myWorkFolder = "H:\SupporterStats Construction\"

UseThisFolder = ""
TestStr = ""
On Error Resume Next
TestStr = dir(myWorkFolder & "nul")
On Error GoTo 0

If TestStr < "" Then
UseThisFolder = myWorkFolder
Else
'not found, check for home
On Error Resume Next
TestStr = dir(myHomeFolder & "nul")
On Error GoTo 0

If TestStr < "" Then
UseThisFolder = myHomeFolder
End If
End If

If UseThisFolder = "" Then
MsgBox "both locations not available"
Else
ChDrive UseThisFolder
ChDir UseThisFolder
End If

End Sub

=====
If you start having to add more locations, then I'd build an array of
those
folder names and loop until I found a good location.



RobN wrote:

I have a procedure, shown below (in part), where I want to jump to
another
part of the code when the Directory is invalid. ie I want to be able
to
run
the code on 3 different computers without having to amend the code
each
time. Maybe there's a better way to do this, but I would have thought
that
what I have done should work! ie in the part of the code that's headed
'work, if there is no such dir, then the code jumps to the line
OFFICE:
without a hitch, BUT even though there is no valid dir in the OFFICE:
part
of the code, I get a run-time error 76 "Path not Found" at the line
"ChDir
dir", and it doesn't jump to the next part of the code (ie HOME:).

Why doesn't the On Error command work at that point?
If there is a better way to do this please advise.

Rob

Dim drive As String
Dim dir As String

'work
WORK:
On Error GoTo OFFICE
dir = "H:\SupporterStats Construction\"
drive = "H"
ChDir dir
GoTo Proceed

'Office
OFFICE:
On Error GoTo HOME
dir = "K:\2007\common\Supporter Stats\"
drive = "K"
ChDir dir
GoTo Proceed

'home
HOME:
dir = "C:\Documents and Settings\USER\My
Documents\Fola\UnderConstruction\"
drive = "C"
ChDir dir

Proceed:

--

Dave Peterson

--

Dave Peterson