0

new to SAP but have done some VBA programming.

I need to automate some things in SAP GUI using Excel VBA but I am starting with baby steps and getting stumped.

All I want to do is copy data from a column in SAP GUI (table already open) into an array. Sounds simple. However it starts off well, but never finishes properly. I have a table that has 306 rows. It has gotten stuck at 85, 127, and 178. Not sure if these values mean anything. What's even more puzzling to me is why does it return a value of 0000000127?? I've been looking for an answer for hours.

Running SAP Complex and Excel 2013. Cannot locate SAPfewse.ocx either. I've enabled almost all references in VBA Developer window. Any help/ideas would be much appreciated! I'm hoping it is something obvious.

Set Table = Session.FindById("wnd[0]/usr/cntlDISASSEMBLY_ALV/shellcont/shell")
Dim rows As Long
Dim arrRow() As Variant
Dim colName As String
Dim rowCount As Double

rows = Table.rowCount - 1

ReDim arrRow(rows)

colName = "ZZMRO_CHA"

For j = 0 To rows
    arrRow(j) = Table.GetCellValue(j, colName)
Next

Array watch output - "completed" loop

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Paul Lukas
  • 1
  • Although you Dimmed `arrRow` as `Variant`, try to `Debug.Print` each `arrRow(j)` like: `Debug.Print TypeName(arrRow(j))`.... just to see what is spit out in your Immediate window (Ctrl+G). Curious... – J VBA Mar 14 '19 at 03:09
  • Thanks for your response J! I spent some more time playing with this. I tried your suggestion and it all came out as string. I changed the variables accordingly. What was interesting is that the length of that faulty entry is 390 characters even though only 10 characters are displayed. Also, I tried capturing that entry on its own and still got the funny output. And I tried breaking up the for loop into 0-84 and 85-end. No luck. Everything after the funny output is just "". – Paul Lukas Mar 16 '19 at 00:02
  • Paul, the screen shot makes it look like there are tons of spaces before the actual text. Try - `arrRow(j) = Application.WorksheetFunction(Trim(Table.GetCellValue(j, colName))` – J VBA Mar 16 '19 at 00:10
  • J, I tried it. No luck. Now the faulty value is just 10 characters long instead of 390 lol – Paul Lukas Mar 17 '19 at 13:47
  • It seems to be pulling the record number of your `Table`. Perhaps something with the source data. I'm really unsure at this point... :( – J VBA Mar 17 '19 at 16:36

1 Answers1

0

You could try the following:

...
 For j = 0 To rows 
        arrRow(j) = Table.GetCellValue(j, colName) 
        If j Mod 32 = 0 Then    Table.currentCellRow = j
  Next 
  ...

Regards, ScriptMan

ScriptMan
  • 1,580
  • 1
  • 9
  • 9
  • Hi ScriptMan, thanks for your suggestion! It's funny you mention that. I just got off the phone with an expert and he suggested I scroll down the page and try running it. Funny enough, SAP is THAT picky. Once I scrolled down, it registered the full table and ran smoothly. Having said that, your method works as well! However, it is very slow running it because of the constant screen update. I will find a way to scroll the page down, or CurrentCellRow = LastRow. Nevertheless, I can stop losing sleep over this. Thank you guys! – Paul Lukas Mar 18 '19 at 19:19
  • Hi Paul, You could only read the table at certain intervals to speed it up. (see above) – ScriptMan Mar 18 '19 at 22:10
  • Thanks for the suggestion. That is exactly what I did :) But I used FirstVisibleRow instead. Seemed to run a little faster than CurrentCellRow. – Paul Lukas Mar 19 '19 at 01:39