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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,775
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,177
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,177

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
38,775
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,177

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,792
Office Version
  1. 365
Platform
  1. Windows
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,177
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,792
Office Version
  1. 365
Platform
  1. Windows
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.)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,714
Messages
5,833,276
Members
430,201
Latest member
Deepakpilla36

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