VBA Macro for N Connect
-
- 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
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?
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?
- Alan Kirk
- Site Admin
- Posts: 6610
- 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
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.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?
Code: Select all
Application.Run("N_CONNECT", "MyServer", "MyLogin", "MyPassword")
"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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- 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
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.
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
-
- MVP
- Posts: 3128
- 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
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
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
-
- 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
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.
With N_DISCONNECT I get Run-Time Error 5: Invalid Procedure Call or Arguement.
I'm still kinda getting myself famliar with VBA.
Code: Select all
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
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.
-
- 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
Should your code not simply be this:
(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
Code: Select all
Application.Run("N_CONNECT", "Nexus", "TextName", "TextPassword")
Tony
-
- 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
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.
So I'm not totally sure how to make it work.
-
- MVP
- Posts: 3128
- 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
Hi
You have to assign the result of the macro to e.g. a cell in Excel.
I usually have a function like this:
This function is called like this:
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
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
Code: Select all
Sub ConnectTM1()
Dim s As String
s = ConnectExcelToTM1(True)
End Sub
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
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
-
- 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
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.
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.
-
- MVP
- Posts: 3128
- 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
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
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
-
- 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
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
- Alan Kirk
- Site Admin
- Posts: 6610
- 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
For a start you may want to look at what N_Connect actually returns, which is: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
(My emphasis.)If the connection is successful, N_CONNECT returns no value. If a connection cannot be established, server error messages are returned.
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"
Thirdly you have the message
Code: Select all
MsgBox "Connected to TM1 Server"
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.
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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- 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
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...
Thanks in advance...
- Alan Kirk
- Site Admin
- Posts: 6610
- 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
Isn't this exactly what the code in my previous post in this thread does?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.
"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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- 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
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:
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:
'TM1_File according to the windows version
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?
Best Regards
Matt
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
Code: Select all
Workbooks.Open(Filename:=TM1_File).RunAutoMacros Which:=xlAutoOpen
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
After pushing the button twice, the makro works.
Question: How can I prevent the question about disconnecting?
does not work...Application.DisplayAlerts = False
Best Regards
Matt
- Attachments
-
- err_TM1.jpg (8.7 KiB) Viewed 10136 times