How To Use VBA Code With Oracle’s Smart View Add-in

By Chris Newman • Updated: 07/08/22 • 19 min read

How to control data retrievals with VBA code and Smart Views.

What Is The Oracle Smart View Add-in?

The Smart View Excel Add-in is a free Oracle tool that allows you to connect to its database formats and retrieve data directly in Microsoft Excel.

Smart View is compatible with the following Oracle Products (if you know others, let me know in the comments section):

Essbase
Hyperion Financial Management (HFM)
Hyperion Planning
Hyperion Reporting and Analysis

If you are looking to automate something with Smart View that is not covered in this guide, you can search through Oracle’s VBA Developers Guide for additional functions and documentation.

VBA Coding Prep

Determine If You Have Access To Smart View’s API

Before you get started, you’ll want to ensure you have access to all the Smart View functions (API). If you have the Smart View Add-in installed and running in Excel, you should be fine. You can verify your access to their functions by doing the following:

Open the Visual Basic Editor (VBE)
Open the Tools menu
Select References
In the Available References Listbox, ensure there is an Oracle SmartView reference available (this does not have to be checked)

If you see the Oracle SmartView reference in the dialog, you should be fine running any of the code in this article.

Coding For 32-Bit/64-Bit Excel

While most folks are running 64-bit Excel nowadays, there is still the risk that you or your end-users are running a version of 32-bit Excel. For this reason, we will declare the Smart View functions in both 32-bit and 64-bit.

Luckily, Oracle has set up all its functions to return a Long value, so converting the functions to 64-bit is quite simple. All you will need to do is add the word PtrSafe in between Declare and Function and the function will be 64-bit compatible.

You can use an If Statement to determine if the end-user is running 64-bit Excel (VBA7) or 32-bit Excel. Depending on the version will determine how the function needs to be declared in your VBA code.

You’ll also notice that depending on the bit version of Excel you are running, the non-compatible declaration code font color will turn red. This is completely fine and can be ignored.

If you are going to be using a lot of the Smart View functions in your VBA code, I recommend you declare all the functions within a single If Statement at the top of your code (or if you have a lot, you can declare them all in their own Module).

Here is an example:

Storing Your Username And Password

You will likely be creating code where you need to log into the Server. Most VBA coders immediately want to write their username and password directly into their VBA code. This is NOT a great practice!

There are a couple more secure/ideal ways you can go about storing your username/password outside of your VBA project and even your Excel file.

Store your credentials in your PC’s registry
Store your credentials in a text file and save it to your computer

While these solutions are not going to pass your IT department’s security rigor, it at least gives your credentials more protection and requires one to be logged into your computer in order to access this information. The worse thing you could do is accidentally email a file or save it to a shared drive with your username/password available for everyone to see.

Establishing Oracle Connections

Working with Smart View connections requires an understanding of all the names used to setup the database. The various names you will encounter include:

Server Name
Application Name
Database Name
Friendly Name (Private Connection Name)

You will typically be referencing the Friendly Name throughout your Smart View VBA coding, so ensure you have Private Connections established and use a sensible naming convention. The Friendly Name will always be the name after the hyphen symbol in the Smart View connection outline.

Connect To Existing Private Connection

If a private connection has already been created in your Smartview add-in, you can log in through Smart View before retrieving or submitting any data. This essentially mimics how you would typically set up a retrieve on your Excel spreadsheet.

The following code shows you how you can use the HypConnect function to log in and connect to a private connection. You will need to reference your private connection’s name (aka Friendly Name) within the VBA code.

A private connection’s Friendly Name is displayed in the Smart View pane after the database name (separated by a hyphen).

#If VBA7 Then
  Declare PtrSafe Function HypConnect Lib "HsAddin" (ByVal vtSheetName As Variant, ByVal vtUserName As Variant, _
    ByVal vtPassword As Variant, ByVal vtFriendlyName As Variant) As Long
#Else
  Declare Function HypConnect Lib "HsAddin" (ByVal vtSheetName As Variant, ByVal vtUserName As Variant, _
    ByVal vtPassword As Variant, ByVal vtFriendlyName As Variant) As Long
#End If

