PDA

View Full Version : Solved: Error on "Save As"



ChrisJ
03-29-2007, 05:44 AM
Hello,

I have the following bit of code for saving a file with the name of a specific cell. This bit works fine, I have a problem when you select No or Cancel from the Saveas dialog box I get 1004 runtime error, telling me Method 'SaveAs' of object'_Workbook' failed


Sub GenSave()

Application.ScreenUpdating = False
ActiveSheet.Unprotect
Application.Goto "PICName"
ActiveWorkbook.SaveAs Filename:=ActiveCell.Value, FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
Application.Range("Mth").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End Sub


Can someone suggest how I can prevent this but retaining the ability of saving with name of the file being a cell contents (in this case "PICName")

Thankyou in advance

Chris
:dunno

Edited 30-Mar-07 by geekgirlau. Reason: insert vba tags

JKwan
03-29-2007, 06:06 AM
Easiest would be adding
On Error Resume Next

onto your procedure.
Just put the line right after SUB

ChrisJ
03-29-2007, 06:09 AM
Thankyou... that worked great..

Bob Phillips
03-29-2007, 06:28 AM
Sub GenSave()
Dim sFile As String

Application.ScreenUpdating = False
ActiveSheet.Unprotect
Application.Goto "PICName"
sFile = Application.GetSaveAsFilename(initialfilename:=ActiveCell.Value)
If sFile <> "False" Then
ActiveWorkbook.SaveAs Filename:=sFile, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End If
Application.Range("Mth").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True

End Sub

david000
03-29-2007, 08:15 PM
initialfilename --- you don't see that everyday! damn!

geekgirlau
03-29-2007, 09:23 PM
Welcome to the board ChrisJ (not to mention david000!) :hi:

Chris, I've taken the liberty of renaming your thread. Even though you have a solution now, it may help other members in the future find a solution to a similar problem. Generally speaking it's a good idea to use a subject for your thread that gives some indication of the topic, rather than a generic "help" title.

I've also enclosed your text in VBA tags. Whenever you post code, select the code text and click on the "VBA" button - this formats your code as you see above, making it easier to read.

Hope to hear a lot more from you both in future!