Enable connection to palo server via VBA

    Enable connection to palo server via VBA

    Hi,

    I have written a function which allows me to enable an existing but closed connection to a palo server.

    Source Code

    1. Private Function ConnectToPaloServer(server As String) As Boolean
    2. Dim retval As Variant
    3. Dim connectionStatus As Variant
    4. Dim connectionInfo As Variant
    5. Dim nextTry As Boolean
    6. Dim success As Boolean
    7. Dim obj As Jedox_Palo_XlAddin.IPaloEngineCom
    8. Set obj = New Jedox_Palo_XlAddin.ComInterface
    9. On Error GoTo ConnectToPaloServer_Error
    10. 'ggf. Verbindung auf "online" stellen
    11. retval = RegValueGet(HKEY_CURRENT_USER, RegKey & server & "\", "connected", connectionStatus)
    12. If connectionStatus <> "true" Then _
    13. retval = RegValueSet(HKEY_CURRENT_USER, RegKey & server & "\", "connected", "true")
    14. obj.ForceServerListUpdate
    15. On Error Resume Next
    16. Do
    17. success = False
    18. nextTry = False
    19. connectionInfo = obj.GetConnectionDataInfo(server)
    20. Select Case Err.Number
    21. Case 0:
    22. If connectionInfo(0) = "" Then
    23. success = True
    24. nextTry = False
    25. Else
    26. retval = MsgBox(connectionInfo(0), vbAbortRetryIgnore)
    27. nextTry = (retval = vbRetry)
    28. End If
    29. Case Else:
    30. retval = MsgBox("Anmeldung am Palo Server fehlgeschlagen.", vbAbortRetryIgnore)
    31. nextTry = (retval = vbRetry)
    32. End Select
    33. Err.Clear
    34. Loop While nextTry
    35. ConnectToPaloServer = success
    36. On Error GoTo 0
    37. Exit Function
    38. ConnectToPaloServer_Error:
    39. Debug.Print "Error " & Err.Number & " (" & Err.Description & ") in procedure ConnectToPaloServer of Modul PaloServerRegistration"
    40. ConnectToPaloServer = False
    41. End Function

    The trick is very simple. Jedox saves all informations about server connections in registry. You can manipulate every entry such as "connected". Afterwards you have to force palo to requery registry. This should do the the method ForeceServerListUpdate. But in some cases palo doesn't recongnize the changes. A Palo.Data formula ends in an error and modeller doesn't show the entry.

    Has anybody else notice such a behavior?

    Regards

    Andreas

    P.S.: As a christmas gift I provide you my whole VBA module with other usefull functions about palo connections :)
    Files
    Andreas Mueller
    FORECAST Unternehmensplanungen GmbH, Berlin, Germany
    www.forecast.de