Hi, stuck on this..
I have this VB code which save a single worksheet from a workbook as a new file
Sub savesheet()
Application.ScreenUpdating = False
Dim savsheet
Dim savfile
savfile = ActiveWorkbook.Name
savsheet = ActiveSheet.Name
Sheets(savsheet).Select
Sheets(savsheet).Copy
ActiveWorkbook.SaveAs "c:windowsdesktopETV Bunker Log ~ monthly archive.xls"
Application.Workbooks(savfile).Activate
Sheets(savsheet).Select
Application.ScreenUpdating = True
Application.Workbooks("ETV Bunker Log ~ monthly archive").Close
End Sub
This module works, however, if the file already exists then xl displays a dialog box with 3 options (yes, no, cancel). “Yes” overwrites the existing file with the new save. “No” or “Cancel” throw up a VB error, and everything stops.
What I want to do is either get the module to check for an existing file, then delete it before making the new save. OR make it just overwrite the existing file without displaying the dialog box
Thanks in advance, Mike
p.s. I have found VB code on the WWW which the author says will check whether the file exists, but it is written as a Function, and I can’t get it to work (it keeps trying to run a macro).
I have this VB code which save a single worksheet from a workbook as a new file
Sub savesheet()
Application.ScreenUpdating = False
Dim savsheet
Dim savfile
savfile = ActiveWorkbook.Name
savsheet = ActiveSheet.Name
Sheets(savsheet).Select
Sheets(savsheet).Copy
ActiveWorkbook.SaveAs "c:windowsdesktopETV Bunker Log ~ monthly archive.xls"
Application.Workbooks(savfile).Activate
Sheets(savsheet).Select
Application.ScreenUpdating = True
Application.Workbooks("ETV Bunker Log ~ monthly archive").Close
End Sub
This module works, however, if the file already exists then xl displays a dialog box with 3 options (yes, no, cancel). “Yes” overwrites the existing file with the new save. “No” or “Cancel” throw up a VB error, and everything stops.
What I want to do is either get the module to check for an existing file, then delete it before making the new save. OR make it just overwrite the existing file without displaying the dialog box
Thanks in advance, Mike
p.s. I have found VB code on the WWW which the author says will check whether the file exists, but it is written as a Function, and I can’t get it to work (it keeps trying to run a macro).