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.
精彩评论