CODEWITHSUNDEEP
excelvba
Spionred
Spionred

Reputation: 807

Prevent Overwrite Warning in VBA

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?

Upvotes: 2

Views: 19726

Answers (3)

QHarr
QHarr

Reputation: 84465

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

Upvotes: 2

Synoon
Synoon

Reputation: 2351

source: SolutionSite

To suppress the warnings:

Application.DisplayAlerts = False

To activate the warnings:

Application.DisplayAlerts = True

Upvotes: 9

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

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.

Upvotes: 1

Related Questions