Smart View Login Script…And Error Trapping

Wow!  It’s been a while since I last posted…guess that happens with summer.  I have a few posts lined up, many of which contain code samples.

This first one is what I use when logging into Essbase in Smart View VBA.  Note that this section will create the Private Connection needed for the login.  I have also included error trapping for Smart View VBA errors that resides in a separate function and can be used with other code you might write.

Option Explicit

Sub EssbaseQA()
  Dim QAProvider, QAProvURL, QAServer, AppName, DBName, QACommonName, QADescription As String
  Dim DBName, PlanType2, PlanType3, Username, Password As String
  Dim X, Y, Alias As Long

  Username = “username”
  Password = “password”
  Provider = “HYP_ESSBASE”
  QAProvURL = “http://APSServer:13080/aps/APS”
  QAServer = “EssbaseServer”
  AppName = “Application”
  DBName = “Database”
  QACommonName = “PrivateConnectionName”
  QADescription = “PrivateConnectionDescription”

  X = HypConnect(Empty, Username, Password, QACommonName)
  If X <> 0 Then
    ‘Check if Private Connection exists
    If HypConnectionExists(QACommonName) = False Then
      ‘Create Private Connection
      Y = HypCreateConnection(Empty, Username, Password, Provider, QAProvURL, QAServer, AppName, DBName, QACommonName, QADescription)
      If Y <> 0 Then
        Application.ScreenUpdating = True
        SVError = SmartViewEC(Y)
        Exit Sub
      Else
        ‘Connect to cube
        X = HypConnect(Empty, Username, Password, QACommonName)
        If X <> 0 Then
          Application.ScreenUpdating = True
          SVError = SmartViewEC(X)
          Exit Sub
        Else
          ‘Here is where you will do your retrieves, etc…if you have any
          ‘Set Alias table to Default
          Alias = HypSetAliasTable(Empty, “Default”)
          ‘Retrieve data on worksheet
          Y = HypRetrieveRange(“EssbaseRet”, “Ret_Area”, Empty)
          If Y <> 0 Then
            Application.ScreenUpdating = True
            SVError = SmartViewEC(Y)
            Exit Sub
          End If
        End If
      End If
    End If
  Else
    ‘Here is where you will do your retrieves, etc…if you have any
    Y = HypRetrieveRange(“EssbaseRet”, “Ret_Area”, Empty)
    If Y <> 0 Then
      Application.ScreenUpdating = True
      SVError = SmartViewEC(Y)
      Exit Sub
    End If
  End If
End Sub

