Save As Part 2

[Guest]

I received this macro from one of the members of the community yesterday. It
saves the file with a filename from cell A1. It also saves it on a specific
directory.

Sub Save_As()
Dim s As String
s = "C:\Documents and Settings\user\My Documents\" & Range("A1").Value &
".xls"
ActiveWorkbook.SaveAs Filename:=s
End Sub

I would like to have an updated macro whereby the destination directory
would be the user's choice, maybe show the "Save As" window and let them
choose and navigate from that window to the folder of their choice. The
filename should still come from cell A1 of the file.

Is this possible? Please help. Thanks.

 

[Guest]

Give this a whirl...

Sub Save_As()
Dim objShell As Object
Dim objFolder As Object
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(&H5&)
Application.Dialogs(xlDialogSaveAs).Show _
objFolder.Self.Path & "\" & Range("A1").Value & ".xls"
End Sub

 

[Guest]

Jim,

Worked great. Thank you very much.

Give this a whirl...

Sub Save_As()
Dim objShell As Object
Dim objFolder As Object
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(&H5&)
Application.Dialogs(xlDialogSaveAs).Show _
objFolder.Self.Path & "\" & Range("A1").Value & ".xls"
End Sub