Set Defaults Directory for Text Import

  • Thread starter Thread starter Jim P
  • Start date Start date
J

Jim P

How do I set the default drive and directory when using the
Refresh All option for importing text. Each time I move my
spreadsheet to a new subdirectory to process a different
set of data residing in that subdirectory, I am asked to
locate the files all over again and have to drill down
through the directory tree. I am aware of the ChDir "path"
and ChDrive "drive" statements but they don't work for me
in Excel 2000. The Tools/Options/General/Default File
Location doesn't seem have any effect either. I'd
appreciate help with this problem.
 
The only time I've seen chdir/chdrive fail is on a network (UNC) drive--not a
mapped drive.

Here's a post I saved from Laurent Longre:
==============


Try to use the API function SetCurrentDirectory rather than ChDrive and
ChDir:

Private Declare Function SetCurrentDirectoryA Lib "Kernel32" _
(ByVal lpszCurDir As String) As Long

Sub Test()
Dim FName As String, CDir As String
CDir = CurDir$
If SetCurrentDirectoryA("\\ntserver\rootdir\directory") = 0 Then
MsgBox "Unable to open this directory.", vbCritical
Exit Sub
End If
FName = Application.GetOpenFilename
SetCurrentDirectoryA CDir
MsgBox "Selected file : " & FName
End Sub
 
Back
Top