Page 1 of 1
VBA Macro for N Connect
Posted: Fri Jun 05, 2009 2:33 am
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?
Re: VBA Macro for N Connect
Posted: Fri Jun 05, 2009 2:57 am
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.)
Re: VBA Macro for N Connect
Posted: Thu Aug 06, 2009 6:25 am
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 (10.05 KiB) Viewed 12719 times
Re: VBA Macro for N Connect
Posted: Thu Aug 06, 2009 7:15 am
by Wim Gielis
Can you post your Excel file so that others can have a look at it?
Re: VBA Macro for N Connect
Posted: Thu Aug 06, 2009 7:22 am
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.
Re: VBA Macro for N Connect
Posted: Thu Aug 06, 2009 8:35 am
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
Re: VBA Macro for N Connect
Posted: Thu Aug 06, 2009 8:46 am
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.
Re: VBA Macro for N Connect
Posted: Thu Aug 06, 2009 9:07 am
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
Re: VBA Macro for N Connect
Posted: Thu Aug 06, 2009 9:48 am
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.
Re: VBA Macro for N Connect
Posted: Thu Aug 06, 2009 2:56 pm
by Wim Gielis
Sorry, I have no experience with Cognos BI.
Re: VBA Macro for N Connect
Posted: Mon Aug 10, 2009 3:29 am
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
Re: VBA Macro for N Connect
Posted: Mon Aug 10, 2009 3:53 am
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
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
Re: VBA Macro for N Connect
Posted: Wed Nov 10, 2010 9:28 pm
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...
Re: VBA Macro for N Connect
Posted: Wed Nov 10, 2010 9:30 pm
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?
Re: VBA Macro for N Connect
Posted: Mon Nov 16, 2015 2:52 pm
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