macro to delete existing file

[skadan]

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

 

[Richie(UK)]

Hi skadan,

This will perform the save function while ignoring the warning about the workbook name already existing.

<pre>Sub savesheet()
Dim savsheet as String, savfile as String

Application.ScreenUpdating = False

savfile = ActiveWorkbook.Name
savsheet = ActiveSheet.Name
Sheets(savsheet).Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs _
"c:windowsdesktopETV Bunker Log ~ monthly archive.xls"
ActiveWorkbook.Close

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub</pre>
Alternatively, this will check if the file exists and delete it if it does. Take your pick :wink: .
<pre>Sub savesheet()
Dim savsheet As String, savfile As String, savName As String

Application.ScreenUpdating = False

savfile = ActiveWorkbook.Name
savsheet = ActiveSheet.Name
savName = "c:windowsdesktopETV Bunker Log ~ monthly archive.xls"

Sheets(savsheet).Copy
If FileExists(savName) Then Kill savName
ActiveWorkbook.SaveAs savName
ActiveWorkbook.Close

Application.ScreenUpdating = True

End Sub

Function FileExists(stFile As String) As Boolean
'Uses FULL filename
If Dir(stFile) <> "" Then FileExists = True
End Function</pre>
HTH

 

[skadan]

cheers Richie

stumbled across the displayalerts = true/false

which works, but will try your other solutions too.

Mike

 

[Jack in the UK]

Hi skadan

stumbled across the displayalerts = true/false
|>>> YES very tue but thats not what you Original Post requested help on, this syntax will simply over write the file witout warning NOT delete as you required / requested,

Im posting this so you know and understand the diffeence.

IN EFFECT they will do the same thing i guess as a result, like i say thats notwhat you asked for and Riche(UK) has offered a solution not a fix, the diffecence im sure you will now understand.

which works, but will try your other solutions too.
>> Worth a go and will mean you can edit the VBA script to cover more or less as needs grow.

Im not posting this to be a jerk, but to hightlight the difference thats all, and many others will also read this post i i hope the share of knowledge is received from the heart as its delivered to you / all

Kindest possible regards

JITUK