One common question that I see in the newsgroups is “how can I force Application.GetSaveAsFileName or Application.GetOpenFileName to open in a specific directory ?”. The answer to this is use the ChDrive and ChDir functions.
If you want to open the dialog in say “E:My files”, then this code should do it
Dim Filename As Variant
ChDrive “E”
ChDir “E:My files”
Filename = Application.GetSaveAsFilename()
If TypeName(Filename) <> “Boolean” Then
MsgBox Filename
End If
End Sub
However, this doesn’t work if your path is in a network folder, like
\SomeServerSomefolder
to do this, you need to use the SetCurrentDirectory API function, which is defined as:
Now you can change the current path no matter if the path is mapped to a drive or not. You can also extend this example, saving the current path temporarily, to avoid messing with the user’s settings.
Dim CurrentPath As String
Dim Filename As Variant
‘Store the current path
CurrentPath = CurDir
‘Change the path to the one we want
SetCurrentDirectory “\SomeServerSome Path”
‘Ask for the file name
Filename = Application.GetSaveAsFilename()
If TypeName(Filename) <> “Boolean” Then
MsgBox Filename
End If
‘Change the path back
SetCurrentDirectory CurrentPath
End Sub
Hi
Or use Rob Bovey’s example (almost the same)
See how I use it here
http://www.rondebruin.nl/copy3.htm#select
Sub : Example 6
How’s about:
On Error Resume Next
ChDrive ThisWorkbook.Path
ChDir ThisWorkbook.Path
On Error GoTo 0
Works every time.
Youssef,
Try it with the workbook saved in a network path… not in a mapped drive.
If you are on Office XP or 2003 why not use?
Application.FileDialog(msoFileDialogFilePicker)
then you pick multiple files to open, choose the path, etc.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaof11/html/ofproDialogType_HV03083175.asp
Thanks for the hints – it’s been a regular source of annoyance that the dialogs opened up in the “wrong” folder every time.
A (somewhat) related question for me would be can you convert a mapped drive to the UNC name (ie E: = \ServerSomePath )? Is this another API function?
Tony
TJM
The API call for mapping a drive is WNetAddConnection2
Example on VBnet: Connecting to Network Resources
Alternatively, you could use the Shell command to execute NET USE.
headtoadie –
The GetOpenFileName method gives you more control over the process. All it tells the program is the name or names of the selected files. Your program is then allowed to do with these files what you want it to, usually open them, but not always.
Thanks Rob, that wasn’t quite what I wanted but the function WNetGetUniversalName from the same group of pages does better what I need to do. The shares are typically already mapped to a drive letter, it’s just I can never assume that it’s the same letter on each PC so I was hoping for something to let me check it.
I would like to be able something corresponding to
ChDrive ThisWorkbook.Path
ChDir ThisWorkbook.Path
(to get the relative path)
when klicking on a template (.xlt-file). (I am forced to use the xlt extension by my boss.) These templates all extract the same information from an .xls file residing in the same folder as the templates. Problem is, I need to use relative paths since these folders will reside in completely different locations in every computer and network where they’ll be used. When starting from say template.xlt, you get a new workbook – “template1? and this new project doesn’t seem to have a “home” yet.
So my problem again (or maybe my dilemma): I want a template1 file (that is started by double clicking a template.xlt file) to be able to find the folder of the template.xlt from which it is started, because in this folder we have a data file with info that the template1 needs to get in order to work properly.
I tried the VBA code to change the current directory to a network path, my company’s corporate shared folders. I was only able to get it working once I had made the function private by declaring it as private like so:
Private Declare Function SetCurrentDirectory Lib “kernel32? Alias “SetCurrentDirectoryA” _
(ByVal lpPathName As String) As Long
Cheers
Mahtab
Thanks!!!!