Legal | Privacy
print Printer View e-mail E-mail this page Bookmark Bookmark
Smart View VBA OBE

Customizing Smart View Worksheets by Using the VBA Toolkit

Purpose

This tutorial covers customizing Smart View worksheets by using the Visual Basic for Applications (VBA) toolkit.

Time to Complete

Approximately 40 minutes

Topics

This tutorial covers the following topics:

Overview
Scenario
Prerequisites
Importing the Resources File
Adding VBA Procedures
Adding Macro Buttons
Summary
Related information

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

This tutorial introduces the VBA toolkit for Smart View. Smart View enables you to customize and automate common tasks by using VBA functions. The menu equivalents are Visual Basic functions that execute the Hyperion menu commands.

In this tutorial, you use the VBA toolkit to create buttons on a Smart View worksheet. The buttons connect to, and disconnect from, a data source.

Back to Topic List

 

Scenario

Your users have asked you, a Smart View administrator, to create an easy way for them to connect to, and disconnect from, their most common applications.

Back to Topic List

Prerequisites

Before starting this tutorial, you should:

1.

Have an existing connection to a data source in Smart View
2.

Know the user name and password of the user who connects to the data source

Back to Topic List

Importing the Resources File

Before you can use Visual Basic functions in an Excel project, you must declare them in a module. A declaration outlines the necessary elements of the function so that Visual Basic can run it. You can declare only those functions that you plan to use, or you can declare all functions.

In this topic, you declare all VBA functions by importing the smartview.bas file.

1.

In Excel, select Tools > Macro > Visual Basic Editor.

Visual Basic Editor opens in a separate window within Excel.

 

2.

Select File > Import File.
3.

Select c:\Hyperion\SmartView\Bin\smartview.bas, and click Open to import the file.

This file contains all Smart View VBA functions. By importing it, you are declaring all functions ready for use. You can also declare only those functions that you know you will use by deleting unused functions in the .BAS file.

 

Back to Topic List

Adding VBA Procedures

In this topic, you create a VBA module and add "Connect" and "Disconnect" procedures to the module.

1.

In VBA Editor, select Insert > Module.

 
2.

Select Insert > Procedure.

 
3.

In the Add Procedure dialog box, perform these actions:

  1. In the Name box, type ConnectTraining. (You can substitute the name of your application for "Training.")
  2. Under Type, select Sub.
  3. Click OK.

A sub procedure is created.

 
4.

Within the sub procedure, enter x=HypConnect("SheetName","Username","Password","Connection").

  • Sheetname is the name of the worksheet to operate on.
  • Username is the name of a valid user for the data source provider.
  • Password is the password for this user.
  • Connection is the connection name of the data source.

The Connect function is declared.

 
5.

Select Insert > Procedure.

 
6.

In the Add Procedure dialog box, perform these actions:

  1. In the Name box, type DisconnectTraining. (You can substitute the name of your application for "Training.")
  2. Under Type, select Sub.
  3. Click OK.

A sub procedure is created.

 
7.

Within the sub procedure, enter x=HypDisConnect("SheetName",True).

Sheetname is the name of the worksheet to operate on.

The Disconnect function is declared.

 
8.

Select File > Close and Return to Microsoft Excel.

 

Back to Topic

Adding Macro Buttons

In this topic, you create Connect and Disconnect buttons within Excel, and you assign them the VBA functions that you created.

1.

In Excel, select Hyperion > Connection Manager.

 
2.

If any connections are active, select the active connection and click Disconnect to close it. Click Close to close Connection Manager.

 
3.

Select View > Toolbars > Forms.

The Forms toolbar is displayed.
4.

Select the Button icon.
5.

Place the crosshairs in the grid, and drag to draw a button.

The Assign Macro dialog box is displayed.
6.

Select the ConnectTraining function, and click OK.

You are returned to the worksheet.
7.

Select the button text, and enter Connect to Training.

 
8.

Repeat steps 4, 5, and 6 to create another button, and assign the DisconnectTraining function to it.

 
9.

Select the button text, and enter Disconnect Training.

 
10.

Click the Connect to Training button.

 
11.

Select Hyperion > Active Connections to verify that you are connected to the data source.

 
12.

Click the Disconnect Training button.

 
13.

Select Hyperion > Active Connections to verify that you are disconnected from the data source.

You have completed the tutorial.

 

Back to Topic List

Summary

In this lesson, you've learned how to:

Import the Smartview.bas file to declare VBA functions
Create a VBA module and add VBA procedures
Assign VBA functions to buttons

Back to Topic List

Related Information

To learn more about VBA for Smart View, refer to the Hyperion Smart View for Office Release 9.3.1.0.0 User's Guide

To learn more about Smart View, refer to additional OBEs on the OTN Web site

Back to Topic List

Place the cursor over this icon to hide all screenshots.

 

 

 
E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy