VBA to overwrite an existing file

[RichardMGreen]

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?

 

[RoryA]

You can check for the file and delete it first:

If Dir(filepath) <> "" then kill filepath

 

[RichardMGreen]

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?

 

[RoryA]

I did look at that, but I don't think it'll work.

because?

 

[RichardMGreen]

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]

Makes no sense to me, but you might try

Application.DisplayAlerts = False

to suppress the dialog.

 

[RichardMGreen]

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

 

[Ruddles]

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:-

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.

 

[RichardMGreen]

Here's the code that generates the output file:-

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]

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