Haven't done this for years. When I used Essbase it was a pleasure,
despite having only one macro command available (due to local
restriction on the software licence). This mimics the Essbase Retrieve
method into an Excel worksheet - ie. you have to select the range
before running Retrieve. This is because I always used a hidden area
on the worksheet that was set up to accept the data from Essbase,
which was then linked to cells in my formatted reports. The reason for
this is that any interference with the data extract area on the sheet
- such as by formatting cells - eventually with re-use resulted in
corruption of the data (usually by the cell remaining empty). The
Essbase version I used imported all data as text.
Taking all this into account it made the job of producing monthly
reports extremely simple and fast, enabling the use of blank template
workbooks or copying/renaming ones from the previous month. In essence
we only need a single line of code, but here is something slightly
more functional. Of course you can give the range any name you like,
although keeping it consistent means that you can run this macro on
any workbook (and have a macro button to name the range). :-
'=======================================
' MACRO TO UPDATE ALL ESSBASE RANGES
' IN A WORKBOOK
' All essbase extract ranges named "_essbase"
' Brian Baulsom September 1999
'======================================
Sub ESSBASE_UPDATE()
Dim wsName As String
Dim MyError As Boolean
On Error GoTo NoEssbase
'-------------------------
For Each ws In Worksheets
wsName = ws.Name
MyError = False
Application.Goto Reference:=ws.Range("_essbase")
If Not MyError Then Application.Run Macro:="EssMenuRetrieve"
Next
'--------------------------
On Error GoTo 0
Exit Sub
'--------------------------
NoEssbase:
MsgBox ("Worksheet called '" & wsName & "' has no essbase range.")
MyError = True
Resume Next
End Sub
'---------------------------------------
Regards
BrianB
===========================
