Prevent Overwrite Warning in VBA

Question

I am writing a macro to extract XML data from a Web API into a spreadsheet. The first column contains two fields delimited with a comma so I have extended the macro to insert a column then run Text to Columns to split out the data.

The macro works really well, but I get an annoying warning asking if I want to replace the data:

enter image description here

Is there a way to suppress the warning?

score 9 · Accepted Answer · edited Sep 10 '14 at 06:42

9

source: SolutionSite

To suppress the warnings:

Application.DisplayAlerts = False

To activate the warnings:

Application.DisplayAlerts = True

edited Sep 10 '14 at 06:42

Jean-François Corbett

37,420
30
139
188

answered Sep 09 '14 at 08:13

Synoon

2,297
4
22
37

Jean-François Corbett · Answer 2 · 2014-09-09T08:19:51.790

1

Application.DisplayAlerts = False

Two important points from the documentation:

[...] when a message requires a response, Microsoft Excel chooses the default response.

and

If you set [DisplayAlerts] to False, Microsoft Excel sets [it back] to True when the code is finished [...]

meaning you'll still have the standard "Do you want to save before closing?" etc. prompts during normal Excel use.

edited Sep 09 '14 at 08:19

answered Sep 09 '14 at 08:11

Jean-François Corbett

37,420
30
139
188

Awesome! Works a treat. – Spionred Sep 09 '14 at 08:13

score 1 · Answer 3 · answered Dec 11 '21 at 03:21

I found a scenario where neither Application.DisplayAlerts = False nor Application.AlertBeforeOverwriting = False work.

When running some of the Data Analysis Add-In tools the messaging is bypassed. Whilst not ideal, I found a solution from prsthlm whereby one uses Application.SendKeys "{ENTER}" before the Run method call to handle the overwrite pop-up. E.g.

Pseudo code:

With Application
    .SendKeys "{ENTER}"
    .Run "ATPVBAEN.XLAM!Histogram", range1  _
    , range2, range3, False, _
    True, True, False
End With

Prevent Overwrite Warning in VBA

3 Answers3

Linked