VBA Macro for N Connect

VBA Macro for N Connect

Postby appleglaze28 » Fri Jun 05, 2009 2:33 am

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?
appleglaze28
Regular Participant
 
Posts: 266
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

Postby Alan Kirk » Fri Jun 05, 2009 2:57 am

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.)
"It's only 'hubris' if I fail."
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
Alan Kirk
Site Admin
 
Posts: 4372
Joined: Sun May 11, 2008 2:30 am
Location: Sydney, Australia
OLAP Product: TM1
Version: 9.5.2 64 bit
Excel Version: XP to 2010

Re: VBA Macro for N Connect

Postby appleglaze28 » Thu Aug 06, 2009 6:25 am

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 2185 times
appleglaze28
Regular Participant
 
Posts: 266
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

Postby Wim Gielis » Thu Aug 06, 2009 7:15 am

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

Wim Gielis

Excel Most Valuable Professional, 2011, 2012, 2013
http://www.wimgielis.be ==> 79 TM1 articles and a lot of custom code
Newest blog article: Relative time dimensions
Wim Gielis
MVP
 
Posts: 725
Joined: Mon Dec 29, 2008 6:26 pm
Location: Brussels, Belgium
OLAP Product: TM1
Version: TM1 10.1.1
Excel Version: 2010

Re: VBA Macro for N Connect

Postby appleglaze28 » Thu Aug 06, 2009 7:22 am

Well not much to post basically
Code: Select all
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.
appleglaze28
Regular Participant
 
Posts: 266
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

Postby TJMurphy » Thu Aug 06, 2009 8:35 am

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
TJMurphy
 
Posts: 45
Joined: Mon May 12, 2008 12:25 pm
OLAP Product: TM1
Version: 9.5.1 32 bit
Excel Version: Excel 2007

Re: VBA Macro for N Connect

Postby appleglaze28 » Thu Aug 06, 2009 8:46 am

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.
appleglaze28
Regular Participant
 
Posts: 266
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

Postby Wim Gielis » Thu Aug 06, 2009 9:07 am

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

Excel Most Valuable Professional, 2011, 2012, 2013
http://www.wimgielis.be ==> 79 TM1 articles and a lot of custom code
Newest blog article: Relative time dimensions
Wim Gielis
MVP
 
Posts: 725
Joined: Mon Dec 29, 2008 6:26 pm
Location: Brussels, Belgium
OLAP Product: TM1
Version: TM1 10.1.1
Excel Version: 2010

Re: VBA Macro for N Connect

Postby appleglaze28 » Thu Aug 06, 2009 9:48 am

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.
appleglaze28
Regular Participant
 
Posts: 266
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

Postby Wim Gielis » Thu Aug 06, 2009 2:56 pm

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

Wim Gielis

Excel Most Valuable Professional, 2011, 2012, 2013
http://www.wimgielis.be ==> 79 TM1 articles and a lot of custom code
Newest blog article: Relative time dimensions
Wim Gielis
MVP
 
Posts: 725
Joined: Mon Dec 29, 2008 6:26 pm
Location: Brussels, Belgium
OLAP Product: TM1
Version: TM1 10.1.1
Excel Version: 2010

Re: VBA Macro for N Connect

Postby appleglaze28 » Mon Aug 10, 2009 3:29 am

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
appleglaze28
Regular Participant
 
Posts: 266
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

Postby Alan Kirk » Mon Aug 10, 2009 3:53 am

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.
"It's only 'hubris' if I fail."
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
Alan Kirk
Site Admin
 
Posts: 4372
Joined: Sun May 11, 2008 2:30 am
Location: Sydney, Australia
OLAP Product: TM1
Version: 9.5.2 64 bit
Excel Version: XP to 2010

Re: VBA Macro for N Connect

Postby Dccauley » Wed Nov 10, 2010 9:28 pm

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...
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

Postby Alan Kirk » Wed Nov 10, 2010 9:30 pm

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?
"It's only 'hubris' if I fail."
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
Alan Kirk
Site Admin
 
Posts: 4372
Joined: Sun May 11, 2008 2:30 am
Location: Sydney, Australia
OLAP Product: TM1
Version: 9.5.2 64 bit
Excel Version: XP to 2010


Return to Cognos TM1

Who is online

Users browsing this forum: Bing [Bot], Exabot [Bot], Google [Bot] and 5 guests

Loading