No file when Save As is executed

[D.Parker]

I am trying to save a worksheet as a separate workbook. I am manipulating
code from a pervious response. The Save As form comes up and I can set a
file name and choose a path, but when I click the save button there is no
file in my designated folder. The Save As is good since the user will be
changing the filename each time upon exiting. I'm assuming I missing some
code somewhere? Secondly, is there a way to save the worksheet object as
opposed to the entire workbook (i.e. save the worksheet into a new workbook,
less the VBA code preferrably)?

Sub RenameFilenameUponClose()

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

NewName = "P2 LogHistory Shift"
fFilter = "Excel Files (*.xls), *.xls"
SaveName = Application.GetSaveAsFilename _
(NewName, fileFilter:=fFilter)

End Sub

Your help is greatly appreciated as always.

[Dave Peterson]

This line:

SaveName = Application.GetSaveAsFilename _
(NewName, fileFilter:=fFilter)

only returns the name of the file the user chose--it doesn't do the actual save.

Sub RenameFilenameUponClose()

Dim SaveName As variant '<--changed
Dim fFilter As String
Dim NewName As String

NewName = "P2 LogHistory Shift"
fFilter = "Excel Files (*.xls), *.xls"
SaveName = Application.GetSaveAsFilename _
(NewName, fileFilter:=fFilter)

if savename = false then
'use cancelled--what to do?
else
thisworkbook.saveas filename:=savename, fileformat:=xlworkbooknormal
end if

End Sub

I change SaveName from a String to Variant--so that it could represent the
boolean value False, too.

D.Parker wrote:
>
> I am trying to save a worksheet as a separate workbook. I am manipulating
> code from a pervious response. The Save As form comes up and I can set a
> file name and choose a path, but when I click the save button there is no
> file in my designated folder. The Save As is good since the user will be
> changing the filename each time upon exiting. I'm assuming I missing some
> code somewhere? Secondly, is there a way to save the worksheet object as
> opposed to the entire workbook (i.e. save the worksheet into a new workbook,
> less the VBA code preferrably)?
>
> Sub RenameFilenameUponClose()
>
> Dim SaveName As String
> Dim fFilter As String
> Dim NewName As String
>
> NewName = "P2 LogHistory Shift"
> fFilter = "Excel Files (*.xls), *.xls"
> SaveName = Application.GetSaveAsFilename _
> (NewName, fileFilter:=fFilter)
>
> End Sub
>
> Your help is greatly appreciated as always.

--

Dave Peterson

[D.Parker]

Thank you very much!

"Dave Peterson" wrote:

> This line:
>
> SaveName = Application.GetSaveAsFilename _
> (NewName, fileFilter:=fFilter)
>
> only returns the name of the file the user chose--it doesn't do the actual save.
>
> Sub RenameFilenameUponClose()
>
> Dim SaveName As variant '<--changed
> Dim fFilter As String
> Dim NewName As String
>
> NewName = "P2 LogHistory Shift"
> fFilter = "Excel Files (*.xls), *.xls"
> SaveName = Application.GetSaveAsFilename _
> (NewName, fileFilter:=fFilter)
>
> if savename = false then
> 'use cancelled--what to do?
> else
> thisworkbook.saveas filename:=savename, fileformat:=xlworkbooknormal
> end if
>
> End Sub
>
> I change SaveName from a String to Variant--so that it could represent the
> boolean value False, too.
>
> D.Parker wrote:
> >
> > I am trying to save a worksheet as a separate workbook. I am manipulating
> > code from a pervious response. The Save As form comes up and I can set a
> > file name and choose a path, but when I click the save button there is no
> > file in my designated folder. The Save As is good since the user will be
> > changing the filename each time upon exiting. I'm assuming I missing some
> > code somewhere? Secondly, is there a way to save the worksheet object as
> > opposed to the entire workbook (i.e. save the worksheet into a new workbook,
> > less the VBA code preferrably)?
> >
> > Sub RenameFilenameUponClose()
> >
> > Dim SaveName As String
> > Dim fFilter As String
> > Dim NewName As String
> >
> > NewName = "P2 LogHistory Shift"
> > fFilter = "Excel Files (*.xls), *.xls"
> > SaveName = Application.GetSaveAsFilename _
> > (NewName, fileFilter:=fFilter)
> >
> > End Sub
> >
> > Your help is greatly appreciated as always.
>
> --
>
> Dave Peterson
>

