Hello all,
I am new to scripting with the recorder. I have successfully completed one repetitive task, and am looking to expand! The tcode I am using requires that I click Ctrl +y, then Ctrl +C, then paste into excel when I perform the task manually. Exporting the report does not provide the report in the format I need.
I don't know if anyone else uses ZCO30, but that's the tcode I'm using.
Here's the code I currently have I would like to take the values displayed on the last screen and paste them into excel:
Sub ZCO30_XPCATIEOUT()
Dim FromPeriod, ToPeriod, FiscalYear, ProfitCenterGroup As String
Dim I, J As Single
If Not IsObject(App) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set App = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
Set Connection = App.Children(0)
End If
If Not IsObject(session) Then
Set session = Connection.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject App, "on"
End If
ThisWorkbook.Activate
J = Cells(Rows.Count, "A").End(xlUp).Row
'For I = 2 To J
I = 2
FromPeriod = Cells(I, "A").Value
ToPeriod = Cells(I, "B").Value
FiscalYear = Cells(I, "C").Value
ProfitCenterGroup = Cells(I, "D").Value
session.findById("wnd[0]").resizeWorkingPane 128, 41, False
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nzco30"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtP_PERIOF").Text = FromPeriod
session.findById("wnd[0]/usr/ctxtP_PERIOT").Text = ToPeriod
session.findById("wnd[0]/usr/ctxtP_PROFIT").Text = ProfitCenterGroup
session.findById("wnd[0]/usr/ctxtS_VALUES-LOW").Text = ""
session.findById("wnd[0]/usr/chkCB_REIM").SetFocus
session.findById("wnd[0]/usr/chkCB_REIM").Selected = True
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").selectItem " 1", "C 2"
session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").ensureVisibleHorizontalItem " 1", "C 2"
session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").doubleClickItem " 1", "C 2"
session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").expandNode " 3"
session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").topNode = " 3"
session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").expandNode " 4"
session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").topNode = " 3"
session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").expandNode " 5"
session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").topNode = " 3"
session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").selectItem " 5", "C 2"
session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").ensureVisibleHorizontalItem " 5", "C 2"
session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").doubleClickItem " 5", "C 2"
session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").expandNode " 1"
session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").topNode = " 1"
session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").selectItem " 2", "C 1"
session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").ensureVisibleHorizontalItem " 2", "C 1"
Cells(I, "F").Value = session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").Text
'Next I
MsgBox "Done"
End Sub
I was trying to make the last line copy the value...but it only returns "SAP.TableTreeControl.1"
Cells(I, "F").Value = session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell").Text
Here's a picture of what I'm trying to export without the Actual column which would be displayed next to Profit Center (which are the numbers I'm trying to copy into Excel I left them out because it has sensitive data)
Any help would be greatly appreciated.
Thanks,
Danielle
Accepted Solutions (1)
Hello Danielle,
welcome in the Scripting Language forum.
You use an individual transaction ZCO30, so here a commented example how to read a column from a tree. I use the standard transaction code SE80, because they have a tree with columns too - look at the picture below.
'-Begin-----------------------------------------------------------------
If Not IsObject(application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
Set connection = application.Children(0)
End If
If Not IsObject(session) Then
Set session = connection.Children(0)
End If
Set Tree = session.findById("wnd[0]/shellcont/shell/shellcont[3]/shell/shellcont[2]/shell")
'Column 2 is Beschreibung, but the index starts with 0
'To get the correct column 2 minus 1
colName = Tree.GetColumnNames.Item("1")
Set col = Tree.GetColumnCol(colName)
'Get top node
topNode = CStr(Tree.TopNode)
'Counts all sub nodes of the top node
cntSubNodes = Tree.GetNodeChildrenCountByPath(topNode)
'Scan all sub nodes
For i = 1 To cntSubNodes
'The path of the subnodes is 1/1, 1/2 etc.
NodeName = Tree.GetNodeTextByPath(topNode & "/" & CStr(i))
'Search for the correct node name
If NodeName = "RFC-Services" Then
'Get the key of the node, index starts also with 0 therefore -1
Key = CLng(Tree.GetNodeKeyByPath(topNode & "/" & CStr(i))) - 1
'Get the description
Beschreibung = col.Item(Key)
MsgBox Beschreibung
End If
Next
'-End-------------------------------------------------------------------
As you can see the example delivers the content of the column Beschreibung of the node RFC-Services.
Additional questions are welcome.
Cheers
Stefan
Thank you stefan! This helped me get the first node into excel successfully!
I need to get the 5 nodes below it as well and I'm not sure how to get it to work?
Here's what I used to get first node:
Set Tree = session.findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell/shellcont[1]/shell")
'Column 2 is Profit Center, but the index starts with 0
'To get the correct columnt 2 minus 1
colName = Tree.GetColumnNames.Item("1")
Set col = Tree.GetColumnCol(colName)
'Get top node
topNode = CStr(Tree.topNode)
'Counts all sub nodes of the top node
cntSubNodes = Tree.GetNodeChildrenCountByPath(topNode)
'Scan all sub nodes
For I = 1 To cntSubNodes
'The path of the subnodes is 1/1, 1/2 etc.
NodeName = Tree.GetNodeTextByPath(topNode & "/" & CStr(I))
'Search for the correct node name
If NodeName = "Communications, Medi" Then
'Get the key of the node
Key = CLng(Tree.GetNodeKeyByPath(topNode & "/" & CStr(I))) - 1
'Get the description
ProfitCenter = col.Item(Key)
Cells(I, "F").Value = ProfitCenter
End If
Next
The remaining nodes would be:
If NodeName = "Financial Services" Then
If NodeName = "Health & Public Serv" Then
If NodeName = "Products" Then
If NodeName = "Resources" Then
If NodeName = "Other" Then
I tried making NodeName an array, but that didn't work and I tried reusing the code above but the Next is tripping me up?
Can anyone assist?
Thank you!
Danielle
Hello Danielle,
my suggested solution seems to be a little bit special for the transaction SE80 and I mixed the key of the node with the item position of the entry in the column. They are different, but in my example case they are coincidentally the same.
Here another approach, to read the column Kurztext from the subnodes of Anwendungs-Registry:
'-Begin---------------------------------------------------------------
If Not IsObject(application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
Set connection = application.Children(0)
End If
If Not IsObject(session) Then
Set session = connection.Children(0)
End If
Set Tree = _
session.findById("wnd[0]/shellcont/shell/shellcont[2]/shell")
colName = Tree.GetColumnNames.Item("2")
Set col = Tree.GetColumnCol(colName)
topNode = CStr(Tree.TopNode) : Key = topNode
'-Counter to get correct index of node------------------------------
cnt = 1 + Tree.GetNodeChildrenCountByPath(topNode)
'-Scan nodes on the first level-------------------------------------
For i = 1 To Tree.GetNodeChildrenCountByPath(topNode)
cnt = cnt + 1
nodeName = Tree.GetNodeTextByKey(Key)
nodePath = Tree.GetNodePathByKey(Key)
If nodeName = "Anwendungs-Registry" Then
'-Scan nodes on the second level--------------------------------
For j = 1 To Tree.GetNodeChildrenCount(Key)
cnt = cnt + 1
subNode = Tree.GetNodeKeyByPath(nodePath & "/" & CStr(j))
subNodeName = Tree.GetNodeTextByKey(subNode)
Select Case subNodeName
Case "S_AREA_GDMA"
MsgBox col.Item(cnt)
Case "S_AREA_RMS"
MsgBox col.Item(cnt)
End Select
Next
End If
If i < Tree.GetNodeChildrenCountByPath(topNode) Then
Key = Tree.GetNextNodeKey(Key)
End If
Next
'-End-----------------------------------------------------------------
Hint: The variable cnt works only in this special case, if I open some nodes it doesn't work correct.
I talk with a friend of mine this week about scanning the SAP tree control with SAP GUI Scripting, and we are both the opinion that this is one of the most toughest tasks.
Additional questions are welcome.
Let us know your results.
Cheers
Stefan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.