Prevent error messagebox when refresing querytable from the web fails

[bzm]

Hi,

I have an excel workbook with a querytable on a sheet
The querytable retrieve data from a web site, and I need that data to
be checked automatically, and saved when some data is changed.
So I create a class to implement QueryTable events, and I have my own
code in AfterRefresh, which saves changed data

I need this to be checked and saved automatically, regardless if I'm in
from of PC or not, so I set the querytable to refresh automatically
with 1 minute RefreshPeriod. Also, the querytable based obrect is
instantiated when I open the worbook

If sometimes data cannot be retrieved from the web, Excel opens a
Messagebpox telling me that it cannot access the url to retrieve data.
Unfortunately, this popup must be closed manually, by user, otherwise a
new refresh doesn't occurs.

What I want it to disable somehow that error message. Is there any way
to do that? If at some point the querytable cannot load data from the
web, that's it, it's not a big deal, but I want it to keep refreshing
as usual, since the connection will eventually go up again and further
data can be read.

Does anyone know how to do that? I tried to add sendkeys "{Enter}" in
BeforeRefresh event, hoping that in case of that messagebox, enter will
reach it and close, but don't work

Thanks for any suggestion

(PS: I use Excel2003)

[Ken Johnson]

Hi bzm,
Have you tried "Application.DisplayAlerts = False"?

If this is the solution then make sure it is set back to True before
the code has finished. Excel doesn't reset it itself.
Ken Johnson

[bzm]

Thanks for answering.

It seems this works. However, there are still some strange issues:
After working ok for a while (during this time, I still made some other
changed in the VBA code), at some point it opened that error again
When I checked Application.DisplayAlerts, I saw it is true (although I
set it to true in class_terminate event, and set to false in
class_initialize)

Maybe the code released the class, due some bug and unhandled error
message.

I'll check it out, but I think you pointed me into right direction.

Thanks, and best regards,
Bogdan Zamfir

[Ken Johnson]

Thanks for the feedback Bogdan, hope you get it all sorted. Class
modules are totally foreign to me so I can't offer any more
suggestions.
Ken Johnson

[Tom Ogilvy]

In the interests of completeness:

Ken stated:
> If this is the solution then make sure it is set back to True before
> the code has finished. Excel doesn't reset it itself.

from help on DisplayAlerts:

If you set this property to False, Micorosoft Excel sets this property to
True when the code is finished, unless you are running cross process code.



--

Regards,

Tom Ogilvy



"Ken Johnson" <[email protected]> wrote in message
news:[email protected]...
> Hi bzm,
> Have you tried "Application.DisplayAlerts = False"?
>
> If this is the solution then make sure it is set back to True before
> the code has finished. Excel doesn't reset it itself.
> Ken Johnson
>

[Norman Jones]

Hi Tom,

Using xl2k, the VBA help for DisplayAlerts, says:

'=====================
If you set this property to False, Microsoft Excel doesn't automatically set
it back to True when your macro stops running. Your macro should always set
the property back to True when it stops running.

'=====================

Are you aware when this changed?

---
Regards,
Norman



"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> In the interests of completeness:
>
> Ken stated:
>> If this is the solution then make sure it is set back to True before
>> the code has finished. Excel doesn't reset it itself.
>
> from help on DisplayAlerts:
>
> If you set this property to False, Micorosoft Excel sets this property to
> True when the code is finished, unless you are running cross process code.
>
>
>
> --
>
> Regards,
>
> Tom Ogilvy
>
>
>
> "Ken Johnson" <[email protected]> wrote in message
> news:[email protected]...
>> Hi bzm,
>> Have you tried "Application.DisplayAlerts = False"?
>>
>> If this is the solution then make sure it is set back to True before
>> the code has finished. Excel doesn't reset it itself.
>> Ken Johnson
>>
>
>

[Ken Johnson]

Thanks Tom, I'm a little behind the times.
Ken Johnson

[tony h]

an interesting observation from the help files but I have never noticed an occaision that it is not set back to TRUE when code stops.

regards

[Tom Ogilvy]

Thoughtful reflection on this article:
http://support.microsoft.com/kb/153043/en-us

would suggest to me that the behavior to reset to true has not changed and
that the help in xl2k is incorrect. I use xl97 extensively and don't ever
recall that displayalerts remained false after code terminated. Admittedly
I don't recall ever testing this explicitly, but believe I would have
noticed. Do you have a different experience?

--
Regards,
Tom Ogilvy