[D.Parker]

Dave:

Is there a way to save a particular worksheet into a new workbook?
Otherwise, I will just have to password protect the code in the current
workbook. Thanks again!

"Dave Peterson" wrote:

> This line:
>
> SaveName = Application.GetSaveAsFilename _
> (NewName, fileFilter:=fFilter)
>
> only returns the name of the file the user chose--it doesn't do the actual save.
>
> Sub RenameFilenameUponClose()
>
> Dim SaveName As variant '<--changed
> Dim fFilter As String
> Dim NewName As String
>
> NewName = "P2 LogHistory Shift"
> fFilter = "Excel Files (*.xls), *.xls"
> SaveName = Application.GetSaveAsFilename _
> (NewName, fileFilter:=fFilter)
>
> if savename = false then
> 'use cancelled--what to do?
> else
> thisworkbook.saveas filename:=savename, fileformat:=xlworkbooknormal
> end if
>
> End Sub
>
> I change SaveName from a String to Variant--so that it could represent the
> boolean value False, too.
>
> D.Parker wrote:
> >
> > I am trying to save a worksheet as a separate workbook. I am manipulating
> > code from a pervious response. The Save As form comes up and I can set a
> > file name and choose a path, but when I click the save button there is no
> > file in my designated folder. The Save As is good since the user will be
> > changing the filename each time upon exiting. I'm assuming I missing some
> > code somewhere? Secondly, is there a way to save the worksheet object as
> > opposed to the entire workbook (i.e. save the worksheet into a new workbook,
> > less the VBA code preferrably)?
> >
> > Sub RenameFilenameUponClose()
> >
> > Dim SaveName As String
> > Dim fFilter As String
> > Dim NewName As String
> >
> > NewName = "P2 LogHistory Shift"
> > fFilter = "Excel Files (*.xls), *.xls"
> > SaveName = Application.GetSaveAsFilename _
> > (NewName, fileFilter:=fFilter)
> >
> > End Sub
> >
> > Your help is greatly appreciated as always.
>
> --
>
> Dave Peterson
>

[Dave Peterson]

You can copy a worksheet to a new workbook and save that workbook. Is that what
you meant?

Something like this may get you going:

Option Explicit
Sub testme()
Dim wks As Worksheet
Set wks = ActiveWorkbook.Worksheets("sheet1")

wks.Copy 'to a new workbook
With ActiveSheet.Parent
.SaveAs Filename:="hithere"
.Close savechanges:=False
End With

End Sub

If you're going to overwrite an existing file, put:

application.displayalerts = false
..saveas filename:=....
application.displayalerts = true

to suppress any "are you sure" prompt.


