Refresh Oracle Smart View Sheets Automatically with VBA

📎 This article includes 1 downloadable practice file ↓

Month-end packs often have fifteen Smart View sheets that all need refreshing in the same order. Clicking Refresh on each one is slow and easy to get wrong. Smart View ships with a set of VBA functions that let you do it from a macro.

Step 1: declare the Smart View functions

Smart View installs a file called smartview.bas in its bin folder (typically C:\Oracle\SmartView\bin). Import it into your VBA project (File → Import File) — it contains all the declarations. If you only need a few, these are enough:

Declare PtrSafe Function HypRetrieve Lib "HsAddin" (ByVal vtSheetName As Variant) As Long
Declare PtrSafe Function HypConnectionExists Lib "HsAddin" (ByVal vtFriendlyName As Variant) As Variant
Declare PtrSafe Function HypDisconnect Lib "HsAddin" (ByVal vtSheetName As Variant, ByVal bLogoutUser As Boolean) As Long

Step 2: refresh every sheet that has a connection

Sub RefreshAllSmartViewSheets()
    Dim ws As Worksheet, rc As Long, report As String
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            rc = HypRetrieve(ws.Name)
            report = report & ws.Name & ": " & IIf(rc = 0, "OK", "error " & rc) & vbLf
        End If
    Next ws
    Application.ScreenUpdating = True
    MsgBox report, vbInformation, "Smart View refresh"
End Sub

HypRetrieve returns 0 on success. Anything else is an error code — the most common is a sheet with no active connection. Collecting the results in a message tells you exactly which sheets need attention instead of failing silently.

Step 3: connect first (optional)

If users are already connected through the Smart View panel, retrieve just works. For scheduled or hand-off use you can connect in code with HypConnect, but never hard-code passwords in a workbook. Prompt for them, or rely on SSO where your environment supports it.

Tips from real month-ends

• Refresh POV/driver sheets first, then the report sheets that depend on them — order your loop explicitly if needed.
• Set Smart View options (suppress missing rows, zero rows) once per sheet; they are saved with the sheet.
• Add a timestamp cell (Range("A1").Value = "Refreshed " & Now) so readers know the numbers are current.

New to Smart View? Start with Smart View ad hoc analysis for beginners.