VBA command to say "yes"/"no" to popup window

[Manuel]

Hi,

Does anyone know what VBA command I could insert into my
Macro that would automatically select "yes" or "no" to an
Excel pop-up window?

So for example, I have a Macro which opens and closed
workbooks and moves data from one workbook to another. In
the process, I'll get an Excel window, which asks "do you
want to save" (or something along those lines). I then
click "No" and the Macro continues along its merry way. I
was wondering if I could insert VBA code at that junction
which would automatically select "No" to that prompt.

Please advise.

Thanks,

Manuel

 

[Ron de Bruin]

Hi Manuel

If you use this for example the workbook will be closed without saving

ThisWorkbook.Close SaveChanges:=False

 

[Harald Staff]

Hi Manuel

You don't reply to a popup like that, you make sure there are none. Like
this:

Application.DisplayAlerts = False
'your code here
Application.DisplayAlerts = True

HTH. Best wishes Harald

 

[Ikke]

It is possible to set the default value to no in the msgbox
The msg box definition is then
MsgBox("Do you want to save?", vbYesNo + vbDefaultButton2, "test1")

Greetz
Ikke

 

[Manuel Soares]

Thanks, your suggestion may work for one part of the macro, but not for
others. Let me explain. The macro pulls (copies then pastes) data from
text files, then closes said text files. 3 times while running the
macro I receive the following message:

'FILE NAME' is not in Microsoft Excel 97 format. Do you want to save
your changes?

I manually click NO to these prompts.

I also receive a message that says the following:

A file named 'File Name' already exists in this location. Do you want
to replace it?

I manually click YES to this prompt (it's necessary that this particular
file is overwritten each time the macro runs).

So turning off the displays for the 1st message would probably resolve
prompt 1, but at prompt 2 I want to be able to click YES to overlay the
file, and I don't believe removing the prompts would accomplish this, or
would it?

I appreciate any help you can provide.

Manuel

 

[Manuel Soares]

Thanks for the suggestion, but I don't think it'll suit my needs. Let
me explain further, as I wasn't very clear before. The macro pulls
(copies then pastes) data from text files, then closes said text files.
3 times while running the macro I receive the following message:

'FILE NAME' is not in Microsoft Excel 97 format. Do you want to save
your changes?

I manually click NO to these prompts.

I also receive a message that says the following:

A file named 'File Name' already exists in this location. Do you want
to replace it?

I manually click YES to this prompt (it's necessary that this particular
file is overwritten each time the macro runs).

So I'm not sure the command you suggested would work becaue I'm dealing
with closing text files after I've imported data into Excel, and not
Workbooks. Do you have any other suggestion?

I appreciate any help you can provide.

Manuel

 

[Harald Staff]

'FILE NAME' is not in Microsoft Excel 97 format. Do you want to save
your changes?

I manually click NO to these prompts.

Hi Manuel

The correct syntax is impossible to guess without seeing your code, but I'll
try.
Put
Workbooks("FILE NAME").Saved = True
immediately before
Workbooks("FILE NAME").Close

I also receive a message that says the following:

A file named 'File Name' already exists in this location. Do you want
to replace it?

I manually click YES to this prompt (it's necessary that this particular
file is overwritten each time the macro runs).

Application.DisplayAlerts = False
'either :
Workbooks("FILE NAME").Save
'or :
Workbooks("WHATEVER").SaveAs "FILE NAME"
Application.DisplayAlerts = True
Workbooks("FILE NAME").Close

Don't just participate in USENET...get rewarded for it!

You get paid for receiving our free assistance ?

HTH. Best wishes Harald

 

[Keith Willshaw]

Thanks for the suggestion, but I don't think it'll suit my needs. Let
me explain further, as I wasn't very clear before. The macro pulls
(copies then pastes) data from text files, then closes said text files.
3 times while running the macro I receive the following message:

'FILE NAME' is not in Microsoft Excel 97 format. Do you want to save
your changes?

I manually click NO to these prompts.

I also receive a message that says the following:

A file named 'File Name' already exists in this location. Do you want
to replace it?

I manually click YES to this prompt (it's necessary that this particular
file is overwritten each time the macro runs).

So I'm not sure the command you suggested would work becaue I'm dealing
with closing text files after I've imported data into Excel, and not
Workbooks. Do you have any other suggestion?

I appreciate any help you can provide.

Manuel

You can suppress these messages by using the command
Application.DisplayAlerts = False

dont forget to turn them on again when you are done

Keith