By TopDown Team
January 29th, 2014
Smart View reports often rely heavily on the appropriate option settings. Luckily, you can automate them in two steps using VBA.
First, import the smartview.bas file:
- In Excel, navigate to Developer tab (if not visible, enable the Developer tab in Excel Options ) and select Visual Basic
- In Visual Basic explorer, select File \ Import File
- Navigate to oracle \ SmartView \ Bin \ smartview.bas
Second, create a VBA module:
- From Visual Basic explorer menu select Insert \ Module
- You can write text similar to that below or simply enter the same instructions for two distinct options profiles (In this text, the first section turns on suppression and zooms to the bottom. The second section turns off suppression and set zoom to next level.)
In the function HypSetSheetOption:
✓ Option 2 – enables ‘include selection’ setting
✓ Option 1 sets zoom level to 0 next level, 1 all levels or 2 bottom level
✓ Option 6 enables suppress missing setting
✓ Option 7 enables suppress zeros setting
NOTE: If you want to remind the user of which settings are active, you can add a message box.
To attach a shortcut key, navigate to View tab, click Macros button and select View Macros in pull-down. Click macro name and click Options button; then select shortcut key in “Macro Options” window.
See the Smart View User Guide under VBA functions for a listing of other Options settings.