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