CurDir, ChDrive, ChDir error questions

[goaljohnbill]

In excel 2003 windows xp I have been using the following code for a
long time successfully:

sub ForceDirectoryOpenSave

Dim strStartingDirectory As String
strStartingDirectory = CurDir
ChDrive "T"
ChDir "T:\xx\xy\xu\xa"

' code for whatever application.get___ i need

ChDrive strStartingDirectory
ChDir strStartingDirectory

end sub

Recently I have started having errors on the line;

ChDrive strStartingDirectory

It seems to happen randomly ie not every time the users have that
code, or even every time the machine is restarted ect and if you rerun
the sub (or skip JUST that line in subs without full error handling)
it works. After reading through groups I assume that the system admins
(i work at a large public university) have started controlling some
things with UNC paths and when excel last used a UNC path it errors.
All of our data/workspace is on the T drive so I dont have to be able
to "point" to UNC paths for the dialogs. I just am tired of subs being
dropped out of randomly and having to figure out how to get the user
back to where they need to be to continue working without negative
effects.
What are the "consequences" (if any) of not changeing the Drive back
to match the starting directory but changing the directory back? Or
using OnError Resume Next to "shut off" my regular error handling for
these instances to stop this from dropping users out of subs (then
after that line using OnError Goto ErrorHandler to "turn on" normal
error handling again) so that when excel is pointing to a mapped drive
it still changes it back?

thanks
john

 

[Jim Rech]

It may be that the start drive is not mapped to a letter, a UNC path as they
say. It's better I think to use this Windows API call, which works with
mapped and unmapped paths, and changes drive and path in one call.

Add at the top of your modules:

Declare Function SetCurrentDirectoryA Lib "KERNEL32" (ByVal lpPathName As
String) As Long

then in your code:

strStartingDirectory = CurDir
SetCurrentDirectoryA "T:\xx\xy\xu\xa"

' code for whatever application.get___ i need

SetCurrentDirectoryA strStartingDirectory

 

[goaljohnbill]

Add at the top of your modules:

Declare Function SetCurrentDirectoryA Lib "KERNEL32" (ByVal lpPathName As
String) As Long

then in your code:

  strStartingDirectory = CurDir
  SetCurrentDirectoryA "T:\xx\xy\xu\xa"

  '  code for whatever application.get___ i need

  SetCurrentDirectoryA strStartingDirectory

....

So it will work properly with just the declare function line? So does
that mean the additional lines in responses to other chdrive problems
is checking/error handling for "selecting" a UNC drive?
A solution where i add a line to each module and change 3 lines with 2
at each usage is better than what I was worried I would need to do
after searching.

Thanks