Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
|
|||
|
|||
Prevent error messagebox when refresing querytable from the web fails
Thanks Tom, I'm a little behind the times.
Ken Johnson |
#8
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent error messagebox when refresing querytable from the web fails | Excel Discussion (Misc queries) | |||
Prevent error messagebox when refresing querytable from the web fails | New Users to Excel |