No Success with GetSaveAs

[D.Parker]

How do I get my new workbook filename to save to a given directory? The save
as dialog box pops up with the NewName but unfortunately the file is "not"
saved to the given directory when I click Save.

I have a button on my spreadsheet when initiates the RenameFilenameUponClose
subroutine. Dave kindly gave me advice, upon which I have tried them all but
I still am not getting a new file save to my selected directory.

Is there something obvious I am doing wrong or anything for that matter?
The code is attached.

Sub RenameFilenameUponClose()

Dim SaveName As Variant
Dim fFilter As String
Dim NewName As String

NewName = "P2 LogHistory Shift"
fFilter = "Excel Files (*.xls), *.xls"
SaveName = Application.GetSaveAsFilename _
(NewName, FileFilter:=fFilter, Title:="Save As New P2 Workbook")

If SaveName = True Then
ThisWorkbook.SaveAs Filename:=SaveName, _
FileFormat:=xlWorkbookNormal
Else
Exit Sub
End If

End Sub

Kind regards,

D.Parker

 

[Rowan]

Your macro will never hit the ThisWorkbook.SaveAs statement as SaveName does
not ever evaluate to True. It is either the path and name selected in the
dialog or if the user hits cancel then it is False. Try

Sub RenameFilenameUponClose()

Dim SaveName As Variant
Dim fFilter As String
Dim NewName As String

NewName = "P2 LogHistory Shift"
fFilter = "Excel Files (*.xls), *.xls"
SaveName = Application.GetSaveAsFilename _
(NewName, FileFilter:=fFilter, Title:="Save As New P2 Workbook")

If SaveName = False Then
Exit Sub
Else
ThisWorkbook.SaveAs Filename:=SaveName, _
FileFormat:=xlWorkbookNormal
End If

End Sub

Hope this helps
Rowan

 

[D.Parker]

Wow. Thank you so much. It works!

Thank you for loaning your expertise. You guys/gals are awesome.

D.Parker

 

[Dave Peterson]

Just curious--what was the difference between Rowan's suggestion and this:
http://groups.google.co.uk/groups?threadm=3ADA6975-AC6E-4FF3-A43D-3EFFAE96AA4B@microsoft.com
(one line in your browser)

 

[Rowan]

Looks about the same to me

Just curious--what was the difference between Rowan's suggestion and this:
http://groups.google.co.uk/groups?threadm=3ADA6975-AC6E-4FF3-A43D-3EFFAE96AA4B@microsoft.com
(one line in your browser)

 

[Dave Peterson]

Then why didn't my suggestion work <vbg>?

Looks about the same to me