VBA to overwrite an existing file

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,175
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?
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,517
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can check for the file and delete it first:
Code:
If Dir(filepath) <> "" then kill filepath
 

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,175
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?
 

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,175

ADVERTISEMENT

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,517
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Makes no sense to me, but you might try
Code:
Application.DisplayAlerts = False
to suppress the dialog.
 

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,175

ADVERTISEMENT

Me neither, but there you go. :)
I did try that, but it still asks if I want to overwrite the file.
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,785
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:

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,175
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.
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,785
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.)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,205
Messages
5,527,399
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top