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

 

[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?

 

[Ken Johnson]

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

 

[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?

 

[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.

 

[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