Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

Resolved Question

Need Excel macro to answer a dialogue box

Sep 6, 2011 6:24PM PDT

Hi,

I am sure this is really simple, but I can't work it out or find the answer anwhere.

I have a macro that opens a workbook, switches to an other work book, changes links to the workbook just opened, and then closes it.

It repeats this for about 12 files.

Problem is each time it shuts the workbook, a message appears saying Save Yes/No ?, and I have to click no, which defeats the whole point of the macro, in that I can't leave it to run unatended.

How do I make the macro answer the message box by selecting 'No' ?

Discussion is locked

rcgyuk has chosen the best answer to their question. View answer

Best Answer

- Collapse -
Re: auotanswer
Sep 6, 2011 6:37PM PDT

Two options:
1. Use the 'close without saving' parameter when you close the file.
2. Use sendkeys to answer the question.

The first seems better.

Kees

- Collapse -
Re:
Sep 6, 2011 8:15PM PDT

I couldn't get sendkeys to work, I tried Application.SendKeys ("N").

In the end I used:

Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

This solves half the problem. The remaining thing I am struggling with is each time the macro opens one of the workbooks, that workbook itself has an auto running macro that ends by displaying a message box that requires you to click OK.

I tried turning workbook security to high, so the macro didn't run in the first place, that didn't work.

Next I tried:

Application.SendKeys ("~")

Which I believe is how to send an enter key, but still nothing.

This is the piece of code:

Workbooks.Open Filename:= _ "G:\INHQLONCTY\Finance Hq\Managment Reports\2011\MRR\04 Apr\IIDK MRR 1104.xls" _ , UpdateLinks:=0
Application.SendKeys ("~")
ActiveWindow.ActivateNext

Where am I going wrong?

Many thanks

- Collapse -
Re: auto running macro
Sep 6, 2011 8:23PM PDT
- Collapse -
Re: auto running macro
Sep 6, 2011 8:45PM PDT

Sorted Happy

Thanks for your help

Robert