If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Prevent error messagebox when refresing querytable from the web fails
Thanks Tom, I'm a little behind the times.
Ken Johnson |
#8
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
|
Thread Tools | |
Display Modes | |
|
|