Skipping 'reopen' file prompt box using VBA

Hi all,

I have a piece of code that opens another spreadsheet, imports some data,
saves a closes down that file. this file that has shared access so it can be
updated similtaneously over a network.

However, if I have this particular file open on my machine and I run the
above code, I get the prompt box stating,

"example.xls is already open. Reopening will cause any changes to be
discarded. Do you want to reopen example.xls?", Yes or No...!

Is there any way of answering yes to this prompt box within the code so it
does not appear?

Thanks in advance,

Bhupinder Rayat

Good afternoon Bhupinder

Have you tried using the DisplayAlerts function?

This does not answer yes for you but will suppress the question in the first instance.

Use this line before you re-open the workbook:

Application.DisplayAlerts=False

At the end of the code use this to turn the alerts back on:

Application.DisplayAlerts=True

I've never actually used it to suppress this particular kind of error, so hope that works OK for you

HTH

DominicB

Bhupinder,

This will reopen an open file without the warnings..
but note that any changes are discarded!

Application.DisplayAlerts = False
Workbooks.Open (sPath)
Application.DisplayAlerts = True


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bhupinder Rayat wrote :

> Hi all,
>
> I have a piece of code that opens another spreadsheet, imports some
> data, saves a closes down that file. this file that has shared access
> so it can be updated similtaneously over a network.
>
> However, if I have this particular file open on my machine and I run
> the above code, I get the prompt box stating,
>
> "example.xls is already open. Reopening will cause any changes to be
> discarded. Do you want to reopen example.xls?", Yes or No...!
>
> Is there any way of answering yes to this prompt box within the code
> so it does not appear?
>
> Thanks in advance,
>
> Bhupinder Rayat

keepITcool,

thanks for that, with a bit a tweeking, it works like a charm!!

Bhupinder //

"keepITcool" wrote:

>
> Bhupinder,
>
> This will reopen an open file without the warnings..
> but note that any changes are discarded!
>
> Application.DisplayAlerts = False
> Workbooks.Open (sPath)
> Application.DisplayAlerts = True
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Bhupinder Rayat wrote :
>
> > Hi all,
> >
> > I have a piece of code that opens another spreadsheet, imports some
> > data, saves a closes down that file. this file that has shared access
> > so it can be updated similtaneously over a network.
> >
> > However, if I have this particular file open on my machine and I run
> > the above code, I get the prompt box stating,
> >
> > "example.xls is already open. Reopening will cause any changes to be
> > discarded. Do you want to reopen example.xls?", Yes or No...!
> >
> > Is there any way of answering yes to this prompt box within the code
> > so it does not appear?
> >
> > Thanks in advance,
> >
> > Bhupinder Rayat
>

Hi, how were you able to resolve this? I have the same issue, however, if one person is attempting to write to the database, which is an excel document and its open, then another person tries to write after, it will overwrite the first person's entry. I was thinking to build a loop so that if the error occurs, to loop until the workbook is available. Can anyone assist please?

I had the same issue. For me it appeared not related to the workbook containing my vba.
I noticed this when I tried opening a clean excel file, I noticed that excel was trying to load an add on that I uninstalled
but its link was still sitting in the addins list. I just went to excel options--->addins ---> and i removed the uninstalled addin
from the active application list. Close and reopen ... everything went ok.

Then I opened my excel file containing my vba project, the prompt to reopen the file did not appear.