Automatic spreadsheet update, Cam level security
Posted: Tue Jan 21, 2014 2:16 am
Hi
We recently "downgraded" from TM1 9.5.2 to cognos express with xcelerator 10.1. (due to cheaper pricing model) Part of the change was to move to Cam level security.
Since moving to CAM level security we have lost the ability to use Excel VBA and "N_Connect" to TM1. I have searched various threads on this forum and had no luck in solving this. I am assuming the process we have previously adopted is a fairly common requirement, however implemented differently under CAM level security or best practice.
Our old update spreadsheet process was (ran daily, automatically):
1: run via Microsoft scheduler open Excel with TM1 addin to a specific spreadsheet. (No human intervention)
2: when excel opened this triggered Workbook_Open() - excel VBA
Problem step 3: the VBA then Logged onto TM1 via Application.Run "N_CONNECT". (I have unsuccessfully tried Application.Run ("NET_CONN") as well)
4: Macro Refereshed data, hardcodes and saves to a common location on our intranet for users to access
Step 3 above “N_Connect” does not work with Cam security. The old process suited our site well as data was made available daily in excel form on the intranet, excel master templates were easy to maintain if required, scheduled daily with no manual intervention.
I also have tried a TI process, ExecuteCommand(*.*) to kick off a batch file (open the excel file) but this executes on the client not the server so connecting is still an issue (if scheduled via a chore).
I would appreciate your thoughts on this problem, if there is no direct answer to the automatic log on issue, what work arounds / better practices have you employed to automatically update spreadsheets without a physical user having to do it.
Regards
JD
We recently "downgraded" from TM1 9.5.2 to cognos express with xcelerator 10.1. (due to cheaper pricing model) Part of the change was to move to Cam level security.
Since moving to CAM level security we have lost the ability to use Excel VBA and "N_Connect" to TM1. I have searched various threads on this forum and had no luck in solving this. I am assuming the process we have previously adopted is a fairly common requirement, however implemented differently under CAM level security or best practice.
Our old update spreadsheet process was (ran daily, automatically):
1: run via Microsoft scheduler open Excel with TM1 addin to a specific spreadsheet. (No human intervention)
2: when excel opened this triggered Workbook_Open() - excel VBA
Problem step 3: the VBA then Logged onto TM1 via Application.Run "N_CONNECT". (I have unsuccessfully tried Application.Run ("NET_CONN") as well)
4: Macro Refereshed data, hardcodes and saves to a common location on our intranet for users to access
Step 3 above “N_Connect” does not work with Cam security. The old process suited our site well as data was made available daily in excel form on the intranet, excel master templates were easy to maintain if required, scheduled daily with no manual intervention.
I also have tried a TI process, ExecuteCommand(*.*) to kick off a batch file (open the excel file) but this executes on the client not the server so connecting is still an issue (if scheduled via a chore).
I would appreciate your thoughts on this problem, if there is no direct answer to the automatic log on issue, what work arounds / better practices have you employed to automatically update spreadsheets without a physical user having to do it.
Regards
JD