VBA Macro for N Connect

Post Reply
appleglaze28
Regular Participant
Posts: 269
Joined: Tue Apr 21, 2009 3:43 am
OLAP Product: Cognos TM1, Planning
Version: 9.1 SP3 9.4 MR1 FP1 9.5
Excel Version: 2003

VBA Macro for N Connect

Post by appleglaze28 »

I'm not a techie person but I'd like to ask if anybody can help me out with this.

How do you write the VBA script for N Connect VBA Code so I can log on to TM1 without going through the TM1 Perspective. With using 3 text box...1 for the Server, 1 for Username & 1 for Password Input.

Are there any pros & cons when taking advantage of this connection with Excel instead of loggin in through perspective?
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: VBA Macro for N Connect

Post by Alan Kirk »

appleglaze28 wrote:I'm not a techie person but I'd like to ask if anybody can help me out with this.

How do you write the VBA script for N Connect VBA Code so I can log on to TM1 without going through the TM1 Perspective. With using 3 text box...1 for the Server, 1 for Username & 1 for Password Input.

Are there any pros & cons when taking advantage of this connection with Excel instead of loggin in through perspective?
To run any TM1 worksheet function or macro from VBA, just use the Application.Run method of VBA. The first argument is the name of the function or macro, then each of the arguments is passed in turn.

Code: Select all