D.Parker wrote:
>
> Dave:
>
> Is there a way to save a particular worksheet into a new workbook?
> Otherwise, I will just have to password protect the code in the current
> workbook. Thanks again!
>
> "Dave Peterson" wrote:
>
> > This line:
> >
> > SaveName = Application.GetSaveAsFilename _
> > (NewName, fileFilter:=fFilter)
> >
> > only returns the name of the file the user chose--it doesn't do the actual save.
> >
> > Sub RenameFilenameUponClose()
> >
> > Dim SaveName As variant '<--changed
> > Dim fFilter As String
> > Dim NewName As String
> >
> > NewName = "P2 LogHistory Shift"
> > fFilter = "Excel Files (*.xls), *.xls"
> > SaveName = Application.GetSaveAsFilename _
> > (NewName, fileFilter:=fFilter)
> >
> > if savename = false then
> > 'use cancelled--what to do?
> > else
> > thisworkbook.saveas filename:=savename, fileformat:=xlworkbooknormal
> > end if
> >
> > End Sub
> >
> > I change SaveName from a String to Variant--so that it could represent the
> > boolean value False, too.
> >
> > D.Parker wrote:
> > >
> > > I am trying to save a worksheet as a separate workbook. I am manipulating
> > > code from a pervious response. The Save As form comes up and I can set a
> > > file name and choose a path, but when I click the save button there is no
> > > file in my designated folder. The Save As is good since the user will be
> > > changing the filename each time upon exiting. I'm assuming I missing some
> > > code somewhere? Secondly, is there a way to save the worksheet object as
> > > opposed to the entire workbook (i.e. save the worksheet into a new workbook,
> > > less the VBA code preferrably)?
> > >
> > > Sub RenameFilenameUponClose()
> > >
> > > Dim SaveName As String
> > > Dim fFilter As String
> > > Dim NewName As String
> > >
> > > NewName = "P2 LogHistory Shift"
> > > fFilter = "Excel Files (*.xls), *.xls"
> > > SaveName = Application.GetSaveAsFilename _
> > > (NewName, fileFilter:=fFilter)
> > >
> > > End Sub
> > >
> > > Your help is greatly appreciated as always.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[D.Parker]

With the changes from your first reply, when I do a save as and go to the
folder to check, the workbook still does not exist? I changed SaveName to
Variant as well as NewName to Variant, would that cause the problem? Thanks
again for you assistance.

"Dave Peterson" wrote:

> You can copy a worksheet to a new workbook and save that workbook. Is that what
> you meant?
>
> Something like this may get you going:
>
> Option Explicit
> Sub testme()
> Dim wks As Worksheet
> Set wks = ActiveWorkbook.Worksheets("sheet1")
>
> wks.Copy 'to a new workbook
> With ActiveSheet.Parent
> .SaveAs Filename:="hithere"
> .Close savechanges:=False
> End With
>
> End Sub
>
> If you're going to overwrite an existing file, put:
>
> application.displayalerts = false
> ..saveas filename:=....
> application.displayalerts = true
>
> to suppress any "are you sure" prompt.
>
>
> D.Parker wrote:
> >
> > Dave:
> >
> > Is there a way to save a particular worksheet into a new workbook?
> > Otherwise, I will just have to password protect the code in the current
> > workbook. Thanks again!
> >
> > "Dave Peterson" wrote:
> >
> > > This line:
> > >
> > > SaveName = Application.GetSaveAsFilename _
> > > (NewName, fileFilter:=fFilter)
> > >
> > > only returns the name of the file the user chose--it doesn't do the actual save.
> > >
> > > Sub RenameFilenameUponClose()
> > >
> > > Dim SaveName As variant '<--changed
> > > Dim fFilter As String
> > > Dim NewName As String
> > >
> > > NewName = "P2 LogHistory Shift"
> > > fFilter = "Excel Files (*.xls), *.xls"
> > > SaveName = Application.GetSaveAsFilename _
> > > (NewName, fileFilter:=fFilter)
> > >
> > > if savename = false then
> > > 'use cancelled--what to do?
> > > else
> > > thisworkbook.saveas filename:=savename, fileformat:=xlworkbooknormal
> > > end if
> > >
> > > End Sub
> > >
> > > I change SaveName from a String to Variant--so that it could represent the
> > > boolean value False, too.
> > >
> > > D.Parker wrote:
> > > >
> > > > I am trying to save a worksheet as a separate workbook. I am manipulating
> > > > code from a pervious response. The Save As form comes up and I can set a
> > > > file name and choose a path, but when I click the save button there is no
> > > > file in my designated folder. The Save As is good since the user will be
> > > > changing the filename each time upon exiting. I'm assuming I missing some
> > > > code somewhere? Secondly, is there a way to save the worksheet object as
> > > > opposed to the entire workbook (i.e. save the worksheet into a new workbook,
> > > > less the VBA code preferrably)?
> > > >
> > > > Sub RenameFilenameUponClose()
> > > >
> > > > Dim SaveName As String
> > > > Dim fFilter As String
> > > > Dim NewName As String
> > > >
> > > > NewName = "P2 LogHistory Shift"
> > > > fFilter = "Excel Files (*.xls), *.xls"
> > > > SaveName = Application.GetSaveAsFilename _
> > > > (NewName, fileFilter:=fFilter)
> > > >
> > > > End Sub
> > > >
> > > > Your help is greatly appreciated as always.
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

