SOLVED

saving a file with a cell value error

Contributor

Hi all,

i ve got this VBA which save my file using a cell reference as a name, the code seems to work ok, the only issue is, if, and when i decide to re-save excel will ask me if i want to overwrite the existing file. if i answer no the macro will stop and give me a run-time error `1004` can someone please help me to fix it?

thank you in advance

 

Option Explicit
Sub SvMe()
'Saves filename as value of A1

Dim newFile As String, fName As String
fName = Range("D5").Value
newFile = fName & " "
ChDir _
"C:\Users\Paolo.De Luca\Documents\lETS PLAY WITH MACRO"
ActiveWorkbook.SaveAs Filename:=newFile

End Sub

 

7 Replies
best response confirmed by olopa67 (Contributor)
Solution
On Error Resume Next # This line helps macro to resume when there is an error. Use it after Sub line:
Sub SvMe()
On Error Resume Next
'Saves filename as value of A1
thank you problem solved thanks to your advice, can i take advantage of your expertise and ask you how can i have a DO YOU WANT TO SAVE YES\NO? kind of option ?
In your case it would be:
On Error Resume Next
Dim Result As Integer
Result = MsgBox("Do you want to Save?", vbQuestion + vbYesNo)
If Result = vbYes Then
#### Your code here
End If


See if this works for you
Awesome it works perfectly thank you very much
i have saved the files as a template macro enabled ed i just edit the script with the new file directory for saving as follow
Option Explicit
Sub SvMe()
'Saves filename as value of A1 plus the current date
On Error Resume Next
Dim Result As Integer
Result = MsgBox("Do you want to Save?", vbQuestion + vbYesNo)
If Result = vbYes Then

Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
fName = Range("D2").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
newFile = fName
' Change directory to suit your PC, including USER NAME
ChDir _
"O:\1Paolo\360 CATERING\RECEPE AND COSTING\RECEPES"
ActiveWorkbook.SaveAs Filename:=newFile
End If
End Sub

now it save the file in the microsoft template directory instead of the one in the script and also could you please tell me what i should change to save as a macro enabled file,
thanks again for your time
After your line:
ActiveWorkbook.SaveAs Filename:=newFile
press ',' (comma) and you'll see options.
ActiveWorkbook.SaveAs Filename:=newFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Alternately, you could manually save the file in your desired format. Then running the macro in your manually saved file will use the parent format for saving. The above is for forcing xlsm saving