Code to answer No to an error message

[boohah]

Hi

I have a macro enable spreadsheet (Book1.xlsm) and one of its functions is to copy data from a second spreasheet (Book2.xlsx) and copy it into the original spreasheet (Book1.xlsm). Everything works fine if Book2 is closed when i run the macro but if it is already open I get the following message:

"Book2.xlsx is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen Book1.xlsx" [Yes] [No]

The code in use is large and complicated so I haven't pasted it in this forum. All I need is a single line of code that answers No to the above error message.

Any help would be appreciated.

Doug

[romperstomper]

You should check in your code to see if it's already open:

Dim wbk as workbook
on error resume next
set wbk = workbooks("Book2.xlsx")
on error goto 0
if wbk is nothing then
   set wbk = workbooks.open("path to workbook here")
End if

[Andrew-R]

Before you open the new workbook add the line:

Application.DisplayAlerts=False

And after the open put:

Application.DisplayAlerts=True

[romperstomper]

IIRC, the default answer is Yes, so that won't do what was required.

[Andrew-R]

Does turning off displayalerts just give the default answer?

Actually I've just tested it and it works fine. I think it assumes that you want to just go ahead and do whatever damn fooled thing you were trying to do.

[romperstomper]

Does turning off displayalerts just give the default answer?

Yes.

Actually I've just tested it and it works fine. I think it assumes that you want to just go ahead and do whatever damn fooled thing you were trying to do.

but if you had unsaved changes, you would lose them.

[Andrew-R]

Good point - that's what I get for not reading the OP's request properly.

[boohah]

Thanks andrew and romperstomper.

I have used romperstomper's method and I am getting results. If I hide the alert it does work too but it just reopens the file over the top without any chance of saving. Thankyou both for your super fast help, you guys are champions.

Doug

[Andrew-R]

Credit goes to romperstomper for answering the question you actually asked, rather than making one up and answering that instead

[romperstomper]

Can be more fun that way, though.

[boohah]

I added to both of your reputations because you are both doing your best to make other peoples lives easier and it is appreciated. Also the code to hide error messages is always handy and I will keep it in my bag of tricks.

Thanks again.

[romperstomper]

Thank you kindly.