I think if you post your current code, it would be easier to guess.

D.Parker wrote:
>
> With the changes from your first reply, when I do a save as and go to the
> folder to check, the workbook still does not exist? I changed SaveName to
> Variant as well as NewName to Variant, would that cause the problem? Thanks
> again for you assistance.
>
> "Dave Peterson" wrote:
>
> > You can copy a worksheet to a new workbook and save that workbook. Is that what
> > you meant?
> >
> > Something like this may get you going:
> >
> > Option Explicit
> > Sub testme()
> > Dim wks As Worksheet
> > Set wks = ActiveWorkbook.Worksheets("sheet1")
> >
> > wks.Copy 'to a new workbook
> > With ActiveSheet.Parent
> > .SaveAs Filename:="hithere"
> > .Close savechanges:=False
> > End With
> >
> > End Sub
> >
> > If you're going to overwrite an existing file, put:
> >
> > application.displayalerts = false
> > ..saveas filename:=....
> > application.displayalerts = true
> >
> > to suppress any "are you sure" prompt.
> >
> >
> > D.Parker wrote:
> > >
> > > Dave:
> > >
> > > Is there a way to save a particular worksheet into a new workbook?
> > > Otherwise, I will just have to password protect the code in the current
> > > workbook. Thanks again!
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > This line:
> > > >
> > > > SaveName = Application.GetSaveAsFilename _
> > > > (NewName, fileFilter:=fFilter)
> > > >
> > > > only returns the name of the file the user chose--it doesn't do the actual save.
> > > >
> > > > Sub RenameFilenameUponClose()
> > > >
> > > > Dim SaveName As variant '<--changed
> > > > Dim fFilter As String
> > > > Dim NewName As String
> > > >
> > > > NewName = "P2 LogHistory Shift"
> > > > fFilter = "Excel Files (*.xls), *.xls"
> > > > SaveName = Application.GetSaveAsFilename _
> > > > (NewName, fileFilter:=fFilter)
> > > >
> > > > if savename = false then
> > > > 'use cancelled--what to do?
> > > > else
> > > > thisworkbook.saveas filename:=savename, fileformat:=xlworkbooknormal
> > > > end if
> > > >
> > > > End Sub
> > > >
> > > > I change SaveName from a String to Variant--so that it could represent the
> > > > boolean value False, too.
> > > >
> > > > D.Parker wrote:
> > > > >
> > > > > I am trying to save a worksheet as a separate workbook. I am manipulating
> > > > > code from a pervious response. The Save As form comes up and I can set a
> > > > > file name and choose a path, but when I click the save button there is no
> > > > > file in my designated folder. The Save As is good since the user will be
> > > > > changing the filename each time upon exiting. I'm assuming I missing some
> > > > > code somewhere? Secondly, is there a way to save the worksheet object as
> > > > > opposed to the entire workbook (i.e. save the worksheet into a new workbook,
> > > > > less the VBA code preferrably)?
> > > > >
> > > > > Sub RenameFilenameUponClose()
> > > > >
> > > > > Dim SaveName As String
> > > > > Dim fFilter As String
> > > > > Dim NewName As String
> > > > >
> > > > > NewName = "P2 LogHistory Shift"
> > > > > fFilter = "Excel Files (*.xls), *.xls"
> > > > > SaveName = Application.GetSaveAsFilename _
> > > > > (NewName, fileFilter:=fFilter)
> > > > >
> > > > > End Sub
> > > > >
> > > > > Your help is greatly appreciated as always.
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[uriel78]

