Forum Discussion
Solved
saving a file with a cell value error
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...
- 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
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
- 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- 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
- After your line:
ActiveWorkbook.SaveAs Filename:=newFile
press ',' (comma) and you'll see options.
ActiveWorkbook.SaveAs Filename:=newFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled
- Awesome it works perfectly thank you very much