Database Reference
In-Depth Information
Table 10.1 (
Continued
)
Newer VBA F
unctions
Function
Description
hypgetnamerangeList
hypretrievenamerange
named ranges are one of the best features of Excel when it comes to
automation. These two new functions programmatically extend the
named range capability to Smart view. The new functionality allows
Smart view to work with multiple retrieve ranges on a single
worksheet. Because Smart view now allows you to retrieve a range
on the sheet, you can select the desired named range
programmatically and refresh its data.
here is an example that submits data, calculates a Calc Script,
retrieves a named range, runs a second Calc Script, and then
retrieves the corresponding named range.
Sub runForecasts()
'using Smart View to refresh named ranges in Excel
Dim svRetVal As Long
Dim isWaitForCalcToComplete As Boolean
isWaitForCalcToComplete = True
svRetVal = HypSubmitData()
svRetVal = HypExecuteCalcScript(Empty, "CurrFcst",
isWaitForCalcToComplete)
svRetVal = HypRetrieveNameRange(Empty, Range("ShortRangeFCST"))
svRetVal = HypExecuteCalcScript(Empty, "LongFcst",
isWaitForCalcToComplete)
svRetVal = HypRetrieveNameRange(Empty, Range("LongRangeFCST"))
End Sub
hypShowPov
If the Pov toolbar has ever caused you headaches, this function is
your pain relief. to hide the Pov toolbar, call hypShowPov(false).
to show the Pov toolbar, call hypShowPov(true).
Sub SVconnect(sUser, sPassword, sApp, sDb, sUrl, sServer, Optional
sSheet)
Dim svRetVal As Long
Dim isCreatedConnection As Boolean
Dim isConnected As Boolean
Dim sProvider
Dim sConnName
If IsMissing(sSheet) Then sSheet = DEFAULT_SHEETNAME
sConnName = sApp & "." & sDb
sProvider = HYP_ANALYTIC_SERVICES
isCreatedConnection = HypConnectionExists(sConnName)
If (isSVError(svRetVal)) Then
Call handleSVError(svRetVal, "HypConnectionExists",
"SVconnect")
Exit Sub
End If
If Not isCreatedConnection Then
svRetVal = HypCreateConnection(sSheet, sUser, sPassword,
sProvider, sUrl, sServer, sApp, sDb, sConnName, "")
