Database update Mk II

[Ken McLennan]

G'day there One & All,

I recently posted here requesting assistance to determine when an
OLAP query had finished. I've since had a chance to look further at the
spreadsheets in the office, and they're starting to get a bit clearer.

To reiterate, I'm trying to build a report which is intended to be
printed. I have the layout done, and some of the data is obtainable
through simple links to cells. I'll have to search various pages to
obtain the remainder, but that's not my problem at the moment.

First off I need to set various parameters on the data sheets from
within my report. Each sheet then rebuilds from the OLAP cube and
refreshes itself. Only then do I have valid data to use. I need to find
a way to determine when these refreshes are complete and then build my
report from the now updated data.

I've poked about at work and found that there are no code modules.
in the data sheets. Each workbook consists entirely of PivotTables with
some setup code in ThisWorkBook.

That being the case, I can't think of anyway to programatically
determine when an update has completed. It's simple enough to do it
manually; the status bar stops showing messages. Unfortunately, that's
not much good to me in this case. Unless, perhaps, I monitor the status
bar but that seems a bit clunky. It's possible it may remain static
while the network slows down my requests. (I think our network operates
on damp string rather than copper wire).

I'm not a programmer, and have zero experience with databases &
reports. This is my first attempt at using them. If anybody knows how I
can find when the refreshes are finished, I'll be happy to hear from
you.

Thanks for listening,
Ken McLennan
Qld, Australia

 

[Joel]

You can use the event below. Put in Thisworkbook VBA sheet.

Private Sub QueryTable_AfterRefresh(Success As Boolean)
If Success
' Query completed successfully
Else
' Query failed or was cancelled
End If
End Sub


You have two ways of handling the event

1) Put the code you want in the event handler
2) Set what is called a semiphore in the event handler when the query is
updated. Make a variable Public like this

Public EventCounter as integer
Public LastCount as integer

Sub QueryTable_AfterRefresh(Success As Boolean)
If Success
' Query completed successfully
EventCounter = EventCounter + 1
Else
' Query failed or was cancelled
End If
End Sub

Then in a public module
sub MyModule

'Initialize counters
OldEventCounter = 0
LastCount = 0

'wait 5 minutes
'Application.OnTime Now + TimeValue("00:05:00"), "QueryUpdate"

End sub


sub QueryUpdate()

'only execute your code if the counter has changed
if LastCount <> EventCounter then
'Enter your query code here

update counter
LastCount = EventCounter
end if
'wait again for next update
'Application.OnTime Now + TimeValue("00:05:00"), "QueryUpdate"

end sub

 

[Ken McLennan]

G'day there Joel,

You can use the event below. Put in Thisworkbook VBA sheet.

Thanks very much for that code. I thought I was on a winner with
those routines you sent, however I've not been able to implement
them.

The worksheets that obtain the data from the OLAP cube can't be
locked. They're open to anybody to corrupt or delete and if that happens
they're simply replaced by getting new copies from the central file
server. For that reason I tried to add your code programmatically from
my report sheet. That way it would still work if someone did delete the
datasheets and I could just run my report from the replacements.

The problem now, is that the security levels have been set by our
Information Security people to not allow code changes programmatically.

Your code would still be easily implemented manually, but it will
disappear the first time somebody stuffs it up (and I have no doubt that
WILL happen!!)

Do you know of a way I can monitor the data worksheets from my
report worksheet? I've already set each of the data worksheets to an
object in my report sheet when I was trying to write your code
programmatically into the data sheets. I'm still looking at it, but it's
largely a matter of trial & error which is very time consuming.

Thanks for the assistance you've given me so far. At least now I know
some of the events to look for. I had a browse through the object model
thingy (F2 key in the code editor) today looking at PivotTable stuff.
It's quite comprehensive and I'm still trying to get my head around it.
I'm sure you have me headed in the right direction.

Thanks again,
Ken McLennan
Qls, Australia

 

[Ken McLennan]

G'day there again Joel,

[This followup was posted to microsoft.public.excel.programming and a
copy was sent to the cited author.]

You can use the event below. Put in Thisworkbook VBA sheet.

Private Sub QueryTable_AfterRefresh(Success As Boolean)
If Success
' Query completed successfully
Else
' Query failed or was cancelled
End If
End Sub

I've had a bit of a success, after fiddling about for a bit I've
now simply copied the singe sheets I wanted from the data workbooks into
my report workbook (I thougtht they were protected, but I was mistaken).
I'm now trying to get the pivot table to connect to the cube from within
my new workbook, but I hope to figure that out somehow when I get a
chance to play with it again.

The good news is that I'll now be able to use the code you
supplied to flag whent the updates are done.

So, thanks once again for your assistance. I appreciate your help.

See ya
Ken McLennan
Qld, Australia