I've got a question on this argument...How can I do to obtain a routine
similar to the one you posted, which allows me to enter the name of the file
in the same way I do when I choose "save as" from Fyle menu.. instead of
typing it in the macro
I mean I need sthg to subsitute the line

NewName = "P2 LogHistory Shift"

with a call for the windows displaying "save as"...

Thanks in advance

"Dave Peterson" <ec35720@netscapeXSPAM.com> ha scritto nel messaggio
news:4249EAE3.C387F423@netscapeXSPAM.com...
> This line:
>
> SaveName = Application.GetSaveAsFilename _
> (NewName, fileFilter:=fFilter)
>
> only returns the name of the file the user chose--it doesn't do the actual
save.
>
> Sub RenameFilenameUponClose()
>
> Dim SaveName As variant '<--changed
> Dim fFilter As String
> Dim NewName As String
>
> NewName = "P2 LogHistory Shift"
> fFilter = "Excel Files (*.xls), *.xls"
> SaveName = Application.GetSaveAsFilename _
> (NewName, fileFilter:=fFilter)
>
> if savename = false then
> 'use cancelled--what to do?
> else
> thisworkbook.saveas filename:=savename,
fileformat:=xlworkbooknormal
> end if
>
> End Sub

[Dave Peterson]

The application.getsaveasfilename has a parameter that can be used to specify
the initial name (if you want to "suggest" a name to the user).

If you don't want to suggest a name at all, you can use:

fFilter = "Excel Files (*.xls), *.xls"
SaveName = Application.GetSaveAsFilename(InitialFileName:="", _
fileFilter:=fFilter)

And if you want to let excel suggest what it wants to suggest:

fFilter = "Excel Files (*.xls), *.xls"
SaveName = Application.GetSaveAsFilename(fileFilter:=fFilter)



uriel78 wrote:
>
> I've got a question on this argument...How can I do to obtain a routine
> similar to the one you posted, which allows me to enter the name of the file
> in the same way I do when I choose "save as" from Fyle menu.. instead of
> typing it in the macro
> I mean I need sthg to subsitute the line
>
> NewName = "P2 LogHistory Shift"
>
> with a call for the windows displaying "save as"...
>
> Thanks in advance
>
> "Dave Peterson" <ec35720@netscapeXSPAM.com> ha scritto nel messaggio
> news:4249EAE3.C387F423@netscapeXSPAM.com...
> > This line:
> >
> > SaveName = Application.GetSaveAsFilename _
> > (NewName, fileFilter:=fFilter)
> >
> > only returns the name of the file the user chose--it doesn't do the actual
> save.
> >
> > Sub RenameFilenameUponClose()
> >
> > Dim SaveName As variant '<--changed
> > Dim fFilter As String
> > Dim NewName As String
> >
> > NewName = "P2 LogHistory Shift"
> > fFilter = "Excel Files (*.xls), *.xls"
> > SaveName = Application.GetSaveAsFilename _
> > (NewName, fileFilter:=fFilter)
> >
> > if savename = false then
> > 'use cancelled--what to do?
> > else
> > thisworkbook.saveas filename:=savename,
> fileformat:=xlworkbooknormal
> > end if
> >
> > End Sub

--

Dave Peterson

[uriel78]

thank you for your help!!


"Dave Peterson" <ec35720@netscapeXSPAM.com> ha scritto nel messaggio
news:424AA4F8.B1FDC939@netscapeXSPAM.com...
> The application.getsaveasfilename has a parameter that can be used to
specify
> the initial name (if you want to "suggest" a name to the user).
>
> If you don't want to suggest a name at all, you can use:
>
> fFilter = "Excel Files (*.xls), *.xls"
> SaveName = Application.GetSaveAsFilename(InitialFileName:="", _
> fileFilter:=fFilter)
>
> And if you want to let excel suggest what it wants to suggest:
>
> fFilter = "Excel Files (*.xls), *.xls"
> SaveName = Application.GetSaveAsFilename(fileFilter:=fFilter)