HypRetrieveRange

Data provider types: Essbase, Planning (ad hoc only), Oracle Planning and Budgeting Cloud (ad hoc only), Financial Management (ad hoc only), Hyperion Enterprise (ad hoc only)

Description

HypRetrieveRange() enables users to refresh a selected or named range of cells in a grid or worksheet. If the range specified for this function contains more rows or columns than the actual grid has, the additional rows and columns are treated as comments and are thus part of the grid.

HypRetrieveRange clears the Undo buffer, therefore the Undo operation cannot be used afterward.

Essbase only: Users can refresh selected continuous cell ranges from more than one grid in a multiple grid worksheet. HypRetrieveRange can also be used to create a multiple grid sheet, as shown in Example: Creating Multiple grid sheet (Essbase Only). Once the user runs HypRetrieveRange, the sheet becomes a multiple grid ad hoc sheet.

Syntax

HypRetrieveRange(vtSheetName,vtRange,vtFriendlyName)

ByVal vtSheetName As Variant

ByVal vtRange As Variant

ByVal vtFriendlyName As Variant

Parameters

vtSheetName: The name of worksheet on which to run the function. If vtSheetName is Null or Empty, the active worksheet is used.

vtRange: The continuous range to be refreshed. This range must contain one or more member cells as well as data cells. When connected to Essbase, continuous ranges from multiple grid in a worksheet are allowed. If vtRange is Null, the entire worksheet is refreshed, and GetUsedRange is used on the worksheet specified to get the range to be refreshed.

vtFriendlyName: The friendly name of the connection to be used to refresh the range. If set to Null, the active connection associated with the worksheet is used to refresh the range on that worksheet. If no connection is associated, an error is returned.

Return Value

Returns 0 if successful; otherwise, returns the appropriate error code.

Example

Public Declare Function HypRetrieveRange Lib "HsAddin" (ByVal vtSheetName As Variant, ByVal vtRange As Variant, ByVal vtFriendlyName As Variant) As Long
Worksheets("Sheet2").Names.Add name:="MyRange", RefersTo:="=$E$11:$F$28"
Sub Example_RetrieveRange
   Worksheets("Sheet1").Names.Add name:="MyRange", RefersTo:="=$E$11:$F$28"
   sts = HypRetrieveRange(Empty, range("E11:F28"), "Samp1")
      'retrieve by regular range
   sts = HypRetrieveRange(Empty, range("MyRange"), "Samp1")
      'retrieve by named range
End sub

Example: Creating Multiple grid sheet (Essbase Only)

This example assumed that the required data sources are already connected.

Sub Example_CreateMultiGrid
   'create sample-basic range grid
      sts = HypRetrieveRange(Empty, range("E11:F13"), "SampleBasic1")
   'create demo-basic range grid
      sts = HypRetrieveRange(Empty, range("E17:G20"), "DemoBasic1")
End Sub

Once the grids are created, HypRetrieveRange can be called to refresh selected ranges in grids in the sheet one at a time. Additionally, HypRetrieve can be used to refresh all grids in the sheet.