Solved: Allow users to set default save directory.

Daniel do you have your network mapped? Does it show in windows explorer as my Z:\Temp drive shows in the screen shot below?

Hmm... I'd forgotten about this.

It looks as if you'll have to find a workaround, Daniel. There is a couple suggested in the thread I linked to.

HTH,

Wow,

Ken I am going to cry. lol I have tried every suggestion in there and nothing works. lol Thank you for the help

Yep, that's kind of the point. It won't.

I suppose that you could either a) roll your own dialog, or b) live with it.

Cheers,

Ken,

When you say roll my own dialog? do you mean create my own userform? because I have asked if it is possible to create a userform where a user can navigate to a folder and I was told I can't.

Where did you ask.....from an example I yoinked from Ken

Quote:

---

Originally Posted by Djblois

I have asked if it is possible to create a userform where a user can navigate to a folder and I was told I can't.

---

Really? Interesting...

It may not be as seemeless as the built in dialogs, but sure you could do it. Set up two text boxes on a userform. The first one would hold your directory, and would be populated with a routine like the one below (Excel 2002+ search KB for BrowseForFolder for 97-2000 compliant version):
[vba]Private Function GetFolderName(Optional OpenAt As String) As String
'Macro Purpose: To retrieve a file name from a specific location

Dim lngcount

'Ensure that file path ends in a slash
If Len(OpenAt) > 0 Then If Right(OpenAt, 1) <> "\" Then OpenAt = OpenAt & "\"

'Prompt the user for a filename
With Application.FileDialog(4) '4=msoFileDialogFolderPicker
.InitialFileName = OpenAt
.Show
For lngcount = 1 To .SelectedItems.Count
GetFolderName = .SelectedItems(lngcount)
Next lngcount
End With
End Function[/vba]
The second text box, you'd just type in the file name that you want to save under.

Add an okay button, validate that the path exists, and save the file. If you really feel clever, save the chosen path in the registry so that you can feed it back in as a default the next time someone wants to save a file using via your userform.

EDIT: Steve, that was yoinked from me? It doesn't look familiar... inspired, maybe, but yoinked?

but Ken can people go into subdirectories in a userform also?

double click the directories in the form...

Quote:

---

Originally Posted by Djblois

but Ken can people go into subdirectories in a userform also?

---

Yes...

Steve has given you the steps for his. My suggestion is less elegant, but essentially launches the folder picker dialog so that you can pick the folder you want. It will drill up or down.

Yes Ken and Steve but that is a function? how do I put a function in a listbox? sorry for my ignorance?

Quote:

---

Originally Posted by Djblois

Yes Ken and Steve but that is a function? how do I put a function in a listbox?

---

You don't. It won't work that way. You'd attach it to a commandbutton, and it feed the value back into a text box.

Truthfully, Steve's may be a better fit for you than mine.

You can call Kens function starting in a specific directory and return it to a textbox like this:
[VBA]Private Sub CommandButton1_Click()
TextBox1.Value = GetFolderName("Z:\Temp")
End Sub[/VBA]

This actually works in an easier way:

[VBA]Sub ComSaveCode()
Dim wbSaveName

ChDrive "H:"
ChDir "H:\dblois"
wbSaveName = Application.GetSaveAsFilename

wbSaveName.Save FileName:=wbSaveName

End Sub[/VBA]

only problem I want it to automatically save as an excel file. Is this possible?

I thought it was closer than it is.

1) first how do I tell it to save in the folder that the user chose
2) when the GetSaveAsFileName dialog shows up the name is always enclosed in "" how do I get rid of that?
3) Also how do I tell it to save as that name?

1. example:
[VBA]Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
newFile = Format$(Date, "m-d-yy")
' Change directory to suit
ChDir _
"C:\Temp"
ActiveWorkbook.SaveAs Filename:=newFile
[/VBA]
2. don't understand the question
3.....? what name?

been a while since I visited this thread so I'm not sure where you are with things....clarify please.

Please don't yell but for anyone who was also having trouble with this Here is the code. and from the other posting I figured this has been trouble. This is working for me:

[VBA]ChDrive "H:"
ChDir "H:\dblois"

wbfilename = Application.GetSaveAsFilename

On Error Resume Next
If wbfilename = False Then
MsgBox "The File wasn't saved."
Exit Sub
End If
ActiveWorkbook.SaveAs wbfilename[/VBA]

The whole thing works but the last part is driving me crazy. I want it to show the msgbox if the user hits cancel or if the user leaves the name field blank. Right now it shows the msgbox all the time.

[VBA]Sub a()
Dim wbfilename As String
ChDrive "F:"
ChDir "F:\Temp\Daniel"

wbfilename = Application.GetSaveAsFilename
If wbfilename = "False" Then MsgBox "Test"
ThisWorkbook.SaveCopyAs Filename:=wbfilename & ".xls"
End Sub[/VBA]

I'm not sure but do you look for something like this :

http://vbaexpress.com/forum/showpost...3&postcount=18