Sub SmartView_Connect()
'PURPOSE: Log-in to a Smart View connection
'SOURCE: www.TheSpreadsheetGuru.com

Dim Username As String
Dim Password As String
Dim FriendlyName As String
Dim x As Integer

'Connection Inputs
  Username = "JohnDoe"
  Password = "Password123"
  FriendlyName = "Forecast Cube"

'Log-in through Smartview to desired database cube
   x = HypConnect(Empty, Username, Password, FriendlyName)
  
'Verify If Connection was Established
  If x = 0 Then
     MsgBox ("Connection Established")
  Else
     MsgBox ("Connection Failed")
  End If
  
End Sub

Setup Multi-Grid Sheet (Multiple Connections, Same Sheet)

If you are wanting to retrieve data from multiple databases on the same sheet, you can do so by setting up a multi-grid ad hoc retrieval.

This will require connecting to each database individually and creating a range grid for each connection. You will need to reference each database’s Friendly Name while using both the HypConnect and HypCreateRangeGrid Smart View functions.

#If VBA7 Then
  Declare PtrSafe Function HypConnect Lib "HsAddin" (ByVal vtSheetName As Variant, ByVal vtUserName As Variant, _
    ByVal vtPassword As Variant, ByVal vtFriendlyName As Variant) As Long
  Declare PtrSafe Function HypCreateRangeGrid Lib "HsAddin" (ByVal vtSheetName As Variant, _
  ByVal vtRange As Variant, ByVal vtFriendlyName As Variant) As Long
#Else
  Declare Function HypConnect Lib "HsAddin" (ByVal vtSheetName As Variant, ByVal vtUserName As Variant, _
    ByVal vtPassword As Variant, ByVal vtFriendlyName As Variant) As Long
   Declare Function HypCreateRangeGrid Lib "HsAddin" (ByVal vtSheetName As Variant, _
    ByVal vtRange As Variant, ByVal vtFriendlyName As Variant) As Long
#End If

Sub SmartView_MultigridConnect()
'PURPOSE: Create a Multi-Grid Retrieve
'SOURCE: www.TheSpreadsheetGuru.com

Dim Username As String
Dim Password As String
Dim FriendlyName As String
Dim x As Integer

'[1] Log-in through Smartview to desired database cube
  Username = "JohnDoe"
  Password = "Password123"
  FriendlyName = "SalesCube"
  
  x = HypConnect(Empty, Username, Password, FriendlyName)

'[1] Create Adhoc Retrieval Range
  x = HypCreateRangeGrid(Empty, Range("A1:C23"), FriendlyName)

'[2] Log-in through Smartview to desired database cube
  Username = "JohnDoe"
  Password = "Password123"
  FriendlyName = "CustomerCube"
  
  x = HypConnect(Empty, Username, Password, FriendlyName)

'[2] Create Adhoc Retrieval Range
  x = HypCreateRangeGrid(Empty, Range("G17:I39"), FriendlyName)

End Sub

Remove A Connection

You can use the HypDisconnect function to disconnect from a connection on a specific sheet.

#If VBA7 Then
  Declare PtrSafe Function HypDisconnect Lib "HsAddin" (ByVal vtSheetName As Variant, ByVal bLogoutUser As Boolean) As Long
#Else
  Declare Function HypDisconnect Lib "HsAddin" (ByVal vtSheetName As Variant, ByVal bLogoutUser As Boolean) As Long
#End If

Sub SmartView_Disconnect()
'PURPOSE: Disconnect a Smart View connection
'SOURCE: www.TheSpreadsheetGuru.com

Dim x As Integer

'Disconnect from designated sheet (Empty = ActiveSheet)
   x = HypDisconnect("Sheet 3")
   
End Sub

If you would like to disconnect all sheets in an Excel workbook, you can use the HypDisconnectAll function. This is useful if you would like to log in as a different user with your VBA code.

#If VBA7 Then
Declare PtrSafe Function HypDisconnectAll Lib “HsAddin” () As Long
#Else
Declare Function HypDisconnectAll Lib “HsAddin” () As Long
#End If