"Norman Jones" <[email protected]> wrote in message
news:%[email protected]...
> Hi Tom,
>
> Using xl2k, the VBA help for DisplayAlerts, says:
>
> '=====================
> If you set this property to False, Microsoft Excel doesn't automatically
set
> it back to True when your macro stops running. Your macro should always
set
> the property back to True when it stops running.
>
> '=====================
>
> Are you aware when this changed?
>
> ---
> Regards,
> Norman
>
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > In the interests of completeness:
> >
> > Ken stated:
> >> If this is the solution then make sure it is set back to True before
> >> the code has finished. Excel doesn't reset it itself.
> >
> > from help on DisplayAlerts:
> >
> > If you set this property to False, Micorosoft Excel sets this property
to
> > True when the code is finished, unless you are running cross process
code.
> >
> >
> >
> > --
> >
> > Regards,
> >
> > Tom Ogilvy
> >
> >
> >
> > "Ken Johnson" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi bzm,
> >> Have you tried "Application.DisplayAlerts = False"?
> >>
> >> If this is the solution then make sure it is set back to True before
> >> the code has finished. Excel doesn't reset it itself.
> >> Ken Johnson
> >>
> >
> >
>
>

[tony h]

That fits with my experience in both 97 and 2000. Again I don't specifically test for it. I do try to explicitly set it back but I am sure I would have noticed at some time if it didn't reset back to true - particularly during testing while code is incomplete

[Norman Jones]

Hi Tom,

Thank you for youe reply.

Simple testing confirms your original assertion.

> I use xl97 extensively and don't ever recall that displayalerts
> remained false after code terminated. Admittedly I don't recall
> ever testing this explicitly, but believe I would have noticed.
> Do you have a different experience?

No, I do not.

I checked VBA help in this instance, simply because I could not recall
having ever seen your quoted statement. I was, therefore surprised to
encounter the apparent discepancy.

FWIW, although I have always believed that the setting was not persistent, I
have invariably reset DisplayAlerts to true at the end of the macro: I guess
that is something which I could often drop in future.

Thank you for the link and information.


---
Regards,
Norman


"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> Thoughtful reflection on this article:
> http://support.microsoft.com/kb/153043/en-us
>
> would suggest to me that the behavior to reset to true has not changed and
> that the help in xl2k is incorrect. I use xl97 extensively and don't ever
> recall that displayalerts remained false after code terminated.
> Admittedly
> I don't recall ever testing this explicitly, but believe I would have
> noticed. Do you have a different experience?
>
> --
> Regards,
> Tom Ogilvy

[bzm]

Hi,

It seems it might need something else too
In my class module, in BeforeRefresh event, I set
application.DisplayAlerts = .f.

Still, from time to time, I get the popup error message

Is there any way to close that automatically? A timeout or something?

Or any other setting to force that to not show up?

Thank you
Bogdan

[bzm]

Actually, DisplayAlerts seems ok, but somehow it gets reset to true
when I get back in class module, AfterRefresh event. Does anyone know
how to persist that?

Thanks,
Bogdan

[Ken Johnson]

Hi bzm,
If you can see where and what is causing the error you might get away
with:

On Error Resume Next

placed before the offending line then:

On Error Goto 0

after the offending line.

Ken Johnson

[bzm]

I know that, but the problem is the error is caused when the
auto-refresh of the QueryTable object. And I don't trigger it, instead
it is triggered automatically, sinc I set the QueryTable as

qtQueryTable.BackgroundQuery = True
qtQueryTable.RefreshPeriod = 1

So it is refreshed automatocally

A possible approach would be not use user QueryTable.RefreshPeriod, but
instead use a timer, and in timer event, call QueryTable.Refresh (after
an "On Error Resume Next" statement)

However, I cannot find a timer object in Excel. Is there any way to use
one? Or should I use some API code to call a Windows timer?

Thanks for all suggestions

Regards,
Bogdan

[bzm]

Hi,

After a little more research, I found Application.OnTime method, which
allow to run code at certain time.
This seems to be a solution, I can set Application.DisplayAlerts=false
and call Querytable.Refresh with error handler off.
I'll try it.

Thanks to all for all answers.
Regards,
Bogdan

[tony h]

It sounds as though you know what you are doing but a couple of thoughts.


It seems more than a little puzzling that you say you set displayalerts to false and then sometime later it happens to be true.

One situation that can appear to give rise to this effect is when you instantiate the object on the fly. eg you use a "dim as new" rather than a dim as and a set obj= new ...

In this situation, and if you are not careful also in the second, you can automatically "reinstantiate" the object but this is actually an instantiation of a new object of the same class.

If code executtion ceases the references to the object will be lost and the object terminated. When the code starts up again the object will be referenced but the settings will need to be reset.

To find out if this is happening when it is not obvious I put a : debug.print now, "initialise" in the class initialisation event. so that the effect can be traced. and also one in the terminate.

Problems can also be caused by unexpectedly creating a new object for each call.

regards
Tony