GetSaveAsFilename default folder

I am using GetSaveAsFilename in VBA for Excel. Is there any way to give this a default folder 开发者_Python百科to open up to? For example, I always want it to start at C:\MyDocuments\Music when it is called.

---

The FileDialog object offers way more flexibility than GetSaveAsFilename (and its sibling GetOpenFilename). Example:

Dim tuneSaver As FileDialog
Set tuneSaver = Application.FileDialog(msoFileDialogSaveAs)

With tuneSaver
    .Title = "Save this tune as..."
    .InitialFileName = "C:\MyDocuments\Music\"
    ' Set other properties here...
    .Show
End With

Note that an .InitialFileName longer than 256 characters will cause a run-time error.

See VBA help on FileDialog. It has quite a few useful properties, including e.g. AllowMultiSelect (though admittedly this one is irrelevant when saving).

---

This works:

x = Application.GetSaveAsFilename(InitialFileName:="C:\mydocuments\music\", _
    fileFilter:="Text Files (*.*), *.*")

However, if you have spaces in the filespec it gets a little trickier. For example, this:

x = Application.GetSaveAsFilename(InitialFileName:="%USERPROFILE%\My Documents\My Music", _
    fileFilter:="Text Files (*.*), *.*")

only gets as far as My Documents and thinks that My Music is the filename. Hope this helps.

---

Use ChDir before GetSaveAsFilename.

---

ChDir can be used (using the answer of @Mark Ransom) shown in this example code:

Dim workBook As workBook
Set workBook = ActiveWorkbook

ChDir (workBook.Path) ' Set Default folder

saveName = Application.GetSaveAsFilename(InitialFileName:=workBook.Name, _
                                         fileFilter:="Microsoft Excel Worksheet (*.xlsx), *.xlsx")

' Check whether the name specified is usable
If saveName <> False Then
    workBook.SaveAs Filename:=saveName, FileFormat:=xlOpenXMLWorkbook
End If

Hope this clarifies ChDir a bit.