Sub SmartView_DisconnectAll()
‘PURPOSE: Equivalent to the Disconnect All button
‘SOURCE: www.TheSpreadsheetGuru.com

Dim x As Integer

‘Disconnect from all connections
x = HypDisconnectAll()

End Sub

Create A New Private Connection

If you need to create a brand new connection for an Oracle database that has not already been setup in your SmartView pane, you can do this with the HypCreateConnection function.

You will need to gather a handful of names/paths in order to setup the connection (you may need to work with your IT/BI admin to understand the naming conventions used)

Depending on which database type you are connecting to, you will need to use one of the following text values for your Provider Input:

For Essbase = “HYP_ESSBASE”
For HFM = “HYP_FINANCIAL_MANAGEMENT”
For Planning = “HYP_PLANNING”
For Hyperion Reporting and Analysis = “HYP_RA ”

#If VBA7 Then
  Declare PtrSafe Function HypCreateConnection Lib "HsAddin" (ByVal vtSheetName As Variant, _
    ByVal vtUserName As Variant, ByVal vtPassword As Variant, ByVal vtProvider As Variant, _
    ByVal vtProviderURL As Variant, ByVal vtServerName As Variant, ByVal vtApplicationName As Variant, _
    ByVal vtDatabaseName As Variant, ByVal vtFriendlyName As Variant, ByVal vtDescription As Variant) As Long

#Else
  Declare Function HypCreateConnection Lib "HsAddin" (ByVal vtSheetName As Variant, _
    ByVal vtUserName As Variant, ByVal vtPassword As Variant, ByVal vtProvider As Variant, _
    ByVal vtProviderURL As Variant, ByVal vtServerName As Variant, ByVal vtApplicationName As Variant, _
    ByVal vtDatabaseName As Variant, ByVal vtFriendlyName As Variant, ByVal vtDescription As Variant) As Long
#End If

Sub SmartView_CreateConnection()
'PURPOSE: Set-up A Connection via Smartview to desired cube
'SOURCE: www.TheSpreadsheetGuru.com

Dim Username As String
Dim Password As String
Dim Provider As String
Dim ProviderURL As String
Dim ServerName As String
Dim ApplicationName As String
Dim DatabaseName As String
Dim FriendlyName As String
Dim Description As String
Dim x As Integer

'Connection Inputs
  Username = "JohnDoe"
  Password = "Password123"
  Provider = "HYP_ESSBASE"
  ProviderURL = "https://hyperionp.mycompany.com:443/aps/SmartView"
  ServerName = "hypessretp.mycompany.com"
  ApplicationName = "Corporate"
  DatabaseName = "Financials"
  FriendlyName = "Forecast Cube"
  Description = "Essbase Cube for Financials"

'Create a connection through Smartview to desired database cube
  x = HypCreateConnection(Empty, Username, Password, Provider, ProviderURL, ServerName, _
         ApplicationName, DatabaseName, FriendlyName, Description)

'Verify If Connection was Established
  If x = 0 Then
     MsgBox ("Connection Created")
  Else
     MsgBox ("Connection Failed")
  End If

End Sub

Retrieving Data

Retrieve ActiveWorksheet

#If VBA7 Then
  Public Declare PtrSafe Function HypRetrieve Lib "HsAddin" (ByVal vtSheetName As Variant) As Long
#Else
  Public Declare Function HypRetrieve Lib "HsAddin" (ByVal vtSheetName As Variant) As Long
#End If

Sub SmartView_RetrieveActiveSheet()
'PURPOSE: Retrieve data in desired worksheet
'SOURCE: www.TheSpreadsheetGuru.com
'NOTES: If vSheetName is Null/Empty, the ActiveSheet will be used

Dim x As Integer

'Retrieve ActiveSheet
  x = HypRetrieve(Null)

'Verify Retrieve was successful
  If x = 0 Then
     MsgBox ("Retrieve was successful")
  Else
     MsgBox ("Retrieve failed")
  End If

End Sub

Retrieve A Specific Worksheet

#If VBA7 Then
  Public Declare PtrSafe Function HypRetrieve Lib "HsAddin" (ByVal vtSheetName As Variant) As Long
