Extracting array from SAP

[gediminas_rapalis]

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.

[holger_khn · Accepted solution]

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

[gediminas_rapalis · Accepted solution]

thanks Holger. Seems to be exactly what I was looking for

[gediminas_rapalis · Accepted solution]

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

[holger_khn · Accepted solution]

May you Need to DIM all used objects like Table, ColumnsX...aso