VBA to overwrite an existing file

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,177
Hi all

I've code some VBA code which produces an output file from a template.
Now, if I want to run the output more than once, I get an message asking if I want to overwrite the existing file (which I always do).

I've tried turning off displayalerts but it's not working.
Is there any way of turning this message off?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
You can check for the file and delete it first:
Code:
If Dir(filepath) <> "" then kill filepath
 
Upvote 0
I did look at that, but I don't think it'll work.
I stumbled upon an Application.AlertBeforeOverwriting but I'm not sure how to use it (and the help files aren't that helpful).

Any ideas?
 
Upvote 0
Sorry, that was half a comment.
I don't think it will work as the file is on a secure server and it's very difficult to delete files, which is why I was overwriting in the first place. It is, however, easy enough to add/overwrite files to/on the server (bizarre, I know).
 
Upvote 0
Makes no sense to me, but you might try
Code:
Application.DisplayAlerts = False
to suppress the dialog.
 
Upvote 0
Me neither, but there you go. :)
I did try that, but it still asks if I want to overwrite the file.
 
Upvote 0
Did you try Application.AlertBeforeOverwriting=False immediately before you try to write the file?

If the file is being written by VBA code then maybe the warning message also being generated by VBA code, in which case you won't be able to suppress it in that way? Look for some code like this:-
Code:
if dir(somefilename) then
  if msgbox("Are you sure?",vbyesno)=vbyes then
    [COLOR=green]' some code to overwrite the file[/COLOR]
  end if
end if
If there's something like that in your procedure, that's where you should be focusing your attention.

Otherwise I think we've reached the point where you should post the code for us to look at.
 
Last edited:
Upvote 0
Here's the code that generates the output file:-
Code:
Sub b_exporter()
'----- Switch off screen updating (stop flicker) -----
    Application.ScreenUpdating = False
'----- Copy all worksheet to a new book -----
    Worksheets.Copy
'----- Go through worksheets, make into values only and hide row/column headers -----
    For Each ws In Worksheets
        ws.Select
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial (xlPasteValues)
        Range("B1").Select
        ActiveWindow.DisplayHeadings = False
    Next
'----- Destroy variable -----
    Set ws = Nothing
'----- Switch off alerts, remove data_Sheet1 as no longer needed and switch alerts back on -----
    Application.DisplayAlerts = False
    Sheets("Data_Sheet1").Delete
    Application.DisplayAlerts = True
'----- Select first sheet and save/close workbook with new name -----
    Sheets("Call Volumes").Select
    ActiveWorkbook.SaveAs filepath & "\Output Files\NHSD - " & Format(Sheets("Call Volumes").Range("C4"), "Mmmm YYYY") & ".xls"
    ActiveWorkbook.Close True
'----- Select first sheet on this workbook to go back to beginning -----
    Sheets("Call Volumes").Select
'----- Switch screen updating back on -----
    Application.ScreenUpdating = True
End Sub

It looks like it's an automatic error message generated by the system.
 
Upvote 0
I think it may be the ActiveWorkbook.Close which is triggering the message. (You can confirm this if you know how to single-step through the code) Why are you saving the workbook again when you close it if you've just saved it and not made any changes since then?

Try closing it with ActiveWorkbook.Close False.

(I'm only guessing.)
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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
Back
Top