#Else
  Public Declare Function HypRetrieve Lib "HsAddin" (ByVal vtSheetName As Variant) As Long
#End If

Sub SmartView_RetrieveSheet()
'PURPOSE: Retrieve data in desired worksheet
'SOURCE: www.TheSpreadsheetGuru.com

Dim x As Integer

'Retrieve Sheet1 Tab
  x = HypRetrieve("Sheet1")

'Verify Retrieve was successful
  If x = 0 Then
     MsgBox ("Retrieve was successful")
  Else
     MsgBox ("Retrieve failed")
  End If

End Sub

Retrieve All Worksheets In A Workbook

#If VBA7 Then
  Public Declare PtrSafe Function HypRetrieve Lib "HsAddin" (ByVal vtSheetName As Variant) As Long
#Else
  Public Declare Function HypRetrieve Lib "HsAddin" (ByVal vtSheetName As Variant) As Long
#End If

Sub SmartView_RetrieveActiveWorkbook()
'PURPOSE: Retrieve data in sheets in ActiveWorkbook
'SOURCE: www.TheSpreadsheetGuru.com

Dim x As Integer
Dim sht As Worksheet

'Loop through all worksheets in ActiveWorkbook
  For Each sht In ActiveWorkbook.Worksheets
    
    'Retrieve Tab (if applicable)
      x = HypRetrieve(sht.Name)
  
  Next sht

'Notify User of Completion
  MsgBox ("All sheets have been retrieved in this workbook")

End Sub

Retrieve All Worksheets In Open Files

#If VBA7 Then
  Public Declare PtrSafe Function HypRetrieveAllWorkbooks Lib "HsAddin" () As Long
#Else
  Public Declare Function HypRetrieveAllWorkbooks Lib "HsAddin" () As Long
#End If

Sub SmartView_RetrieveAllWorkbooks()
'PURPOSE: Retrieve data in all Open Workbooks
'SOURCE: www.TheSpreadsheetGuru.com

Dim x As Integer

'Retrieve
  x = HypRetrieveAllWorkbooks()

'Verify Retrieve was successful
  If x = 0 Then
     MsgBox ("Retrieve was successful")
  Else
     MsgBox ("Retrieve failed")
  End If

End Sub

Submitting Data

The Smart View API gives you the ability to submit/send data back to the database you are connected to. In this section, we’ll explore a couple of different scenarios you might run into while building out your VBA automation.

Submit Data On Specific Sheet

#If VBA7 Then
  Declare PtrSafe Function HypSubmitData Lib "HsAddin" (ByVal vtSheetName As Variant) As Long
#Else
  Declare Function HypSubmitData Lib "HsAddin" (ByVal vtSheetName As Variant) As Long
#End If

Sub SmartView_SubmitData()
'PURPOSE: Submit data in desired worksheet
'SOURCE: www.TheSpreadsheetGuru.com

Dim x As Integer

'Submit Data on Sheet1 Tab
  x = HypSubmitData("Sheet1")

'Verify Retrieve was successful
  If x = 0 Then
     MsgBox ("Data Submission was successful")
  Else
     MsgBox ("Data Submission failed")
  End If
End Sub

Submit And Include Zeroes

In this example, we’ll submit data but want to ensure that the end-user can submit zero values. This setting can sometimes be turned off by default.

The HypSetOption function enables you to set either global (default) or sheet-specific options so that you do not need separate VBA commands for the two option types. Alternatively, you could utilize either the HypSetGlobalOption or HypSetSheetOption functions if you want to stick with a specific scope.

#If VBA7 Then
  Declare PtrSafe Function HypSubmitData Lib "HsAddin" (ByVal vtSheetName As Variant) As Long
  Public Declare PtrSafe Function HypSetOption Lib "HsAddin" (ByVal vtItem As Variant, ByVal vtOption As Variant, ByVal vtSheetName As Variant) As Long
#Else
  Declare Function HypSubmitData Lib "HsAddin" (ByVal vtSheetName As Variant) As Long
  Public Declare Function HypSetOption Lib "HsAddin" ( ByVal vtItem As Variant,ByVal vtOption As Variant, ByVal vtSheetName As Variant) As Long
#End If

