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" wrote:

> 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

[D.Parker]

Wow. Thank you so much. It works!

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

D.Parker

"Rowan" wrote:

> 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" wrote:
>
> > 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

[Dave Peterson]

Just curious--what was the difference between Rowan's suggestion and this:
http://groups.google.co.uk/groups?th...0microsoft.com
(one line in your browser)



D.Parker wrote:
>
> Wow. Thank you so much. It works!
>
> Thank you for loaning your expertise. You guys/gals are awesome.
>
> D.Parker
>
> "Rowan" wrote:
>
> > 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" wrote:
> >
> > > 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

--

Dave Peterson

[Rowan]

Looks about the same to me

"Dave Peterson" wrote:

> Just curious--what was the difference between Rowan's suggestion and this:
> http://groups.google.co.uk/groups?th...0microsoft.com
> (one line in your browser)
>
>
>
> D.Parker wrote:
> >
> > Wow. Thank you so much. It works!
> >
> > Thank you for loaning your expertise. You guys/gals are awesome.
> >
> > D.Parker
> >
> > "Rowan" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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
>
> --
>
> Dave Peterson
>

[Dave Peterson]

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

Rowan wrote:
>
> Looks about the same to me
>
> "Dave Peterson" wrote:
>
> > Just curious--what was the difference between Rowan's suggestion and this:
> > http://groups.google.co.uk/groups?th...0microsoft.com
> > (one line in your browser)
> >
> >
> >
> > D.Parker wrote:
> > >
> > > Wow. Thank you so much. It works!
> > >
> > > Thank you for loaning your expertise. You guys/gals are awesome.
> > >
> > > D.Parker
> > >
> > > "Rowan" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson