macro to delete existing file

skadan

Board Regular
Joined
Sep 5, 2002
Messages
54
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).
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
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

Board Regular
Joined
Sep 5, 2002
Messages
54
cheers Richie

stumbled across the displayalerts = true/false

which works, but will try your other solutions too.

Mike
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top