Sub SmartView_SubmitDataZeroes()
'PURPOSE: Submit data in desired worksheet (including zeroes)
'SOURCE: www.TheSpreadsheetGuru.com

Dim x As Integer
Dim y As Integer

'Submit Data on Sheet1 Tab
  y = HypSetOption(HSV_SUBMITZERO, True, "") 'Global Setting
  x = HypSubmitData("Sheet1")

'Verify Retrieve was successful
  If x = 0 Then
     MsgBox ("Data Submission was successful")
  Else
     MsgBox ("Data Submission failed")
  End If
End Sub

Change Smart View Options

Many of the options that are available to you via the Smart View Options dialog are also available through their VBA API functions. Options can be set either at the Sheet Level or Globally.

Oracle provides a table for all the various option inputs that can be used within its HypSetOption function. Let’s look at a few examples of settings you may want to change using VBA code.

Remove Member Indentation

#If VBA7 Then
Declare PtrSafe Function HypSetOption Lib "HsAddin" (ByVal vtItem As Variant, _
  ByVal vtOption As Variant, ByVal vtSheetName As Variant) As Long
#Else
  Declare Function HypSetOption Lib "HsAddin" ( ByVal vtItem As Variant, _
    ByVal vtOption As Variant, ByVal vtSheetName As Variant) As Long
#End If

Sub SmartView_RemoveIndent()

Dim x As Integer

'Remove Indentations for Sheet2
  x = HypSetOption(HSV_INDENTATION, 0, "Sheet2")

End Sub

Suppress Rows With #Missing/Zeroes

#If VBA7 Then
Declare PtrSafe Function HypSetOption Lib "HsAddin" (ByVal vtItem As Variant, _
  ByVal vtOption As Variant, ByVal vtSheetName As Variant) As Long
#Else
  Declare Function HypSetOption Lib "HsAddin" ( ByVal vtItem As Variant, _
    ByVal vtOption As Variant, ByVal vtSheetName As Variant) As Long
#End If

Sub SmartView_Suppress()

Dim x As Integer

'Suppress Missing Rows Globally
  x = HypSetOption(HSV_SUPPRESSROWS_MISSING, True, "")
  
'Suppress Zero Rows Globally
  x = HypSetOption(HSV_SUPPRESSROWS_ZEROS, True, "")

End Sub

Retain Excel Formatting

#If VBA7 Then
Declare PtrSafe Function HypSetOption Lib "HsAddin" (ByVal vtItem As Variant, _
  ByVal vtOption As Variant, ByVal vtSheetName As Variant) As Long
#Else
  Declare Function HypSetOption Lib "HsAddin" ( ByVal vtItem As Variant, _
    ByVal vtOption As Variant, ByVal vtSheetName As Variant) As Long
#End If

Sub SmartView_RetainFormats()

Dim x As Integer

'Selects the Excel formatting check box
  x = HypSetOption(HSV_EXCEL_FORMATTING, True, "")

End Sub

Display Member Description Only

#If VBA7 Then
Declare PtrSafe Function HypSetOption Lib "HsAddin" (ByVal vtItem As Variant, _
  ByVal vtOption As Variant, ByVal vtSheetName As Variant) As Long
#Else
  Declare Function HypSetOption Lib "HsAddin" ( ByVal vtItem As Variant, _
    ByVal vtOption As Variant, ByVal vtSheetName As Variant) As Long
#End If

Sub SmartView_DisplayDescriptions()

Dim x As Integer

'Display Descriptions Only for Members
  x = HypSetOption(HSV_MEMBER_DISPLAY, 2, "Sheet2")

End Sub

Zoom In/Drilling Down

Zooming in is another Smart View functionality that is popular to automate with VBA.

HypZoomIn Function Inputs

vtSheetName – The name of the worksheet this function will be run on. If you set it to Null/Empty, the Active Sheet will be used.
vtSelection – The Cell/Range that refers to the member you wish to zoom in on. If you set it to Null/Empty, the Active Cell will be used.
vtLevel – Indicate the level of the zoom you want to carry out.
- 0 = Next Level
- 1 = All Levels
- 2 = Bottom level
- 3 = Siblings
- 4 = Same Level
- 5 = Same Generation
- 6 = Formulas
vtAcross – This input is no longer needed. Just make the value equal FALSE

