Solved: Allow users to set default save directory.

[Djblois]

I want to create a text box where my users can save a default save directory. and when the save as dialog comes up within my program it opens up in that dialog. is this possible?

I already know how to save it to a registry setting but how do I open the save as dialog into that folder?

[VBA]If Not Application.Dialogs(xlDialogSaveAs).Show(, 1) Then
MsgBox "The Report did not Save."
End If[/VBA]

[Bob Phillips]

Check out Cdrive and ChDir

[Djblois]

ok I will look at them

[Ken Puls]

Wouldn't that be ChDrive, Bob?

Daniel, I have an example of a UDF to Get File Name From Specific Directory. It will help in understanding the use of ChDrive and ChDir.

HTH,

[Djblois]

Ken, Bob:

I am using this code:

[vba]ChDrive "H:"
ChDir "H:\dblois\My Documents\Current Projects"

If Not Application.Dialogs(xlDialogSaveAs).Show(, 1) Then
MsgBox "The Report did not Save."
End If[/vba]

However, the save as dialog isn't starting in that directory for some reason?

[Djblois]

I even tested this code:

[VBA]Dim DefaultDrive As String
Dim DefaultDir As String

DefaultDrive = "H"
DefaultDir = "H:\dblois\My Documents\Current Projects"

ChDrive (DefaultDrive)
ChDir (DefaultDir)

If Not Application.Dialogs(xlDialogSaveAs).Show(, 1) Then
MsgBox "The Report did not Save."
End If[/VBA]

[lucas]

this opens the save as dialog for me in F:\Temp
[VBA]Sub a()
ChDir "f:\Temp"
If Not Application.Dialogs(xlDialogSaveAs).Show(, 1) Then
End If
End Sub[/VBA]

[Djblois]

Does it matter if the drive is a network drive?

[lucas]

I'm guessing that you would have to map the network drive in windows explorer first but I'm not a network type.....

[Djblois]

Thanks for trying lucas. Does anyone else know why it won't work with my network drive?

[lucas]

Daniel....
Ken's code from the link above works for networks if you change the path in the function..
ps I didn't change it from file open to saveas as I was trying to get it to find the network drive for you:
[vba]Option Explicit
Private Declare Function SetCurrentDirectoryA _
Lib "kernel32" (ByVal lpPathName As String) As Long
Public Function GetOpenFilenameFrom(Optional sDirDefault As String) As Variant
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: To ask for a file at a specified directory
Dim sDirCurrent As String
Dim lError As Long
'Make note of the current directory
sDirCurrent = "Z:\Temp"
If sDirDefault = vbNullString Then
'If optional arguement not supplied then
'assign current directory as default
sDirDefault = "Z:\Temp"
Else
'If option arguement is supplied, test path to ensure
'that it exists. If not, assign current directory
If Len(Dir(sDirDefault, vbDirectory)) = 0 Then
sDirDefault = sDirCurrent
End If
End If
'Change the drive and directory
'*Drive change is unecessary if same, but takes as long to test
' as just changing it
If Not Left(sDirDefault, 2) = "\\" Then
'Not a network drive, so use ChDir
ChDrive Left(sDirDefault, 1)
ChDir (sDirDefault)
Else
'Network drive, so use API
lError = SetCurrentDirectoryA(sDirDefault)
If lError = 0 Then _
MsgBox "Sorry, I encountered an error accessing the network file path"
ChDir (sDirDefault)
End If
'Get the file's name & path, setting the filters to only display
'desired types. Help on the exact syntax can be found by looking
'up the GetOpenFilename method in the VBA help files
GetOpenFilenameFrom = Application.GetOpenFilename _
("Excel Files (*.xl*), *.xl*,All Files (*.*),*.*")
'Change the drive and directory back
If Not Left(sDirCurrent, 2) = "\\" Then
'Not a network drive, so use ChDrive
ChDrive Left(sDirCurrent, 1)
ChDir (sDirCurrent)
Else
'Network drive, so use API
lError = SetCurrentDirectoryA(sDirCurrent)
If lError = 0 Then _
MsgBox "Sorry, I encountered an error resetting the network file path"
ChDir (sDirCurrent)
End If
End Function
Sub GetMeAFile()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: To test the GetOpenFilenameFrom function
Dim sWBToOpen As Variant
sWBToOpen = GetOpenFilenameFrom(Range("A3").Value)

If Not sWBToOpen = False Then Workbooks.Open (sWBToOpen)

End Sub[/vba]

