Excel: Cube Analysis add in refresh problems

[fmientjes]

I have made an OLAP cube and connect to it using Microsofts Cube Analysis add-in for Excel.

When I make changes to the design or change criteria I need to refresh the sheet.
Sometimes it doesn't refresh properly and I always have to refresh each worksheet (about 25!).
A macro for going into each sheet, then select "Cube analysis" and "Refresh Sheet" simply does not store anything, only the change of worksheet.

I have tried using "refresh connection" too, but then I still have to go into each worksheet for a refresh.

Any hints for me?
Thanks,
Frans

 

[AnotherHiggins]

Record a macro while you go to 'refresh connection' on a single sheet, then post that code. We can help you convert that to a macro that will automatically loop through each sheet in a WB.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[fmientjes]

This is all John: (nothing important)

Sub Macro1()
'
' Macro1 Macro
' De macro is opgenomen op 10-2-2005 door Frans Mientjes.
'

'
End Sub

 

[AnotherHiggins]

hrmph. I'm not using a datacube, but when I created an external data link and then went to refresh it, I get this line of code recorded:

Selection.QueryTable.Refresh BackgroundQuery:=False

I assumed you would get something similar. What I had in mind was something like this:

For i = 1 To ActiveWorkbook.Worksheets.count
    Sheets(i).Activate
    'your recorded code here
Next i

Just to make sure we're talking about the same thing, are you using Data > Refresh Data to update?

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[fmientjes]

No, not via data > Refresh Data.
I installed an add-in from Microsoft for the analysis services. Now I have an extra menu item called Cube Analysis. In that menu there is an option called Refresh sheet.
Unfortunately it looks like it is not programmable by code.

Any more hints?

Frans

 

[AnotherHiggins]

Oh, I see - when you click on 'Refresh sheet', you are actually triggering another macro. You can't record yourself running a macro via an addin - the macro recorder doesn't capture the name of the macro that is being called. That's why you get nothing recorded.

Out of curiosity, is Refresh Data available and, if so, does it do anything?

If not, I'm afraid I'm out of ideas. Maybe someone else here has a suggestion?

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886