Public Function SmartViewEC(SVError As Variant)
  ‘Utility written by Sarah Zumbrum; sczumbrum@gmail.com
  If SVError = 4 Then
    MsgBox (“Smart View Error: SS_ERR_ERROR” & vbCrLf & vbCrLf & “Description: An error specific to the data provider or a generic error that cannot be mapped to a value.”)
  ElseIf SVError = 2 Then
    MsgBox (“Smart View Error: SS_NO_GRID_ON_SHEET_BUT_FUNCTIONS_SUBMITTED” & vbCrLf & vbCrLf & “Description: The value returned when a function sheet without a grid is submitted.”)
  ElseIf SVError = 1 Then
    MsgBox (“Smart View Error: SS_SHEET_NOT_CONNECTED_BUT_FUNCTIONS_SUBMITTED” & vbCrLf & vbCrLf & “Description: The value returned when a function sheet that is not connected is submitted.”)
  ElseIf SVError = 0 Then
    MsgBox (“Smart View Error: SS_OK” & vbCrLf & vbCrLf & “Description: The function ran successfully.”)
  ElseIf SVError = -1 Then
    MsgBox (“Smart View Error: SS_INIT_ERR” & vbCrLf & vbCrLf & “Description: Initialization error.”)
  ElseIf SVError = -2 Then
    MsgBox (“Smart View Error: SS_TERM_ERR” & vbCrLf & vbCrLf & “Description: Termination error.”)
  ElseIf SVError = -3 Then
    MsgBox (“Smart View Error: SS_NOT_INIT” & vbCrLf & vbCrLf & “Description: Initialization error.”)
  ElseIf SVError = -4 Then
    MsgBox (“Smart View Error: SS_NOT_CONNECTED” & vbCrLf & vbCrLf & “Description: The spreadsheet is not yet connected to the server.”)
  ElseIf SVError = -5 Then
    MsgBox (“Smart View Error: SS_NOT_LOCKED” & vbCrLf & vbCrLf & “Description: The spreadsheet is not locked.”)
  ElseIf SVError = -6 Then
    MsgBox (“Smart View Error: SS_INVALID_SSTABLE” & vbCrLf & vbCrLf & “Description: The spreadsheet has become unstable.”)
  ElseIf SVError = -7 Then
    MsgBox (“Smart View Error: SS_INVALID_SSDATA” & vbCrLf & vbCrLf & “Description: The spreadsheet contains invalid data.”)
  ElseIf SVError = -8 Then
    MsgBox (“Smart View Error: SS_NOUNDO_INFO” & vbCrLf & vbCrLf & “Description: No Undo information exists.”)
  ElseIf SVError = -9 Then
    MsgBox (“Smart View Error: SS_CANCELED” & vbCrLf & vbCrLf & “Description: Operation has been canceled.”)
  ElseIf SVError = -10 Then
    MsgBox (“Smart View Error: SS_GLOBALOPTS” & vbCrLf & vbCrLf & “Description: Not used.”)
  ElseIf SVError = -11 Then
    MsgBox (“Smart View Error: SS_SHEETOPTS” & vbCrLf & vbCrLf & “Description: Not used.”)
  ElseIf SVError = -12 Then
    MsgBox (“Smart View Error: SS_NOTENABLED” & vbCrLf & vbCrLf & “Description: Undo is not enabled.”)
  ElseIf SVError = -13 Then
    MsgBox (“Smart View Error: SS_NO_MEMORY” & vbCrLf & vbCrLf & “Description: Not enough memory resources are available.”)
  ElseIf SVError = -14 Then
    MsgBox (“Smart View Error: SS_DIALOG_ERROR” & vbCrLf & vbCrLf & “Description: Appropriate dialog box could not be displayed.”)
  ElseIf SVError = -15 Then
    MsgBox (“Smart View Error: SS_INVALID_PARAM” & vbCrLf & vbCrLf & “Description: Function contains an invalid parameter.”)
  ElseIf SVError = -16 Then
    MsgBox (“Smart View Error: SS_CALCULATING” & vbCrLf & vbCrLf & “Description: Calculation is in progress.”)
  ElseIf SVError = -17 Then
    MsgBox (“Smart View Error: SS_SQL_IN_PROGRESS” & vbCrLf & vbCrLf & “Description: Obsolete setting.”)
  ElseIf SVError = -18 Then
    MsgBox (“Smart View Error: SS_FORMULAPRESERVE” & vbCrLf & vbCrLf & “Description: Operation is not allowed because the spreadsheet is in formula preservation mode.”)
  ElseIf SVError = -19 Then
    MsgBox (“Smart View Error: SS_INTERNALSSERROR” & vbCrLf & vbCrLf & “Description: Operation cannot take place on the specified sheet.”)
  ElseIf SVError = -20 Then
    MsgBox (“Smart View Error: SS_INVALID_SHEET” & vbCrLf & vbCrLf & “Description: Current sheet cannot be determined.”)
  ElseIf SVError = -21 Then
    MsgBox (“Smart View Error: SS_NOACTIVESHEET” & vbCrLf & vbCrLf & “Description: Spreadsheet name as not specified and no active sheet is selected.”)
  ElseIf SVError = -22 Then
    MsgBox (“Smart View Error: SS_NOTCALCULATING” & vbCrLf & vbCrLf & “Description: Calculation cannot be canceled because no calculation is running.”)
  ElseIf SVError = -23 Then
    MsgBox (“Smart View Error: SS_INVALID SELECTION” & vbCrLf & vbCrLf & “Description: Selection parameter is invalid.”)
  ElseIf SVError = -24 Then
    MsgBox (“Smart View Error: SS_INVALIDTOKEN” & vbCrLf & vbCrLf & “Description: Not used.”)
  ElseIf SVError = -25 Then
    MsgBox (“Smart View Error: SS_CASCADENOTALLOWED” & vbCrLf & vbCrLf & “Description: Cascade list file cannot be created, or you are attempting to cascade while the spreadsheet is embedded in another document.”)
  ElseIf SVError = -26 Then
    MsgBox (“Smart View Error: SS_NOMACROS” & vbCrLf & vbCrLf & “Description: Spreadsheet macros cannot be run due to a licensing agreement.”)
  ElseIf SVError = -27 Then
    MsgBox (“Smart View Error: SS_NOREADONLYMACROS” & vbCrLf & vbCrLf & “Description: Spreadsheet macros which update the database cannot be run due to a licensing constraint.”)
  ElseIf SVError = -28 Then
    MsgBox (“Smart View Error: SS_READONLYSS” & vbCrLf & vbCrLf & “Description: You have a read-only license and cannot update the database.”)
  ElseIf SVError = -29 Then
    MsgBox (“Smart View Error: SS_NOSQLACCESS” & vbCrLf & vbCrLf & “Description: Obsolete setting.”)
  ElseIf SVError = -30 Then
    MsgBox (“Smart View Error: SS_MENUALREADYREMOVED” & vbCrLf & vbCrLf & “Description: The menu is removed already.”)
  ElseIf SVError = -31 Then
    MsgBox (“Smart View Error: SS_MENUALREADYADDED” & vbCrLf & vbCrLf & “Description: The menu is added already.”)
  ElseIf SVError = -32 Then
    MsgBox (“Smart View Error: SS_NOSPREADSHEETACCESS” & vbCrLf & vbCrLf & “Description: Not used.”)
  ElseIf SVError = -33 Then
    MsgBox (“Smart View Error: SS_NOHANDLES” & vbCrLf & vbCrLf & “Description: Not used.”)
  ElseIf SVError = -34 Then
    MsgBox (“Smart View Error: SS_NOPREVCONNECTION” & vbCrLf & vbCrLf & “Description: Not used.”)
  ElseIf SVError = -35 Then
    MsgBox (“Smart View Error: SS_LROERROR” & vbCrLf & vbCrLf & “Description: Not used.”)
  ElseIf SVError = -36 Then
    MsgBox (“Smart View Error: SS_LROWINAPPACCESSERR” & vbCrLf & vbCrLf & “Description: Not used.”)
  ElseIf SVError = -37 Then
    MsgBox (“Smart View Error: SS_DATANAVINITERR” & vbCrLf & vbCrLf & “Description: Not used.”)
  ElseIf SVError = -38 Then
    MsgBox (“Smart View Error: SS_PARAMSETNOTALLOWED” & vbCrLf & vbCrLf & “Description: Not used.”)
  ElseIf SVError = -39 Then
    MsgBox (“Smart View Error: SS_SHEET_PROTECTED” & vbCrLf & vbCrLf & “Description: The specified worksheet is protected. Unprotect the worksheet and try the operation again.”)
  ElseIf SVError = -40 Then
    MsgBox (“Smart View Error: SS_CALCSCRIPT_NOTFOUND” & vbCrLf & vbCrLf & “Description: Calc script not found.”)
  ElseIf SVError = -41 Then
    MsgBox (“Smart View Error: SS_NOSUPPORT_PROVIDER” & vbCrLf & vbCrLf & “Description: Provider not supported.”)
  ElseIf SVError = -42 Then
    MsgBox (“Smart View Error: SS_INVALID_ALIAS” & vbCrLf & vbCrLf & “Description: Invalid alias.”)
  ElseIf SVError = -43 Then
    MsgBox (“Smart View Error: SS_CONN_NOT_FOUND” & vbCrLf & vbCrLf & “Description: Connection not found.”)
  ElseIf SVError = -44 Then
    MsgBox (“Smart View Error: SS_APS_CONN_NOT_FOUND” & vbCrLf & vbCrLf & “Description: Provider Services connection not found.”)
  ElseIf SVError = -45 Then
    MsgBox (“Smart View Error: SS_APS_NOT_CONNECTED” & vbCrLf & vbCrLf & “Description: Provider Services not connected.”)
  ElseIf SVError = -46 Then
    MsgBox (“Smart View Error: SS_APS_CANT_CONNECT” & vbCrLf & vbCrLf & “Description: Provider Services cannot connect.”)
  ElseIf SVError = -47 Then
    MsgBox (“Smart View Error: SS_CONN_ALREADY_EXISTS” & vbCrLf & vbCrLf & “Description: Connection already exists.”)
  ElseIf SVError = -48 Then
    MsgBox (“Smart View Error: SS_APS_URL_NOT_SAVED” & vbCrLf & vbCrLf & “Description: Provider Services URL not saved.”)
  ElseIf SVError = -49 Then
    MsgBox (“Smart View Error: SS_MIGRATION_OF_CONN_NOT_ALLOWED” & vbCrLf & vbCrLf & “Description: Migration of connection not allowed.”)
  ElseIf SVError = -50 Then
    MsgBox (“Smart View Error: SS_CONN_MGR_NOT_INITIALIZED” & vbCrLf & vbCrLf & “Description: Connection manager not initialized.”)
  ElseIf SVError = -51 Then
    MsgBox (“Smart View Error: SS_FAILED_TO_GET_APS_OVERRIDE_PROPERTY” & vbCrLf & vbCrLf & “Description: Failed to get Provider Services override property.”)
  ElseIf SVError = -52 Then
    MsgBox (“Smart View Error: SS_FAILED_TO_SET_APS_OVERRIDE_PROPERTY” & vbCrLf & vbCrLf & “Description: Failed to set Provider Services override property.”)
  ElseIf SVError = -53 Then
    MsgBox (“Smart View Error: SS_FAILED_TO_GET_APS_URL” & vbCrLf & vbCrLf & “Description: Failed to get Provider Services URL.”)
  ElseIf SVError = -54 Then
    MsgBox (“Smart View Error: SS_APS_DISCONNECT_FAILED” & vbCrLf & vbCrLf & “Description: Provider Services disconnect failed.”)
  ElseIf SVError = -55 Then
    MsgBox (“Smart View Error: SS_OPERATION_FAILED” & vbCrLf & vbCrLf & “Description: Operation failed.”)
  ElseIf SVError = -56 Then
    MsgBox (“Smart View Error: SS_CANNOT_ASSOCIATE_SHEET_WITH_CONNECTION” & vbCrLf & vbCrLf & “Description: Cannot associate sheet with connection.”)
  ElseIf SVError = -57 Then
    MsgBox (“Smart View Error: SS_REFRESH_SHEET_NEEDED” & vbCrLf & vbCrLf & “Description: Worksheet refresh needed.”)
  ElseIf SVError = -58 Then
    MsgBox (“Smart View Error: SS_NO_GRID_OBJECT_ON_SHEET” & vbCrLf & vbCrLf & “Description: No grid object on sheet.”)
  ElseIf SVError = -59 Then
    MsgBox (“Smart View Error: SS_NO_CONNECTION_ASSOCIATED” & vbCrLf & vbCrLf & “Description: No connection associated.”)
  ElseIf SVError = -60 Then
    MsgBox (“Smart View Error: SS_NON_DATA_CELL_PASSED” & vbCrLf & vbCrLf & “Description: Non-data cell passed.”)
  ElseIf SVError = -61 Then
    MsgBox (“Smart View Error: SS_DATA_CELL_IS_NOT_WRITABLE” & vbCrLf & vbCrLf & “Description: Data cell is not writable.”)
  ElseIf SVError = -62 Then
    MsgBox (“Smart View Error: SS_NO_SVC_CONTENT_ON_SHEET” & vbCrLf & vbCrLf & “Description: No Smart View content on sheet.”)
  ElseIf SVError = -63 Then
    MsgBox (“Smart View Error: SS_FAILED_TO_GET_OFFICE_OBJECT” & vbCrLf & vbCrLf & “Description: Failed to get Office object.”)
  ElseIf SVError = -64 Then
    MsgBox (“Smart View Error: SS_OP_FAILED_AS_CHART_IS_SELECTED” & vbCrLf & vbCrLf & “Description: Operation failed because chart is selected.”)
  ElseIf SVError = -65 Then
    MsgBox (“Smart View Error: SS_EXCEL_IN_EDIT_MODE” & vbCrLf & vbCrLf & “Description: Excel in edit mode.”)
  ElseIf SVError = -66 Then
    MsgBox (“Smart View Error: SS_SHEET_NON_SMARTVIEW_COMPATIBLE” & vbCrLf & vbCrLf & “Description: Sheet not compatible with Smart View.”)
  ElseIf SVError = -67 Then
    MsgBox (“Smart View Error: SS_APP_NOT_STANDALONE” & vbCrLf & vbCrLf & “Description: Application not stand alone.”)
  ElseIf SVError = -68 Then
    MsgBox (“Smart View Error: SS_SMART_VIEW_DISABLED” & vbCrLf & vbCrLf & “Description: Smart View is disabled.”)
  ElseIf SVError = -69 Then
    MsgBox (“Smart View Error: SS_VBA_DEPRECATED” & vbCrLf & vbCrLf & “Description: The function has been deprecated.”)
  ElseIf SVError = -70 Then
    MsgBox (“Smart View Error: SS_OPERATION_NOT_SUPPORTED_IN_MULTIGRID_MODE” & vbCrLf & vbCrLf & “Description: The operation is not supported in worksheets that are in multiple gride mode.”)
  ElseIf SVError = -71 Then
    MsgBox (“Smart View Error: SS_INVALID_MEMBER” & vbCrLf & vbCrLf & “Description: The member name is invalid. Used with HypGetMemberInformation.”)
  ElseIf SVError = -72 Then
    MsgBox (“Smart View Error: SS_NO_SV_NAME_RANGE” & vbCrLf & vbCrLf & “Description: No named ranges are available. Used with HypGetNameRangeList.”)
  ElseIf SVError = -73 Then
    MsgBox (“Smart View Error: SS_AMBIGUOUS_MENU” & vbCrLf & vbCrLf & “Description: The menu item is ambiguous and could not be resolved. Used with HypExecuteMenu.”)
  End If
