Hello...

There is a daily validation process that involves manually going to
each tab in an .XLS file and doing "Cube Analysis"/"Refresh Sheet"
from the main toolbar to refresh the sheets.
This manual refresh operation of each sheet needs to be automated via
a macro, but this step does not show up in the macro after I record it
and save it.
I tried recording a macro going through the manual refresh of each
sheet and the code below is what I received back after saving the
macro, including comments added.

I have a project I inherited that uses Excel Cubes against a SQL
Server 2005 database. I am new to Excel Cubes, learning as I go.
As I am aware, these sheets are simply cubed "reports" showing the
data out of cubed queries.
I cannot locate inside the sheet where the code for the cubed report
is located.

The Excel Cube Analysis add-on tool is installed in my instance of
Excel 2003.

Where you see "Cube Analysis"/"Refresh Sheet" as a comment was where I
refreshed that particular tab.
Those refresh steps did not show up in the macro.
I would like to see the reason why this occurs, and the syntax for
including the refresh step in the macro in my macro.

Thanks.

<begin macro code
Sub mcrValidationCheck1671()
' mcrValidationCheck1671 Macro
' Macro recorded 4/23/2007
' Keyboard Shortcut: Ctrl+Shift+A

'selected the first tab in the workbook
Sheets("1671-1").Select 'selected the first tab in the workbook
Range("B35").Select ' simply selected a cell in this sheet
' <clicked "Cube Analysis"/"Refresh Sheet" from toolbar right here
to refresh this tab

Sheets("1671-2").Select
Range("AK50").Select
' <clicked "Cube Analysis"/"Refresh Sheet" from toolbar right here
to refresh this tab

Sheets("1671-3").Select
Range("C51").Select
' <clicked "Cube Analysis"/"Refresh Sheet" from toolbar right here
to refresh this tab

Sheets("1671-4").Select
Range("C56").Select
' <clicked "Cube Analysis"/"Refresh Sheet" from toolbar right here
to refresh this tab

'save off the entire .xls file as another name...
ChDir "X:\CLA Support\Daily Validation"
ActiveWorkbook.SaveAs Filename:= _
"X:\CLA Support\Daily Validation\1671 04-23-07-333.xls",
FileFormat:=xlNormal _
, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub
<end macro code

To the best of my knowledge Cube Analysis does not have an API allowing you
to get at the functions on the toolbar. That will have to be done manually.
--
HTH...

Jim Thomlinson


" wrote:


Hello...

There is a daily validation process that involves manually going to
each tab in an .XLS file and doing "Cube Analysis"/"Refresh Sheet"
from the main toolbar to refresh the sheets.
This manual refresh operation of each sheet needs to be automated via
a macro, but this step does not show up in the macro after I record it
and save it.
I tried recording a macro going through the manual refresh of each
sheet and the code below is what I received back after saving the
macro, including comments added.

I have a project I inherited that uses Excel Cubes against a SQL
Server 2005 database. I am new to Excel Cubes, learning as I go.
As I am aware, these sheets are simply cubed "reports" showing the
data out of cubed queries.
I cannot locate inside the sheet where the code for the cubed report
is located.

The Excel Cube Analysis add-on tool is installed in my instance of
Excel 2003.

Where you see "Cube Analysis"/"Refresh Sheet" as a comment was where I
refreshed that particular tab.
Those refresh steps did not show up in the macro.
I would like to see the reason why this occurs, and the syntax for
including the refresh step in the macro in my macro.

Thanks.

<begin macro code
Sub mcrValidationCheck1671()
' mcrValidationCheck1671 Macro
' Macro recorded 4/23/2007
' Keyboard Shortcut: Ctrl+Shift+A

'selected the first tab in the workbook
Sheets("1671-1").Select 'selected the first tab in the workbook
Range("B35").Select ' simply selected a cell in this sheet
' <clicked "Cube Analysis"/"Refresh Sheet" from toolbar right here
to refresh this tab

Sheets("1671-2").Select
Range("AK50").Select
' <clicked "Cube Analysis"/"Refresh Sheet" from toolbar right here
to refresh this tab

Sheets("1671-3").Select
Range("C51").Select
' <clicked "Cube Analysis"/"Refresh Sheet" from toolbar right here
to refresh this tab

Sheets("1671-4").Select
Range("C56").Select
' <clicked "Cube Analysis"/"Refresh Sheet" from toolbar right here
to refresh this tab

'save off the entire .xls file as another name...
ChDir "X:\CLA Support\Daily Validation"
ActiveWorkbook.SaveAs Filename:= _
"X:\CLA Support\Daily Validation\1671 04-23-07-333.xls",
FileFormat:=xlNormal _
, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub
<end macro code