G'day there One & All,

I'm currently trying to automate a reporting process at work where
data is derived from 3 separate spreadsheets. The 3 spreadsheets are the
results of a database query. After each spreadsheet has been loaded (and
each spreadsheet is a workbook with up to 20 or so pages), values such
as date, sales district, etc are set, and the data then refreshes.
Values are then copied manually from the required cells onto a Word
report which is then printed.

I'm in the process of building an XL document where the required
values are simply links to appropriate cells in the original 3
workbooks. I have a fair bit of it working fine (including some
calculated values which stretched my high school maths to the limit) but
I can't go further without writing code to search for & link to the
values I need. As part of that process I need to set the date & location
values from within my document. I'm sure I can do that, but whenever I
reset one of these values, the spreadsheets run an OLAP query and
recalculate the values. That's fine, they're supposed to, but since I
won't be updating the screen and will probably have the 3 workbooks
minimized, I need to know a method of finding out when the OLAP query
has finished and the calculations complete so I can continue with
"stuff".

Is there anyway I can monitor the progress of the queries to know
when to continue? I may even display a progress bar so my users won't be
left in the dark. Any assistance will be appreciated.

See ya later,
Ken McLennan
Qld, Australia

I know you can make a progress bar, but how I do not know. One thing you
could do is have a msgbox appear with some text in it saying the process is
complete. Just write the code for the msgbox at the end of code you are
waiting for.
--
Roland


"Ken McLennan" wrote:

G'day there One & All,

I'm currently trying to automate a reporting process at work where
data is derived from 3 separate spreadsheets. The 3 spreadsheets are the
results of a database query. After each spreadsheet has been loaded (and
each spreadsheet is a workbook with up to 20 or so pages), values such
as date, sales district, etc are set, and the data then refreshes.
Values are then copied manually from the required cells onto a Word
report which is then printed.

I'm in the process of building an XL document where the required
values are simply links to appropriate cells in the original 3
workbooks. I have a fair bit of it working fine (including some
calculated values which stretched my high school maths to the limit) but
I can't go further without writing code to search for & link to the
values I need. As part of that process I need to set the date & location
values from within my document. I'm sure I can do that, but whenever I
reset one of these values, the spreadsheets run an OLAP query and
recalculate the values. That's fine, they're supposed to, but since I
won't be updating the screen and will probably have the 3 workbooks
minimized, I need to know a method of finding out when the OLAP query
has finished and the calculations complete so I can continue with
"stuff".

Is there anyway I can monitor the progress of the queries to know
when to continue? I may even display a progress bar so my users won't be
left in the dark. Any assistance will be appreciated.

See ya later,
Ken McLennan
Qld, Australia

G'da there Roland,

I know you can make a progress bar, but how I do not know.

That's the easy bit. I got one from one of John Walkenbach's
excellent books and have already used it in an application at work :)

I once found a website that had about 8 or 9 different ones that
were all scripted in VB. That was several years ago, and I've never
found it since.

One thing you
could do is have a msgbox appear with some text in it saying the process is
complete. Just write the code for the msgbox at the end of code you are
waiting for.

Unfortunately, I don't have access to the code in the sheets that
are doing the processing, nor do I think it would matter anyway.

I've had another look at the sheets while at work, and they're
just PivotTables whose updates are a function of changing selected
PivotField values. Those updates are then a built in function of
accessing the OLAP cube. (At least, I think that's how it works but if
anyone can correct my notions then go right ahead!!)

Anyway, I'm off to read the bits of code I DID have access to and
was able to print up. Whether I can make sense of it is entirely another
matter <g.

Thanks for your assistance Roland. I appreciate your response.

See ya
Ken McLennan
Qld, Australia