1. Design an interactive interface on Excel, that include: listing box, drop down box, text box, label, button, radio option, check box,etc. For example, the region listing box, the product category listing box, the process(data retrieve) button, the submit or update button.
2. Add proper VBA code and call VBA MACRO functions on these control boxes. Macro such as: ESSConnect(), ESSMenu(), ESSRetrieve(),etc
3. Then we can run the on demand interactive Excel report to make special query on the Essbase data, and navigate on different sheets.
4. The VBA code related to these control boxes can be saved as a VBA function library, so that the code can be reused on other Excel report
The next is a simple but standard sample:
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 EssVGetMemberInfo Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal mbrName As Variant, ByVal action As Variant, ByVal aliases As Variant) As Variant
Declare Function EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long
Declare Function EssVFreeMemberInfo Lib "ESSEXCLN.XLL" (ByRef memInfo As Variant) As Long
Const EssBottomLevel = 3
Const EssChildLevel = 1
Const username = "admin"
Const password = "password"
Const ServerName = "bobpc"
Const AppllicationName = "Sample"
Const EssbaseDataBase = "Basic"
Sub Disconnect()
sFileName = ThisWorkbook.Name
For Each wsSheet In Worksheets
X = EssVDisconnect(sFileName & wsSheet.Name)
Next wsSheet
If X = 0 Then
answer = MsgBox("Disconnected!", 64, "Connection Dialog")
End If
End Sub
Sub lstRegion_Change()
v_ListIndex = Sheet1.Shapes(2).ControlFormat.ListIndex
v_ListText = Sheet1.Shapes(2).ControlFormat.List(v_ListIndex)
Call GetBottom(v_ListText)
End Sub
Sub UnselectListBox(ByVal ListBoxNam As Variant)
End Sub
Sub Refresh()
Call GetChild("Market")
Call GetBottom("Market")
End Sub
Sub Process()
End Sub
Sub Submit()
End Sub
Sub Export()
With Sheet1
Set cb = .Shapes.AddFormControl(xlCheckBox, 10, 10, 100, 10)
cb.ControlFormat.LinkedCell = "A1"
End With
End Sub
Sub Quit()
End Sub
Sub Actual()
'MsgBox (Sheet1.Shapes.Item(3).Name)
End Sub
Sub Budget()
'MsgBox (Sheet1.Shapes(3).ControlFormat.LinkedCell
End Sub
Sub Conn()
sFileName = ThisWorkbook.Name
For Each wsSheet In Worksheets
X = EssVConnect(sFileName & wsSheet.Name, username, password, ServerName, AppllicationName, EssbaseDataBase)
Next wsSheet
If X = 0 Then
' answer = MsgBox("Connected!", 64, "Connection Dialog")
Else
answer = MsgBox("not Connected!", 64, "Connection Dialog")
End If
End Sub
Public Sub Tester()
Dim rng As Range
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range(LastRow - 1)
rng.Select
End Sub
Sub SelectFirstToLastInColumn()
' Select From the First NonBlank to the Last Nonblank in the Column
Cells(1, 1).Select
Set TopCell = Cells(1, ActiveCell.Column)
Set BottomCell = Cells(Rows.Count, ActiveCell.Column)
If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)
If TopCell.Row = Rows.Count And BottomCell.Row = 1 Then
ActiveCell.Select
Else
Range(TopCell, BottomCell).Select
End If
End Sub
Sub GetChild(ByVal mbrName As Variant)
Call Conn
Dim vt As Variant
Dim cbItems As Variant
Dim i As Integer
Dim pMember As String
vt = EssVGetMemberInfo(ThisWorkbook.Name & "Sheet1", mbrName, EssChildLevel, False)
If IsArray(vt) Then
cbItems = UBound(vt) + 1
For i = 0 To UBound(vt)
Sheet1.Cells(i + 1, 1) = vt(i)
Next
Else
MsgBox ("Return Value = " + Str(vt))
End If
' Select From the First NonBlank to the Last Nonblank in the Column
Cells(1, 1).Select
Set TopCell = Cells(1, ActiveCell.Column)
Set BottomCell = Cells(Rows.Count, ActiveCell.Column)
If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)
If TopCell.Row = Rows.Count And BottomCell.Row = 1 Then
ActiveCell.Select
Else
Range(TopCell, BottomCell).Select
End If
Sheet1.Shapes(2).ControlFormat.ListFillRange = Range(TopCell, BottomCell).Address
'MsgBox (Worksheets(1).Shapes(2).Name)
X = EssVFreeMemberInfo(vt)
End Sub
Sub lstCity_Clear()
For i = 1 To 20
Sheet1.Cells(i, 2) = Null
Next
End Sub
Sub GetBottom(ByVal mbrName As Variant)
Call lstCity_Clear
Dim vt As Variant
Dim cbItems As Variant
Dim i As Integer
Dim pMember As String
vt = EssVGetMemberInfo(ThisWorkbook.Name & "Sheet1", mbrName, EssBottomLevel, False)
If IsArray(vt) Then
cbItems = UBound(vt) + 1
For i = 0 To UBound(vt)
Sheet1.Cells(i + 1, 2) = vt(i)
Next
Else
MsgBox ("Return Value = " + Str(vt))
End If
Cells(1, 2).Select
Set TopCell = Cells(1, ActiveCell.Column)
Set BottomCell = Cells(Rows.Count, ActiveCell.Column)
If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)
If TopCell.Row = Rows.Count And BottomCell.Row = 1 Then
ActiveCell.Select
Else
Range(TopCell, BottomCell).Select
End If
Sheet1.Shapes(3).ControlFormat.ListFillRange = Range(TopCell, BottomCell).Address
X = EssVFreeMemberInfo(vt)
End Sub
