-2

I am trying to upload info into SAP GUI from Excel data. The VBA script runs, but the problems are below:

  1. It will only work on rows 10-90 flawlessly. If I need it to be above row 90, I get an error.
  2. See the example below - If one of the rows is missing then I am off by one row the entire time. Example of ALV GuiGridView

Any help would be great!

Code:

If Not IsObject(Application) Then
   Set SapGuiAuto = GetObject("SAPGUI")
End If
If Not IsObject(session) Then
End If
If IsObject(WScript) Then
   WScript.ConnectObject session, "on"
   WScript.ConnectObject Application, "on"
End If
session.findById("wnd[0]").ResizeWorkingPane 150, 26, False
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nzsd_reqord"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtS_VBELN-LOW").Text = salesdoc1
session.findById("wnd[0]/usr/ctxtS_VBELN-LOW").caretPosition = 7
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/shellcont/shell").setCurrentCell -1, "POSNR"
session.findById("wnd[0]/shellcont/shell").SelectColumn "POSNR"
session.findById("wnd[0]/shellcont/shell").PressToolbarButton "&FIND"
session.findById("wnd[1]/usr/txtGS_SEARCH-VALUE").Text = itemnumber
session.findById("wnd[1]/usr/cmbGS_SEARCH-SEARCH_ORDER").Key = "0"
session.findById("wnd[1]/usr/txtGS_SEARCH-VALUE").caretPosition = 2
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]").Close

Dim rowNr As Long
Dim rg As Range

Set rg = Range("B2")  'the cell in question

Select Case rg.Value
    Case Is <= 90: rowNr = Left(rg.Value, 1) - 1
    Case 100: rowNr = 10
End Select

session.findById("wnd[0]/shellcont/shell").ModifyCell rowNr, "EINTREFF", deldate
session.findById("wnd[0]/shellcont/shell").ModifyCell rowNr, "BEGRU1", reason
session.findById("wnd[0]/shellcont/shell").ModifyCell rowNr, "BEGRU2", Bizpurpose
session.findById("wnd[0]/shellcont/shell").CurrentCellColumn = "BEGRU2"
session.findById("wnd[0]/shellcont/shell").FirstVisibleColumn = "OFMNG"
session.findById("wnd[0]/shellcont/shell").ClearSelection
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[1]/usr/btnBUTTON_1").press
Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Steve Jackson
  • 1
  • 1
  • `Case Is <= 90: rowNr = Left(rg.Value, 1) - 1` recheck your logic here? – findwindow Sep 27 '22 at 20:11
  • @findwindow - I have piecemealed this thing together lol - Could you help me with the logic? – Steve Jackson Sep 27 '22 at 20:22
  • I can't help with your logic without knowing what you're trying to do. It's odd you skip rows 91-99. – findwindow Sep 27 '22 at 20:24
  • @findwindow - Check out the photo underneath the code "Example Here" I am trying to upload the info I input from excel so that it fills into SAP. – Steve Jackson Sep 27 '22 at 20:31
  • That doesn't convey what you're trying to do. – findwindow Sep 27 '22 at 20:33
  • @findwindow - I was able to upload the info from excel to SAP using the code above and it inputted it into SAP - The problem is once I go to 100+ it messes up, and when I put in row 60 it put the info on row 70 b/c row 50 was missing. – Steve Jackson Sep 27 '22 at 20:38
  • `It will only work on rows 10-90 flawlessly.` so that's a lie XD – findwindow Sep 27 '22 at 20:41
  • My guess is the blank rows are the issue. Delete them before you upload. – findwindow Sep 27 '22 at 20:43
  • `once I go to 100+ it messes up` because you have no `case` for it. You should study the code you found. – findwindow Sep 27 '22 at 20:44
  • @findwindow - Haha - kindof - I have to do alot of these so sometimes rows 10-90 are fully there. When they are there it works flawlessly lol, but other times a row is missing because the item has already shipped. The bigger issue is after row 90 most of the time it glitches. – Steve Jackson Sep 27 '22 at 20:47
  • Downvoting because you have no interest in attempting to understand code you found. – findwindow Sep 27 '22 at 20:48
  • @findwindow - The opposite - I didn't find this code - I found bits and pieces and put them together, and I think that is why I can't figure it out. – Steve Jackson Sep 27 '22 at 20:50
  • It seems your question is not correctly asked, please edit it if needed. I think that it's about determining the row number which contains the value in `itemnumber`. After searching the row (your &FIND block), the cell which matches the value is selected. You may know that row number by using the property [`SelectedCells`](https://help.sap.com/docs/search?q=GuiGridView&product=sap_gui_for_windows). – Sandra Rossi Sep 28 '22 at 11:13

1 Answers1

0

As Sandra Rossi already suspected, it could also simply be the line found in the grid using the find block, which could be queried as follows.

for example:

...
Set rg = Range("B2")  'the cell in question

'Select Case rg.Value
'Case Is <= 90: rowNr = Left(rg.Value, 1) - 1
'Case 100: rowNr = 10
'End Select

rowNr = session.findById("wnd[0]/shellcont/shell").currentCellRow

session.findById("wnd[0]/shellcont/shell").ModifyCell rowNr, "EINTREFF", deldate
...

Regards, ScriptMan

ScriptMan
  • 1,580
  • 1
  • 9
  • 9