Application.Run("N_CONNECT", "MyServer", "MyLogin", "MyPassword")
The main disadvantage is that your password will be stored unencrypted. You should therefore make sure that you password protect the VBA project. (In the Visual Basic Editor, go to View -> Project Explorer, find the workbook, right click on it to select its properties, then go to the Protection tab, check Lock Project for Viewing and enter a password. That's not completely secure, but better than nothing.)
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
appleglaze28
Regular Participant
Posts: 269
Joined: Tue Apr 21, 2009 3:43 am
OLAP Product: Cognos TM1, Planning
Version: 9.1 SP3 9.4 MR1 FP1 9.5
Excel Version: 2003

Re: VBA Macro for N Connect

Post by appleglaze28 »

If I'm getting my value in a textbox, do I need to put double-quotation for it? Since I will be using the Textbox name, right?

Can anyone post a sample VBA script for creating a login prompt in Excel. I'm still studying VBA so I really need some help on this one. I'm not having any luck googling it up.

Code: Select all

Private Sub CancelButton_Click()
' CLEAR BOTH TEXT BOX[attachment=0]Run-time Error438.JPG[/attachment]
        TextName.Text = ""
        TextPassword.Text = ""
End Sub

Private Sub LoginButton_Click()
' ERROR MESSAGE TO APPEAR WHEN CLICKING LOGIN BUTTON WITH NO NAME
        If TextName.Text = "" Then
            MsgBox "Enter your Username/ UserID."
            Exit Sub
        End If
        
' ERROR MESSAGE TO APPEAR WHEN CLICKING LOGIN BUTTON WITH NO PASSWORD
        If TextPassword.Text = "" Then
            MsgBox "You must enter your Password"
            Exit Sub
         End If
        
' RUN TM1 FUNCTION FOR LOGIN
        Application.Workbooks("Sales Cube By Current Product.xls"). _
        Worksheets("MainMenu") = Run("N_CONNECT", "Nexus", "TextName", "TextPassword")
    
End Sub
Run-time Error438.JPG
Run-time Error438.JPG (10.05 KiB) Viewed 12303 times
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: VBA Macro for N Connect

Post by Wim Gielis »

Can you post your Excel file so that others can have a look at it?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
appleglaze28
Regular Participant
Posts: 269
Joined: Tue Apr 21, 2009 3:43 am
OLAP Product: Cognos TM1, Planning
Version: 9.1 SP3 9.4 MR1 FP1 9.5
Excel Version: 2003

Re: VBA Macro for N Connect

Post by appleglaze28 »

Well not much to post basically since I'm trying to figure out the log in via VB.

I just have 2 Textbox namely:
TextName for the Username
TextPassword for the Password

and 2 Command Button namely:
LoginButton for the Login Command
CancelButon for clearing out all characters in the Textbox.


I tried creating a CommandButton to disconnect to the Server. Base on what I read on theTM1 Reference. Since the error occurs on the N_DISCONNECT. Since I'm not yet able to make the N_Connect work...I'm not really sure either how to make the N_Disconnect work.

Code: Select all

Private Sub DisconnectButton_Click()
    Dim Ans As Integer
        Ans = MsgBox("Do you want to Disconnect from TM1?", vbYesNo)
    Select Case Ans
    Case vbYes
    Application.Run = ("N_DISCONNECT")
    Case vbNo
    End Select
End Sub
With N_DISCONNECT I get Run-Time Error 5: Invalid Procedure Call or Arguement.

I'm still kinda getting myself famliar with VBA.
Last edited by appleglaze28 on Thu Aug 06, 2009 8:49 am, edited 1 time in total.
TJMurphy
Posts: 74
Joined: Mon May 12, 2008 12:25 pm
OLAP Product: TM1
Version: PA 2.0.6 Local
Excel Version: Excel 2016

Re: VBA Macro for N Connect

Post by TJMurphy »

Should your code not simply be this:

Code: Select all

Application.Run("N_CONNECT", "Nexus", "TextName", "TextPassword")
(I think even the Application. bit is superfluous but I usually put it in). Not sure what the return value is but it's certainly not a worksheet ...

Tony
appleglaze28
Regular Participant
Posts: 269
Joined: Tue Apr 21, 2009 3:43 am
OLAP Product: Cognos TM1, Planning
Version: 9.1 SP3 9.4 MR1 FP1 9.5
Excel Version: 2003

Re: VBA Macro for N Connect

Post by appleglaze28 »

I still get the error if I copy what you wrote. I get a COMPILE ERROR: EXPECT: =

So I'm not totally sure how to make it work.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: VBA Macro for N Connect

Post by Wim Gielis »

Hi

You have to assign the result of the macro to e.g. a cell in Excel.

I usually have a function like this:

Code: Select all

Public Function ConnectExcelToTM1(Optional blnConnect As Boolean = True) As String
    If blnConnect Then
        Range("Connection").Value = Run("N_CONNECT", _
                                        Range("TM1Server").Text, _
                                        Range("User").Text, _
                                        Range("Password").Text)
    Else
        Range("Connection").Value = Run("N_DISCONNECT")
    End If
    ConnectExcelToTM1 = Range("Connection").Text
End Function
This function is called like this:

Code: Select all

Sub ConnectTM1()
    Dim s As String
    s = ConnectExcelToTM1(True)
End Sub
The disconnection would then send False as parameter. Additional error handling could be incorporated as you want / like /need.

The Excel cells (ranges) that I use have been given a clear name, rather than using cell references that could move when rows/columns are inserted/deleted.

Wim
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
appleglaze28
Regular Participant
Posts: 269
Joined: Tue Apr 21, 2009 3:43 am
OLAP Product: Cognos TM1, Planning
Version: 9.1 SP3 9.4 MR1 FP1 9.5
Excel Version: 2003

Re: VBA Macro for N Connect

Post by appleglaze28 »

Thanks Wim for that sample that you gave. I actually made it work if the users are directly for TM1. But when I tried it with my user security connected to Cognos BI, I get this 3 error on my 3 cell ranges Login Failed:SystemServerCAMSecurityRequired.

Have anyone tried creating a login through Excel with Cognos BI as ur user security connection? Although I'm not to fond of having basic things as login be incorporated in VBA since there's a add-in icon in excel. But just in case my users would like it that way.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: VBA Macro for N Connect

Post by Wim Gielis »

Sorry, I have no experience with Cognos BI.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
appleglaze28
Regular Participant
Posts: 269
Joined: Tue Apr 21, 2009 3:43 am
OLAP Product: Cognos TM1, Planning
Version: 9.1 SP3 9.4 MR1 FP1 9.5
Excel Version: 2003

Re: VBA Macro for N Connect

Post by appleglaze28 »

I can't seem to figure out which part the VBA script you gave me to add msg box to state whether you are able to login or not. Sincethis will allow me to add the command to move to a new sheet as well once I'm logged in. Am I missing out on anything?

Code: Select all

Public Function ConnectExcelToTM1(Optional blnConnect As Boolean = True) As String
    If blnConnect Then
        Range("Connection").Value = Run("N_CONNECT", _
                                        Range("TM1Server").Text, _
                                        Range("User").Text, _
                                        Range("Password").Text)
        MsgBox "Connected to TM1 Server"
    Else
         Range("Connection").Value = Run("N_DISCONNECT")
        MsgBox "Unable to Connect to Server"
    End If
    ConnectExcelToTM1 = Range("Connection").Text
     
End Function
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: VBA Macro for N Connect

Post by Alan Kirk »

appleglaze28 wrote:I can't seem to figure out which part the VBA script you gave me to add msg box to state whether you are able to login or not. Sincethis will allow me to add the command to move to a new sheet as well once I'm logged in. Am I missing out on anything?

Code: Select all

Public Function ConnectExcelToTM1(Optional blnConnect As Boolean = True) As String
    If blnConnect Then
        Range("Connection").Value = Run("N_CONNECT", _
                                        Range("TM1Server").Text, _
                                        Range("User").Text, _
                                        Range("Password").Text)
    Else
         Range("Connection").Value = Run("N_DISCONNECT")
        MsgBox "Unable to Connect to Server"
    End If
    ConnectExcelToTM1 = Range("Connection").Text
     MsgBox "Connected to TM1 Server"
End Function
For a start you may want to look at what N_Connect actually returns, which is:
If the connection is successful, N_CONNECT returns no value. If a connection cannot be established, server error messages are returned.
(My emphasis.)

In other words, the range named Connection is going to contain either nothing at all, or an error message.

Secondly I'm not sure that I agree with the block

Code: Select all

         
        Range("Connection").Value = Run("N_DISCONNECT")
        MsgBox "Unable to Connect to Server"
given that the code is executed if the argument blnConnect is False. In such a case, it actively disconnects from any servers, which is not at all the same thing as being "unable to connect" to them.

Thirdly you have the message

Code: Select all

MsgBox "Connected to TM1 Server"
outside of the If block, meaning that that message will be displayed regardless of whether the function was told to connect, to disconnect, or whether it connected correctly or not.

Accordingly the test that you do is whether the return value is an empty string or not. You can do this via a cell in the sheet as previously shown, or just via a variable:

Code: Select all

Function TM1Connect() As Boolean

Dim s_Result As String

On Error Resume Next

s_Result = Application.Run("N_CONNECT", _
 Range("TM1Server").Text, _
 Range("User").Text, _
 Range("Password").Text)

If s_Result = "" Then
    MsgBox "You are connected to TM1"
Else
    MsgBox "You failed to connect to TM1. Error: " & s_Result
End If

TM1Connect = (s_Result = "")

End Function
Last edited by Alan Kirk on Mon Aug 10, 2009 4:02 am, edited 1 time in total.
Reason: The "MsgBox "Unable to Connect to Server"" line wasn't part of Wim's original code, it was a modification of it. Apologies for any confusion, Wim.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Dccauley
Posts: 1
Joined: Wed Nov 10, 2010 9:20 pm
OLAP Product: TM1
Version: 9.4
Excel Version: Excel 2003

Re: VBA Macro for N Connect

Post by Dccauley »

I have successfully used the -- application.Run "N_CONNECT", "ProdServer" -- to connect. If someone tries to use but does not have access to the server, what is the easiest way to check for a successfull connection to "ProdServer"? I would like to dump a message box that the connection failed. I don't care why it failed.

Thanks in advance...
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: VBA Macro for N Connect

Post by Alan Kirk »

Dccauley wrote:I have successfully used the -- application.Run "N_CONNECT", "ProdServer" -- to connect. If someone tries to use but does not have access to the server, what is the easiest way to check for a successfull connection to "ProdServer"? I would like to dump a message box that the connection failed. I don't care why it failed.
Isn't this exactly what the code in my previous post in this thread does?
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Seraphim1
Posts: 1
Joined: Thu Dec 02, 2010 9:37 am
OLAP Product: TM1
Version: 9.4.1
Excel Version: 11.8328.8329 SP3

Re: VBA Macro for N Connect

Post by Seraphim1 »

Hello,
my problem is not the connecting or disconnecting. The command:
Testvalue = Application.Run("N_CONNECT", Serverbox.Text, Userbox.Text, Passwordbox.Text)
works.
My VBA-tool is semiautonomius, so the user connects with TM1 only, when he has to read or write data. Between those two situations the user has not to be connected.
The user can create sheets from the tool which athers can fill with values. This can be many sheets. After being filled this sheets are then written in my tool. When I do this, I have to quit TM1 as Excel tries then to recalculate every sheet.

I do this with the command:

Code: Select all

 On Error Resume Next
    Application.Run ("N_DISCONNECT")
    Workbooks("tm1pRibbonX.xlam").Close savechanges:=False
    Workbooks("tm1p.xla").Close savechanges:=False
    Application.ExecuteExcel4Macro "UNREGISTER(""" & "tm1.xll" & """)"
    On Error GoTo 0
When the sheets are then in the Tool and I will send them to TM1 I have to connect again. For this I have to start the TM1 addin again:

Code: Select all

Workbooks.Open(Filename:=TM1_File).RunAutoMacros Which:=xlAutoOpen
'TM1_File according to the windows version

Code: Select all

 Version = Application.OperatingSystem
    If Version = "Windows (32-bit) NT 6.00" Then
    TM1_File = "C:\Program Files\IBM\Cognos\TM1\bin\tm1p.xla"
    Else
    TM1_File = "C:\Program Files (x86)\IBM\cognos\tm1\bin\tm1p.xla"
    End If
by activating the workbooks.open there is a messagebox asking if I will quit all TM1-Servers, see attached file

After pushing the button twice, the makro works.

Question: How can I prevent the question about disconnecting?
Application.DisplayAlerts = False
does not work...

Best Regards

Matt
Attachments
err_TM1.jpg
err_TM1.jpg (8.7 KiB) Viewed 9343 times
Post Reply