eNTIRE cODE =========== 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” <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 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 On Wednesday, August 17, 2016 at 9:54:00 AM UTC-7, Appi123 wrote: > > Hi, > I have eeror trapping in our VBA code. But All MsgBox rows are RED... > > 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.”) > > please help to resolve this error. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
