LinkBack URL
About LinkBacksHow 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.
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.
Could you explain in beginner language how to write an Essbase retrieve macro?
Dean Hinson,
I tried to perform the below macro and had many errors occur. Could you explain it in language a beginner an understand?? There may have been alterations that neededf to be made, but I don't know how to write macros well enough to know better. Please help!!
Alison
Originally Posted by Dean Hinson
PM me with your phone number, and I'll believe I can walk you through it.
I am also having problems with my Macro to do a simple retrieve on one worksheet within my workbook. I know I am close, probably something small I am missing. I am a beginner fyi. I read through this post, tried to update my code, and still having problems. The Debug shows the error being on the Set Range line. I have linked my code below, please help!
Please Login or Register to view this content.
hi guys m new to VBA so can any 1 explain this code to me ?? please...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks