Allowing the user to specify save location

[Guest]

Easy question I'm sure.....

How do I write code to save a file as the name in cell C2 but also allow the
user to define the location (path) of the file?

What I would like is the same as the window that pops up when you select
File>> Save As in Excel. If I could get the file name to default (from cell
C2) and then have the user take it from there to save to the location they
want.

How can I get there?

I currently have the code below:

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=Range("C2").Value
Application.DisplayAlerts = True

 

[News]

Tim,
You mean like this ?

Private Sub CommandButton3_Click()
Dim RetVal As Variant
RetVal = Application.GetSaveAsFilename(Range("C2"))
If RetVal <> False Then
ThisWorkbook.SaveAs RetVal
End If
End Sub

NickHK

 

[Die_Another_Day]

Tim, try this:
Sub FolderPicker()
Dim SvPath As String
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = False Then Exit Sub
SvPath = .SelectedItems(1)
End With
ActiveWorkbook.SaveAs SvPath & Range("C1")
End Sub

 

[Guest]

Thanks to both of you. These worked great! One question just out of
curiosity. When I do the save, it saves the file as a file type "File"
rather than an excel worksheet (.xls). What is file type "file"?

 

[Die_Another_Day]

Try:
ActiveWorkbook.SaveAs Filename:= _
SvPath & Range("C1"), _
FileFormat:=xlNormal

Let me know if that fixes things.

Charles

 

[News]

Which format was was the original file in and how do you .SaveAs ?

NickHK