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

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

wrote:

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

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

wrote in message
ps.com...
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

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.


Dave Peterson wrote:

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

wrote:

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

--

Dave Peterson