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]

Thank you very much!

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]

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]

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]

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]

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

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.

 

[D.Parker]

Hello again, here is my current code. I made NewName a Variant since the
filename entered by the user can have alpha characters as well as numerics.
I thought I manipulated the IF..Then to reflect your changes also, could that
be the problem also? Thank you.

Sub RenameFilenameUponClose()

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

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

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

End Sub

 

[Dave Peterson]

I think the problem is the True portion:

Option Explicit
Sub RenameFilenameUponClose()

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

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

If SaveName = False Then
'do nothing
Else
ThisWorkbook.SaveAs Filename:=SaveName, _
FileFormat:=xlWorkbookNormal
End If

End Sub

SaveName will be the filename if the user clicks Save. It'll be False if they
hit cancel.

Hello again, here is my current code. I made NewName a Variant since the
filename entered by the user can have alpha characters as well as numerics.
I thought I manipulated the IF..Then to reflect your changes also, could that
be the problem also? Thank you.

Sub RenameFilenameUponClose()

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

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

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

End Sub

 

[D.Parker]

Dave, thank you for your response, but with the changes, there is still no
file save when I execute the code. The Save As window pops up, I can select
a directory, the Cancel works okay, but when I use the same filename or a
different filename in the window, nothing is getting saved. Any other ideas
are greatly appreciated?

Thank you.

 

[Dave Peterson]

It worked fine for me. I got a workbook named "P2 Loghistory shift.xls".

Did you make any other changes that may have broken the macro?

ps. This code uses ThisWorkbook. It's gonna save the workbook that owns the
code--not the activeworkbook. (But it does save something for me.)

Dave, thank you for your response, but with the changes, there is still no
file save when I execute the code. The Save As window pops up, I can select
a directory, the Cancel works okay, but when I use the same filename or a
different filename in the window, nothing is getting saved. Any other ideas
are greatly appreciated?

Thank you.

 

[D.Parker]

Ahhh, maybe therein lies the problem. I was putting the code in a module
instead of the ThisWorkbook object of the VBA Project. Could that be it?

I had a button on my spreadsheet that launches the "RenameFilenameUponClose"
macro. Other than that, that's all I have. No other changes other than what
you specify.

I apologize for the many strings of replies, but your assistance is superb!
Thank you so much!!!

Kind regards,

D.Parker

 

[Dave Peterson]

Nope. You were doing it ok--that code should go into a general module.

It sounds like you used a button from the forms toolbar. Are you sure that
button's macro is the correct version of your macro (just in case you had
multiple versions in different modules????)

Ahhh, maybe therein lies the problem. I was putting the code in a module
instead of the ThisWorkbook object of the VBA Project. Could that be it?

I had a button on my spreadsheet that launches the "RenameFilenameUponClose"
macro. Other than that, that's all I have. No other changes other than what
you specify.

I apologize for the many strings of replies, but your assistance is superb!
Thank you so much!!!

Kind regards,

D.Parker