Issue with NET_CONN

[Willi]

Hello,

we're on our way to upgrade from 9.5.2 to 10.2.2. Almost there. Now I have a Little Problem with one customer who wants an Login without Perspectives. we developed a small VBA-Macro to call "NET_CONN". The macro is shown below:

Code: Select all

Private Sub Workbook_Open()

    With Application
        .Calculation = xlManual
        .CalculateBeforeSave = False
    End With
     
    Application.Run ("NET_CONN")
    
    Application.Run "TM1REFRESH"
     
End Sub

BTW: EXCEL 2010 is used in both Versions.

This macro works with 9.5.2 as we expected. In 10.2.2 I have the issue that "NET_CONN" is called before the Add-In is loaded. I get the error-msg that NET_CONN is not found and than I see the Splash-Screen from the AddIn. How can I Change this?

Thanks and regards

[declanr]

Can you not just add a line to open the add in as the first step?

[rmackenzie]

I have a Little Problem with one customer

Sounds like they've got a little problem with you

If you don't know what the macro does then it's likely you've no business sticking it in the customers workbook!

[Willi]

Can you not just add a line to open the add in as the first step?

Sounds like a good idea. Thx.

I have a Little Problem with one customer

Sounds like they've got a little problem with you

If you don't know what the macro does then it's likely you've no business sticking it in the customers workbook!

Please read my post completly, take a deep breath, think, read again and perhaps write an answer after that.

[Willi]

Code: Select all

    .
    .
    .
    Application.AddIns("IBM Cognos TM1 Perspectives").Installed = False
    Application.AddIns("IBM Cognos TM1 Perspectives").Installed = True
    Application.Run ("NET_CONN")
    .
    .
    .

That did the trick

[tomok]

Code: Select all

    .
    .
    .
    Application.AddIns("IBM Cognos TM1 Perspectives").Installed = False
    Application.AddIns("IBM Cognos TM1 Perspectives").Installed = True
    Application.Run ("NET_CONN")
    .
    .
    .

That did the trick

Why are you unloading the add-in and adding it back. Why not just remove the Perspectives add-in from those that load automatically, then you don't need the Installed = False line? That would annoy the heck out of me as a user if the add-in loaded, then unloaded, then loaded again every time I used the workbook.

[Headon01]

This comes from an issue where Excel does not go through the startup procedures properly (always through automation). The addin will be shown as installed but really has not been opened at all. Setting the Installed property to True, when already True, will be ignored. You have to kick it into opening by setting the property to false and then setting it to true again. With our automation projects, with Perspectives 9.5.2, we use the following code to make sure that we can proceed with the macro:

Code: Select all

Const cRetries As Integer = 5

Function ScheduledRun() As String
    If Not IsTM1Loaded() Then
        For nI = 1 To cRetries
            LoadTm1AddIn
        
            If IsTM1Loaded() Then
                Exit For
            End If
        Next
    End If
    
    ScheduledRun = ""
    If nI > cRetries Then
        ScheduledRun = "Error: TM1 could not be loaded."
        Exit Function
    End If

    ' Proceed with login and rest of the macro

END Sub

Private Function IsTM1Loaded()
    Dim wbTM1AddIn As Workbook
    
    IsTM1Loaded = False
    
    On Error Resume Next
    Set wbTM1AddIn = Workbooks("tm1p.xla")
    On Error GoTo 0

    If Not wbTM1AddIn Is Nothing Then
        IsTM1Loaded = True
    End If

End Function

Private Sub LoadTm1AddIn()
    Dim oAddIn As AddIn

    Dim nNoOfAddIns As Integer
    Dim nI As Integer
    
    nNoOfAddIns = AddIns.Count
    For nI = 1 To nNoOfAddIns
        Set oAddIn = AddIns(nI)
        If oAddIn.Name = "tm1p.xla" Then
            On Error Resume Next
            oAddIn.Installed = False
            oAddIn.Installed = True
            On Error GoTo 0
            Exit Sub
        End If
    Next
    
    On Error Resume Next
    Application.Workbooks.Open Filename:="C:\Program Files\Cognos\TM1\bin\tm1p.xla"
    On Error GoTo 0

End Sub

Micheal