on 12-11-2014 10:11 AM
Hello,
I need help in order to read data from array within SAP
I normally deal with tables in SAP where I have session id for each position that can easily be defined by playing with numbers in bracket .For example:
Session.findById("wnd[0]/usr/tblRSTXTCATTABLE_CONTROL/txtSELECTIONS-TDNAME[0,0]").Text
Now I came across what turns out to be an array. When trying to get position, only thing is I get a general shell link and nothing else, regardless to which element i interact. I get following:
session.findById("wnd[0]/usr/cntlORDER_CHECK_COND/shellcont/shell")
After investigating with property collector it seems this whole table seems to be an array. I can't find a way how to instruct my VBS script to read the data that is in the table. My goal is to extract all table data and copy to excel. I attach few screenshots for better understanding.
Any help is appreciated.
Hello.
This document from Stefan explain very good how to read a Shell table (rows ad columns).
http://scn.sap.com/docs/DOC-26251
This should give you some idea how to adopt technic of Stefan´s documentation for your requirement.
Br, Holger
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
EDIT: Problem was in "Rows". Needed to rename RowsX same for Columns, otherwise it conflicts with VBA language
Hi again,
I tried to adjust to VBA and run directly in excel, but I got not enough memory error. After debugging it looks like capturing all table rows is an issue. I do not know why as there are only 128 rows in my table, but I get error right after table read. And when I try to see number of rows, I get empty data, while column number is fine.
After I get error, script just process 3 first rows and stops, which doesn't make much sense to me. Here is sample I use. Any ideas?
Sub YVORD_text()
Dim SapGuiAuto
Dim Application
Dim Connection
Dim Session
Dim excelPath
Dim curRow
Dim curCol
Dim Top
Dim vanster
Dim objExcel
Dim workSheetCount
Dim currentWorkSheet
Dim usedColumnsCount
Dim usedRowsCount
Dim Cells
Dim Row
Dim Col
Dim matNo
Dim salesOrg
Dim distrCh
Dim plant
Dim itemCatGrp
Dim GRDays
Dim inputMessageText
Dim InputMessageType
Dim SaveMessageType
Dim SaveMessage
Dim Division
If Not IsObject(Application) Then
Set SapGuiAuto = GetObject("SAPGUISERVER")
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
If IsObject(WScript) Then
WScript.ConnectObject Session, "on"
WScript.ConnectObject Application, "on"
End If
On Error Resume Next
Session.findById("wnd[0]").maximize
Dim char_descr, char_value, posNo, char_descr_check, char_value_check, material_code, qty_no, qty_name, material_name, material_numb, item_cat, tabx, row_count, column_count, copy_column_count, copy_char_descr
Dim SoldPO, SoldDate, SoldType, SoldItem, SoldName, SoldMaterial, PurchPO, PurchDate, PurchType, PurchItem, PurchName, SO_item, SO_item2
'==== below write to excel ====
Dim xlApp, xlBook, xlSht
Dim filename, value1, value2, value3, value4
Dim order(0)
order(0) = Range("SOnumber")
SO_item = Range("SO_item")
'filename = "C:\Users\SERAPALISG\Desktop\Revolution\Automation scripts\Scripts\Random\text.xlsx"
Set xlApp = CreateObject("Excel.Application")
'Set xlBook = xlApp.Workbooks.Open(filename)
'Set xlSht = xlApp.ActiveSheet
xlApp.DisplayAlerts = False
Sheets("YVORDCHK").Select
Set xlSht = ActiveSheet
'write data into the spreadsheet
xlSht.Cells.Clear
'==== above write to excel ====
'==== below extract SO10 data ====
Session.sendCommand ("/nYVORDCHK_DISP")
Session.findById("wnd[0]/usr/ctxtP_VKORG").Text = "BL62"
Session.findById("wnd[0]/usr/ctxtP_VTWEG").Text = "10"
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]/usr/ctxtP_WERKS").Text = "" 'BL01, cant be empty
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]/usr/ctxtP_MSG").Text = "" 'SALES, can be empty
Session.findById("wnd[0]/usr/txtP_CHECK").Text = "" 'checkgroup
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]").sendVKey 8
posNo = 0
row_count = 1
column_count = 0
Dim field_name, status_select, field_name_check, status_numb
posNo = 0
'-Get rows and ColumnsX--------------------------------------------
Set Table = Session.findById("wnd[0]/usr/cntlORDER_CHECK_COND/shellcont/shell")
Rows = Table.RowCount() - 1
Cols = Table.ColumnCount() - 1
'-Get the technical title of all ColumnsX in the first line--
Set ColumnsX = Table.ColumnOrder()
For j = 0 To Cols
xlSht.Cells(posNo + 1, column_count + 1) = CStr(ColumnsX(j))
column_count = column_count + 1
Next
column_count = 0
'-Get the title of all ColumnsX in the second line-----------
For j = 0 To Cols
Set ColumnTitle = Table.GetColumnTitles(CStr(ColumnsX(j)))
xlSht.Cells(posNo + 2, column_count + 1) = CStr(ColumnTitle(0))
column_count = column_count + 1
Next
column_count = 0
For i = 0 To Rows
For j = 0 To Cols
xlSht.Cells(posNo + 3, column_count + 1) = Table.GetCellValue(i, CStr(ColumnsX(j)))
column_count = column_count + 1
Next
column_count = 0
posNo = posNo + 1
'-Each 32 lines actualize the grid------------------------
If i Mod 32 = 0 Then
table.SetCurrentCell i, CStr(ColumnsX(0))
table.firstVisibleRow = i
End If
Next
posNo = 0
Call format_1
'===border===
'lRow = .Range("A" & .Rows.Count).End(xlUp).Row
'lCol = .Cells(1, .ColumnsX.Count).End(xlToLeft).Column
'==== border======
'==== above extract SO10 data ====
'xlBook.Save
'xlBook.Close SaveChanges = True
'xlApp.Close
'xlApp.Quit
'Set xlApp = CreateObject("Excel.Application")
'Set xlBook = xlApp.Workbooks.Open(filename)
'Set xlSht = xlApp.ActiveSheet
'==== below create item copy ====
'===below save and clean ====
Sheets("Sheet1").Select
'xlBook.Save
'xlBook.Close SaveChanges = True
'xlApp.Close
'xlApp.Quit
'always deallocate after use...
Set xlSht = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
'==== above save and clean ====
End Sub
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.