Cognos Automation
Posted: Fri Oct 19, 2018 9:16 pm
Has anyone managed to get the Cognos Automation functionality to work in PAX. Either in VBA in Excel or outside of Excel with a VB script.
I am trying to have a file open, connect to two TM1 servers and refresh all the data in the workbook, unlink all the data and then save the workbook as a different filename. Basically I want to pre-calc a large workbook and paste the values and save to a different name.
I've looked at the sample code IBM provides but I cannot seem to get it to work.
Part of the issue I believe relates to the CognosOfficeAutomationObject.Logon object.
IBM's documentation does not make it clear what to put for the Namespace.
What I believe it is, or at least what returns true is the Friendly Name for the connection, a forward slash and the TM1 server name.
Here is the code I have so far. The issue appears that the data does not always refresh consistently and you are left with #Values, or RECALC_ values in the cells. Any help is greatly appreciated.
Dim wb As Workbook
Dim Current As Worksheet
Dim wbname As String
wbname = (ThisWorkbook.Sheets("Main").Range("Workbook").Value)
Set wb = Workbooks.Open(Filename:=wbname, UpdateLinks:=0)
wb.Activate
Dim overview, sidepane As Boolean
'Hides toolbar area above sheet
overview = CognosOfficeAutomationObject.OverviewVisible
CognosOfficeAutomationObject.OverviewVisible = False
result = CognosOfficeAutomationObject.Logon("http://10.5.194.90", "Admin", "Password", "Production/msh_prd")
result = CognosOfficeAutomationObject.Logon("http://10.5.194.90", "Admin", "Password", "Production/msh_copa")
'Hides side pane
sidepane = CognosOfficeAutomationObject.ApplicationPaneVisible
CognosOfficeAutomationObject.ApplicationPaneVisible = False
DoEvents
Application.ScreenUpdating = False
'Refresh all data in the workbook
CognosOfficeAutomationObject.RefreshAllData
'Set visibility back to initial state
CognosOfficeAutomationObject.OverviewVisible = overview
CognosOfficeAutomationObject.ApplicationPaneVisible = sidepane
Application.ScreenUpdating = True
CognosOfficeAutomationObject.Logoff
wb.SaveAs Filename:=ThisWorkbook.Sheets("Main").Range("Workbook_SaveAs").Value, AccessMode:=xlExclusive, ConflictResolution:=True
wb.Close
Set wb = Nothing
I am trying to have a file open, connect to two TM1 servers and refresh all the data in the workbook, unlink all the data and then save the workbook as a different filename. Basically I want to pre-calc a large workbook and paste the values and save to a different name.
I've looked at the sample code IBM provides but I cannot seem to get it to work.
Part of the issue I believe relates to the CognosOfficeAutomationObject.Logon object.
IBM's documentation does not make it clear what to put for the Namespace.
What I believe it is, or at least what returns true is the Friendly Name for the connection, a forward slash and the TM1 server name.
Here is the code I have so far. The issue appears that the data does not always refresh consistently and you are left with #Values, or RECALC_ values in the cells. Any help is greatly appreciated.
Dim wb As Workbook
Dim Current As Worksheet
Dim wbname As String
wbname = (ThisWorkbook.Sheets("Main").Range("Workbook").Value)
Set wb = Workbooks.Open(Filename:=wbname, UpdateLinks:=0)
wb.Activate
Dim overview, sidepane As Boolean
'Hides toolbar area above sheet
overview = CognosOfficeAutomationObject.OverviewVisible
CognosOfficeAutomationObject.OverviewVisible = False
result = CognosOfficeAutomationObject.Logon("http://10.5.194.90", "Admin", "Password", "Production/msh_prd")
result = CognosOfficeAutomationObject.Logon("http://10.5.194.90", "Admin", "Password", "Production/msh_copa")
'Hides side pane
sidepane = CognosOfficeAutomationObject.ApplicationPaneVisible
CognosOfficeAutomationObject.ApplicationPaneVisible = False
DoEvents
Application.ScreenUpdating = False
'Refresh all data in the workbook
CognosOfficeAutomationObject.RefreshAllData
'Set visibility back to initial state
CognosOfficeAutomationObject.OverviewVisible = overview
CognosOfficeAutomationObject.ApplicationPaneVisible = sidepane
Application.ScreenUpdating = True
CognosOfficeAutomationObject.Logoff
wb.SaveAs Filename:=ThisWorkbook.Sheets("Main").Range("Workbook_SaveAs").Value, AccessMode:=xlExclusive, ConflictResolution:=True
wb.Close
Set wb = Nothing