Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
bzm
 
Posts: n/a
Default Prevent error messagebox when refresing querytable from the web fails

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)

  #2   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
Ken Johnson
 
Posts: n/a
Default Prevent error messagebox when refresing querytable from the web fails

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

  #3   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
bzm
 
Posts: n/a
Default Prevent error messagebox when refresing querytable from the web fails

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

  #4   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
Ken Johnson
 
Posts: n/a
Default Prevent error messagebox when refresing querytable from the web fails

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

  #5   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
Tom Ogilvy
 
Posts: n/a
Default Prevent error messagebox when refresing querytable from the web fails

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" wrote in message
oups.com...
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





  #6   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
Norman Jones
 
Posts: n/a
Default Prevent error messagebox when refresing querytable from the web fails

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" wrote in message
...
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" wrote in message
oups.com...
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





  #7   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
Ken Johnson
 
Posts: n/a
Default Prevent error messagebox when refresing querytable from the web fails

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

  #8   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
Tom Ogilvy
 
Posts: n/a
Default Prevent error messagebox when refresing querytable from the web fails

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" wrote in message
...
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" wrote in message
...
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" wrote in message
oups.com...
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







  #9   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
Norman Jones
 
Posts: n/a
Default Prevent error messagebox when refresing querytable from the web fails

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" wrote in message
...
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



  #10   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
bzm
 
Posts: n/a
Default Prevent error messagebox when refresing querytable from the web fails

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



  #11   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
bzm
 
Posts: n/a
Default Prevent error messagebox when refresing querytable from the web fails

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

  #12   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
Ken Johnson
 
Posts: n/a
Default Prevent error messagebox when refresing querytable from the web fails

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

  #13   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
bzm
 
Posts: n/a
Default Prevent error messagebox when refresing querytable from the web fails

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

  #14   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
bzm
 
Posts: n/a
Default Prevent error messagebox when refresing querytable from the web fails

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Prevent error messagebox when refresing querytable from the web fails bzm Excel Discussion (Misc queries) 16 January 17th 06 04:32 PM
Prevent error messagebox when refresing querytable from the web fails bzm New Users to Excel 13 January 17th 06 02:24 PM


All times are GMT +1. The time now is 11:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"