R
Robin Hammond
Questions:
1. Can anyone think of a way to disable alerts on background queries when
the Excel application is in control. (please see below before answering that
I should set displayalerts = false).
2. Is there a timeout property that can be set on a web query.
This might seem a bit arcane, but is quite critical to a web data capture
routine. I need to turn off alerts WHILE excel, NOT vba is in control of
operations.
I have a vba routine that generates potentialy thousands of web queries in a
workbook. Madness I know but there is method. These queries run massively
quicker when they are performed as background queries - ie. once excel gets
back in control when the vba routine that built the queries has finished. I
have a timer running (ported from vb in an activex wrapper) that monitors
the progress of the queries, then runs some clean up routines when
everything has been completed.
Problems are:
1. If there is an error at the server or in the query, Excel displays an
alert. VBA is not running at this point other than the periodic timer
routine, so displayalerts = false makes no difference to the error message
being displayed. Excel help has the following to say about 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.
2. If the queries take too long, I get a time out error, also leading to
problem 1. And, once the errors start, they are hard to stop since the app
is halted for the error, meaning data returns are probably creating errors.
Finally, having spent ages trying to work out a robust timer solution in
vba, akin to the vb timer, I'll post the activex solution in the next few
days for general usage. If anyone would like to give it a whirl in the
meantime for a bit of testing, please let me know.
Thanks in advance,
Robin Hammond
www.enhanceddatasystems.com
1. Can anyone think of a way to disable alerts on background queries when
the Excel application is in control. (please see below before answering that
I should set displayalerts = false).
2. Is there a timeout property that can be set on a web query.
This might seem a bit arcane, but is quite critical to a web data capture
routine. I need to turn off alerts WHILE excel, NOT vba is in control of
operations.
I have a vba routine that generates potentialy thousands of web queries in a
workbook. Madness I know but there is method. These queries run massively
quicker when they are performed as background queries - ie. once excel gets
back in control when the vba routine that built the queries has finished. I
have a timer running (ported from vb in an activex wrapper) that monitors
the progress of the queries, then runs some clean up routines when
everything has been completed.
Problems are:
1. If there is an error at the server or in the query, Excel displays an
alert. VBA is not running at this point other than the periodic timer
routine, so displayalerts = false makes no difference to the error message
being displayed. Excel help has the following to say about 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.
2. If the queries take too long, I get a time out error, also leading to
problem 1. And, once the errors start, they are hard to stop since the app
is halted for the error, meaning data returns are probably creating errors.
Finally, having spent ages trying to work out a robust timer solution in
vba, akin to the vb timer, I'll post the activex solution in the next few
days for general usage. If anyone would like to give it a whirl in the
meantime for a bit of testing, please let me know.
Thanks in advance,
Robin Hammond
www.enhanceddatasystems.com