setting current directory

[jerm.hannley]

Hello all,

I can set the current directory using the following

myCurrentFolder = ActiveWorkbook.Path
ChDir myCurrentFolder

this works great for my 'file choser' dialog box on my local drive c:
the problem occurs for a mapped network drive... using the same line of
code and running the macro from a mapped network drive it sets my
dialog window to my documents.

does anyone know why this is the case? or any direction to solve this?

regards,
jerm

 

[Dave Peterson]

Saved from a previous post:

If you refer to the folder\file by its UNC path
(\\something\somethingelse\filename.xls), you can use an API call. In fact,
this works with mapped drives, too:

Here's a sample, but with getsaveasfilename.

Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub
Sub testme01()

Dim myFileName As Variant
Dim myCurFolder As String
Dim myNewFolder As String
Dim Wkbk as workbook

myCurFolder = CurDir
myNewFolder = "\\share\folder1\folder2"

On Error Resume Next
ChDirNet myNewFolder
If Err.Number <> 0 Then
'what should happen
MsgBox "Please change to your own folder"
Err.Clear
End If
On Error GoTo 0

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

ChDirNet myCurFolder

If myFileName = False Then
Exit Sub 'user hit cancel
End If

'do your stuff to open it and process it.
Set wkbk = workbooks.open(filename:=myfilename)

'....

End Sub

 

[NickHK]

Each drive has a CurDir, so if you are moving to a drive use a ChDrive also.

myCurrentFolder = ActiveWorkbook.Path
ChDrive myCurrentFolder
ChDir myCurrentFolder

Not sure how/if this works with mapped.

NickHK

 

[Dave Peterson]

This will work for a drive\folder that isn't mapped--or even one that is.

But if your drive is mapped, then it's kind of overkill.