Bottom Level Zoom-In

The following VBA code performs a bottom-level zoom on the member in Cell A2 on the worksheet named Sheet1.

#If VBA7 Then
  Public Declare PtrSafe Function HypZoomIn Lib "HsAddin" (ByVal vtSheetName As Variant, ByVal vtSelection As Variant, ByVal vtLevel As Variant, ByVal vtAcross As Variant) As Long
#Else
  Public Declare Function HypZoomIn Lib "HsAddin" (ByVal vtSheetName As Variant,  ByVal vtSelection As Variant, ByVal vtLevel As Variant, ByVal vtAcross As Variant) As Long
#End If

Sub SmartView_ZoomInBottomLevel()
'PURPOSE: Submit data in desired worksheet
'SOURCE: www.TheSpreadsheetGuru.com

Sub SmartView_ZoomInBottomLevel()
'PURPOSE: Submit data in desired worksheet
'SOURCE: www.TheSpreadsheetGuru.com

Dim x As Integer

'Perform Bottom-Level Drill in Column A
  x = HypZoomIn("Sheet1", Range("A2"), 1, False)

'Report If Ran Successfully
  If x = 0 Then
     MsgBox ("Zoom-in was successful!")
  Else
     MsgBox ("Zoom-in failed")
  End If

End Sub

Bottom Level Zoom-In & Suppress Zeroes/Missing Values

The following VBA code performs a bottom-level zoom on the member in Cell A2 on the worksheet named Sheet1. This time we will also change the Smart View Settings so that zero and missing values are suppressed.

#If VBA7 Then
  Public Declare PtrSafe Function HypZoomIn Lib "HsAddin" (ByVal vtSheetName As Variant, ByVal vtSelection As Variant, ByVal vtLevel As Variant, ByVal vtAcross As Variant) As Long
  Public Declare PtrSafe Function HypSetOption Lib "HsAddin" (ByVal vtItem As Variant, ByVal vtOption As Variant, ByVal vtSheetName As Variant) As Long
#Else
  Public Declare Function HypZoomIn Lib "HsAddin" (ByVal vtSheetName As Variant,  ByVal vtSelection As Variant, ByVal vtLevel As Variant, ByVal vtAcross As Variant) As Long
  Public Declare Function HypSetOption Lib "HsAddin" ( ByVal vtItem As Variant,ByVal vtOption As Variant, ByVal vtSheetName As Variant) As Long
#End If

Sub SmartView_ZoomInBottomLevel_Suppress()
'PURPOSE: Submit data in desired worksheet
'SOURCE: www.TheSpreadsheetGuru.com

Dim x As Integer
Dim y As Integer

'Ensure Options are set to Suppress Missing/Zero Rows
  y = HypSetOption(HSV_SUPPRESSROWS_MISSING, True, "Sheet1")
  y = HypSetOption(HSV_SUPPRESSROWS_ZEROS, True, "Sheet1")

'Perform Bottom-Level Drill in Column A
  x = HypZoomIn("Sheet1", Range("A2"), 1, False)

'Report If Ran Successfully
  If x = 0 Then
     MsgBox ("Zoom-in was successful!")
  Else
     MsgBox ("Zoom-in failed")
  End If

End Sub

I Hope This Helped!

Hopefully, I was able to explain how you can use the Smart View Excel Add-ins API functions to help you automate the retrieval of data from your Oracle data cubes. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Hidden Hacks For VBA Macro Coding

After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!

Learn These Hacks Now For Free!

Keep Learning

Explore more →

Add Line Breaks In Screentips & SuperTips (Ribbon XML)

Parts Of A Ribbon Button’s Tool Tip Programatically, there are two parts to a button’s tool tip box that appears...

Refresh Ribbon UI In Your Custom Excel Add-in

What This VBA Code Does There may be times when you need to refresh the user interface (UI) of your...

How To Create MSI Auto-Installers For VBA Add-ins

I’ve had quite a few IT administrators request that I provide an MSI Installer file (often referred to as a...

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.