How do I program a macro to do an essbase retrieve? I am trying to create a
macro that will do a retrieve on each of my worksheets. Any help would be
appreciated.

Hello Duncan,

You have to put these in the (General)(Declararions) of your module...

Declare Function EssVCalculate Lib "essexcln.XLL" (ByVal sheetName As
Variant, ByVal calcScript As Variant, ByVal synchronous As Variant) As Long
Declare Function EssVCancelCalc Lib "essexcln.XLL" (ByVal sheetName As
Variant) As Long
Declare Function EssVCascade Lib "essexcln.XLL" (ByVal sheetName As Variant,
ByVal range As Variant, ByVal selection As Variant, ByVal path As Variant,
ByVal prefix As Variant, ByVal suffix As Variant, ByVal level As Variant,
ByVal openFile As Variant, ByVal copyFormats As Variant, ByVal overwrite As
Variant, ByVal listFile As Variant) As Long
Declare Function EssVCell Lib "essexcln.XLL" (ByVal sheetName As Variant,
ParamArray memberList() As Variant) As Variant
Declare Function EssVConnect Lib "essexcln.XLL" (ByVal sheetName As Variant,
ByVal username As Variant, ByVal password As Variant, ByVal server As
Variant, ByVal Application As Variant, ByVal database As Variant) As Long
Declare Function EssVDisconnect Lib "essexcln.XLL" (ByVal sheetName As
Variant) As Long
Declare Function EssVFlashBack Lib "essexcln.XLL" (ByVal sheetName As
Variant) As Long
Declare Function EssVGetCurrency Lib "essexcln.XLL" (ByVal sheetName As
Variant) As Variant
Declare Function EssVGetDataPoint Lib "essexcln.XLL" (ByVal sheetName As
Variant, ByVal cell As Variant, ByVal range As Variant, ByVal aliases As
Variant) As Variant
Declare Function EssVGetGlobalOption Lib "essexcln.XLL" (ByVal item As Long)
As Variant
Declare Function EssVGetSheetOption Lib "essexcln.XLL" (ByVal sheetName As
Variant, ByVal item As Variant) As Variant
Declare Function EssVKeepOnly Lib "essexcln.XLL" (ByVal sheetName As
Variant, ByVal range As Variant, ByVal selection As Variant) As Long
Declare Function EssVRemoveOnly Lib "essexcln.XLL" (ByVal sheetName As
Variant, ByVal range As Variant, ByVal selection As Variant) As Long
Declare Function EssVPivot Lib "essexcln.XLL" (ByVal sheetName As Variant,
ByVal range As Variant, ByVal startPoint As Variant, ByVal endPoint As
Variant) As Long
Declare Function EssVRetrieve Lib "essexcln.XLL" (ByVal sheetName As
Variant, ByVal range As Variant, ByVal lockFlag As Variant) As Long
Declare Function EssVSendData Lib "essexcln.XLL" (ByVal sheetName As
Variant, ByVal range As Variant) As Long
Declare Function EssVSetCurrency Lib "essexcln.XLL" (ByVal sheetName As
Variant, ByVal currencyIdentifier As Variant) As Long
Declare Function EssVSetGlobalOption Lib "essexcln.XLL" (ByVal item As Long,
ByVal globalOption As Variant) As Long
Declare Function EssVSetSheetOption Lib "essexcln.XLL" (ByVal sheetName As
Variant, ByVal item As Variant, ByVal sheetOption As Variant) As Long
Declare Function EssVUnlock Lib "essexcln.XLL" (ByVal sheetName As Variant)
As Long
Declare Function EssVZoomIn Lib "essexcln.XLL" (ByVal sheetName As Variant,
ByVal range As Variant, ByVal selection As Variant, ByVal level As Variant,
ByVal across As Variant) As Long
Declare Function EssVZoomOut Lib "essexcln.XLL" (ByVal sheetName As Variant,
ByVal range As Variant, ByVal selection As Variant) As Long
Declare Function EssVSetMenu Lib "essexcln.XLL" (ByVal setMenu As Boolean)
As Long
Declare Function EssVGetStyle Lib "essexcln.XLL" (ByVal sheetName As
Variant, ByVal styleType As Variant, ByVal dimName As Variant, ByVal item As
Long) As Variant
Declare Function EssVSetStyle Lib "essexcln.XLL" (ByVal sheetName As
Variant, ByVal styleType As Variant, ByVal dimName As Variant, ByVal item As
Long, ByVal newValue As Variant) As Long

I call the Essbase Retrieve routine like this...

' Refresh Overhead Budget Data Using Essbase Query
Call GetDeptExpData("OHBdgt1 Qry", "OHBdgt1_ESSQRY")
Call GetDeptExpData("OHBdgt2 Qry", "OHBdgt2_ESSQRY")

This is the subroutine that I use to retrieve Essbase data....

Sub GetDeptExpData(pSheet As String, pRange As String
'-----------------------------------------------------------------------------------'
' This routine will login into the appropriate Essbase server and retrieve
data for '
' query defined in range on specified spreadsheet.
'
'-----------------------------------------------------------------------------------'

Dim book As Workbook
Dim sheet As Worksheet
Dim range As range

On Error GoTo EndMacro

Set book = ThisWorkbook
Set sheet = book.Worksheets(pSheet)
Set range = Worksheets(pSheet).range(pRange)

sheet.Select
x = EssVConnect(Null, "User", "Pwd", "Essbase Server", "deptexp",
"deptexp")
x = EssVSetSheetOption(Null, 9, "0")
x = EssVRetrieve(Null, range, 1)
x = EssVDisconnect(Null)


range.Select
Cells.Replace What:="0", Replacement:="0", LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False

EndMacro:

Set book = Nothing
Set range = Nothing
Set Xrange = Nothing

End Sub

I hope this will get you going...

Dean.

"Duncan Haines" wrote:

How do I program a macro to do an essbase retrieve? I am trying to create a
macro that will do a retrieve on each of my worksheets. Any help would be
appreciated.