No file when Save As is executed

G

Guest

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.
 
D

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.
 
G

Guest

Thank you very much!

Dave Peterson said:
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.
Click to expand...
 
G

Guest

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!
 
D

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.
 
U

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
 
D

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)
 
G

Guest

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

Dave Peterson

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

D.Parker said:
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.
Click to expand...
 
G

Guest

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

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.

D.Parker said:
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
Click to expand...
 
G

Guest

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

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.)



D.Parker said:
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.
Click to expand...
 
G

Guest

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
 
D

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????)

D.Parker said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cancel Macro is user selects 'cancel' at save menu 1
No Success with GetSaveAs 5
'Save as' dialog opens despite Application.DisplayAlerts = False 6
runtime error 91 2
SaveAs using VB 2
SAVE AS MACRO 6
SAVE AS MACRO 5
Code to save spreadsheet 4

Top