Working with GuiTableControl in Excel vba

[daniel_mccollum]

Hi all,

I have a requirement to strip the contents of a GuiTableControl from SAP. The transaction KO03 doesn't have a download function enabled & some of the tables have many lines, making Ctrl+Y to copy'n'paste data manually very tedious & potentially error prone.

I have some code, where I count the total rows (.RowCount) visible rows (.VisibleRowCount), as well as the columns (.Columns.Count). Then I loop to populate an array of the now correct size with the contents of the cells, paging down once I hit the visible row limit & stopping at the total row limit. I then paste that into a spreadsheet. 

ultimately it works well enough for this specific example, but it currently requires that I specify the column ids in each loop. IE

1st column is tblSAPLKOBSTC_RULES/ctxtCOBRB-KONTY[0,0]

2nd column is tblSAPLKOBSTC_RULES/ctxtDKOBR-EMPGE[1,0]

etc

I'd like to make this snippet fairly reusable with minimal adjustment, is there a way to make this generic, so that I only need to loop over the array of displayed cells in the GuiTableControl without having to reference the individual ID's? Or maybe detect the cells ID's & have that as a variable at runtime?

Any insights gratefully received.

Cheers
Daniel

[script_man · Accepted solution]

Hi Daniel,

It could be that you find the answer at this link:

Regards,

ScriptMan

[daniel_mccollum · Accepted solution]

Thanks for the idea. I'll explore & report back

Cheers

Daniel

[daniel_mccollum · Accepted solution]

a lovely method to get all the relevant meta data from one declaration. Thanks

Dim tTableID As Object

Set tTableID = Session.findById("targetGuiTableControl") '

then I have all the visible information in tTableID

I assume there isn't a more elegant way to get everything not visible other than scrolling still?

[daniel_mccollum · Accepted solution]

so, now I have copy'n'pasted the previously linked code & adjusted a little bit. I have a list of inputs I loop over & generate a spread sheet of the scraped table contents for each.

the 'issue' Im having at this point, is where the inputs have a blank last cell on the first column, the scraping ends & goes to the next input. My code block is:

------Begin Code Snippet------

'Start processing data

    Do

    On Error GoTo ErrorHandler

     InternalOrder = Cells(nRow, 1)

Session.findById("wnd[0]/tbar[0]/okcd").text = "/nKO03"

Session.findById("wnd[0]").sendVKey 0

Session.findById("wnd[0]/usr/ctxtCOAS-AUFNR").text = InternalOrder

Session.findById("wnd[0]").sendVKey 0

Session.findById("wnd[0]/tbar[1]/btn[17]").press

myFile = vPath & vFile

mySheet = "Sheet1"

Set xclapp = CreateObject("Excel.Application")

Set xclwbk = xclapp.Workbooks.Open(myFile)

Set xclsht = xclwbk.Sheets(mySheet)

xclapp.Visible = True

xclapp.DisplayAlerts = False

k = 1

Do

Set tTableID = Session.findById(tGuiTableControl)

If k = 1 Then

  tDataColumns = tTableID.Columns.Count

  VisibleRows = tTableID.VisibleRowCount

  For j = 0 To tDataColumns - 1

    xclsht.Cells(k, j + 1).Value = tTableID.Columns.elementAt(j).Title

  Next

  k = k + 1

End If

For i = 0 To VisibleRows - 1

l = 1

  For j = 0 To tDataColumns - 1

    On Error Resume Next

    myVariable = tTableID.GetCell(i, j).text

    If Err.Number <> 0 Then Exit For

    On Error GoTo 0

    xclsht.Cells(k, l).Value = myVariable

    l = l + 1

  Next

  If Err.Number <> 0 Then Exit For

  k = k + 1

Next

If Err.Number <> 0 Then Exit Do

tTableID.VerticalScrollbar.Position = tTableID.VerticalScrollbar.Position + VisibleRows

Loop

Set xclwbk = Nothing

Set xclsht = Nothing

Set xclapp = Nothing

'Check the next row in the spreadsheet for data

nRow = nRow + 1

Loop Until Cells(nRow, 1) = ""

------End Code Snippet------

Im pretty new to vba in any deep or useful sense, so Im uncertain where I should adjust this code to be even more generic.

At the moment it works as I expect with a table of x,y size where all the rows have a full set of filled values.

it doesnt loop to any additional rows where the 1st row ends with a cell that has no contents.

Any insights gratefully received.

Cheers
Daniel

[script_man · Accepted solution]

Let's see what we can do. How about this one?

. . .

For i = 0 To VisibleRows - 1

l = 1

  For j = 0 To tDataColumns - 1

    On Error Resume Next

    myVariable = tTableID.GetCell(i, j).text

    'If Err.Number <> 0 Then Exit For

    On Error GoTo 0

    xclsht.Cells(k, l).Value = myVariable

    myVariable = ""

    l = l + 1

  Next

  'If Err.Number <> 0 Then Exit For

  k = k + 1

if k > tDataRows Then Exit For

Next

'If Err.Number <> 0 Then Exit Do

if k > tDataRows Then Exit Do

tTableID.VerticalScrollbar.Position = tTableID.VerticalScrollbar.Position + VisibleRows

Loop

. . .

Regards,

ScriptMan

[daniel_mccollum · Accepted solution]

Makes sense in hindsight. Thanks for the assistance.

regards,

Daniel

[Former Member]

Hi, this code is working almost perfect, but I have a problem, in my table in SAP appears some columns with checkboxes (true/false), how do I export to excel those values?

Thanks