End Function

4 comments

  1. Thank you. I was trying to use this code but the msgbox in Error handler shows red. Any idea why? I suspect it could be because of “& vbCrLf & vbCrLf &” which aren’t declared anywhere in the code.

    Also, could you please let me know what is the use of this: “Application.ScreenUpdating = True”? Thanks again.

    1. You will need to change the & to & for it work.

      The screen updating means that Excel will not show the different spreadsheet actions as it is going through the cube. The screen will appear frozen until the end of the script. (Makes the script run faster)

  2. Hello all,

    Could you please help me with the automatic connection in all worksheets in an excel file using Smartview? The old essbase had this option. I just connected in one sheet and automatically I would be connected in the whole Excel file.
    As I saw, Smartview does not work this way. I made a macro, but it does not work. I have no error on it, but it just does not work. It worked for 1 day and then just all of a sudden it stopped.
    I am desperate. I can not connect manually in every sheet otherwise I will go crazy. I am working with very large databases.

    Thanks you so much,

    (Previous to writing this macro I imported the .bas file to declare all functions as safe)

    Public Sub Connect()

    Dim wsSheet As Worksheet

    For Each wsSheet In ActiveWorkbook.Worksheets
    x = HypConnect(wsSheet.Name, “user”, “password”, “Connection”)
    Next wsSheet

    End Sub

    1. Hi Ramona,

      I would suggest going through the entire connection process (like on this blog post) to fully error trap why the HypConnect is not working. I’m not sure why it’s not working, but including the entire connect script (HypConnectionExists, HypCreateConnection, etc) and using the error traps should help. May seem overboard, but not everything SV is simple!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s