[Djblois]

ok, i looked at that code. I just didn't understand it, I guess I will have to look at it harder. thank you.

[Djblois]

Ken, Lucas:

this is what I got the code up to and logically it looks like it should work but it doesn't. I guess I don't know enough about VBA yet.

[VBA]Option Explicit
Private Declare Function SetCurrentDirectoryA _
Lib "kernel32" (ByVal lpPathName As String) As Long
Public Function GetSaveFileDir(Optional sDirDefault As String) As Variant
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: To ask for a file at a specified directory
Dim sDirCurrent As String
Dim lError As Long
'Make note of the current directory
sDirCurrent = "H:\\Dblois"
If sDirDefault = vbNullString Then
'If optional arguement not supplied then
'assign current directory as default
sDirDefault = "H:\\Dblois"
Else
'If option arguement is supplied, test path to ensure
'that it exists. If not, assign current directory
If Len(Dir(sDirDefault, vbDirectory)) = 0 Then
sDirDefault = sDirCurrent
End If
End If
'Change the drive and directory
'*Drive change is unecessary if same, but takes as long to test
' as just changing it
If Not Left(sDirDefault, 2) = "\\" Then
'Not a network drive, so use ChDir
ChDrive Left(sDirDefault, 1)
ChDir (sDirDefault)
Else
'Network drive, so use API
lError = SetCurrentDirectoryA(sDirDefault)
If lError = 0 Then _
MsgBox "Sorry, I encountered an error accessing the network file path"
ChDir (sDirDefault)
End If

End Function
Sub testcode()

Dim sDirToSaveTo As Variant
sDirToSaveTo = GetSaveFileDir
If Not sDirToSaveTo = False Then ChDir (sDirToSaveTo)
If Not Application.Dialogs(xlDialogSaveAs).Show(, 1) Then
MsgBox "The Report did not Save."
End If

End Sub[/VBA]

[lucas]

I'm not sure either Daniel because it does work for me....only thing I can see would be a path problem....are you sure of it?

ahh, I just noticed something...in the function you have 2 backslashes in your path...
[vba]sDirDefault = "H:\\Dblois"[/vba]
try removing one of them..

[Djblois]

I originally tried with only one \ but looking at Ken's code, I thought maybe Network drives needed 2 \\:

[VBA]If Not Left(sDirDefault, 2) = "\\" Then
'Not a network drive, so use ChDir
[/VBA]

[Djblois]

Now I am testing this:

[vba]Sub testcode()

Dim sDirToSaveTo As Variant
Dim sDirCurrent As String
Dim lError As Long
'Make note of the current directory
sDirCurrent = "H:\Dblois"
If sDirDefault = vbNullString Then
'If optional arguement not supplied then
'assign current directory as default
sDirDefault = "H:\Dblois"
Else
'If option arguement is supplied, test path to ensure
'that it exists. If not, assign current directory
If Len(Dir(sDirDefault, vbDirectory)) = 0 Then
sDirDefault = sDirCurrent
End If
End If
'Change the drive and directory
'*Drive change is unecessary if same, but takes as long to test
' as just changing it
If Not Mid(sDirDefault, 3, 2) = "\\" Then
'Not a network drive, so use ChDir
ChDrive Left(sDirDefault, 1)
ChDir (sDirDefault)
Else
'Network drive, so use API
'lError = SetCurrentDirectoryA(sDirDefault)
If lError = 0 Then _
MsgBox "Sorry, I encountered an error accessing the network file path"
ChDir (sDirDefault)
End If
sDirToSaveTo = sDirDefault
If Not sDirToSaveTo = False Then ChDir (sDirDefault)
If Not Application.Dialogs(xlDialogSaveAs).Show(, 1) Then
MsgBox "The Report did not Save."
End If[/vba]

It still won't work? Ken since it is your code maybe you know what I am doing wrong?

[Ken Puls]

Hi there,

So big question... how does the drive display in windows explorer? Is it H:\dblois, or is it \\servername\dblois?

[Djblois]

Here you go Ken, copied right out of Explorer:

H:\dblois

[Ken Puls]

I just copied your most recent code, and tried testing with both of the following directories which are valid on my system:
J:\vba tests
C:\tmp

Both launched just fine.

What errors are you getting, if any? Does it stop on a line when you try to step through? Are you watching the values being set in the locals window to ensure that they are correct?

[Djblois]

I have watched them in the watch window and I am not getting any errors. It is just running through and opening into the save as dialog but not in my directory.