Compare 2 cells in excel using VBA for SAP GUI scripting

Question

Currently I have a script to copy data from an excel sheet and paste it in SAP in the transaction code scal.

What my code does, is filter the calendar selection by each ID (e.g. AA/AB) and then copy the data for Start Date and End Date into the scal ID. Save the changes and then move onto the next ID.

Let's say I have an excel sheet with more than one entry for the same calendar ID. What can I do to make excel check if the next row has the same calendar ID as the current one, and then, instead of saving, copy and paste the start/end date for the same ID and only then moving onto the next one.

tldr:compare current column and the column below; if both columns are same, continue with the changes; otherwise save and go back.

Code is given below:

Sub Main()
    Dim row As Integer
    Dim Session

'1. System name entry
    sSystemName = sSystemName_EntrySTD()
'2. Connect to open SAP session
    If bSessionConfirmSTD(Session, sSystemName) = False Then
        MsgBox sSystemName & " session is not opened", vbCritical
        End
    End If

'3. Display status in EXCEL
    application.StatusBar = "Attached to active session"

    On Error Resume Next

'4. for all rows in active sheet (Tabelle1)
    row = 2
    While Tabelle1.Cells(row, 1).Value <> ""

        Dim s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19 As String
        Dim i As Integer

'5. Get value from first column of current row
        s1 = Tabelle1.Cells(row, 1).Value
        s2 = Tabelle1.Cells(row, 2).Value
        s3 = Tabelle1.Cells(row, 3).Value
        s4 = Tabelle1.Cells(row, 4).Value
        s5 = Tabelle1.Cells(row, 5).Value
        s6 = Tabelle1.Cells(row, 6).Value
        s7 = Tabelle1.Cells(row, 7).Value
        s8 = Tabelle1.Cells(row, 8).Value
        s9 = Tabelle1.Cells(row, 9).Value
        s10 = Tabelle1.Cells(row, 10).Value
        s11 = Tabelle1.Cells(row, 11).Value
        s12 = Tabelle1.Cells(row, 12).Value
        s13 = Tabelle1.Cells(row, 13).Value
        s14 = Tabelle1.Cells(row, 14).Value
        s15 = Tabelle1.Cells(row, 15).Value
        s16 = Tabelle1.Cells(row, 16).Value
        s17 = Tabelle1.Cells(row, 17).Value
        s18 = Tabelle1.Cells(row, 18).Value
        s19 = Tabelle1.Cells(row, 19).Value

'6. Update status in EXCEL
        application.StatusBar = "Processing row " & RTrim(LTrim(Str(row))) & ": " & s1

'BEGIN Paste your script here
'===============================================================================================================

Session.findById("wnd[0]").maximize
Session.findById("wnd[0]/tbar[0]/okcd").Text = "scal"
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]/usr/radFMEN-FABKAL").Select
Session.findById("wnd[0]/usr/radFMEN-FABKAL").SetFocus
Session.findById("wnd[0]/usr/btnUPDATE").press
Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").currentCellRow = -1
Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectColumn "IDENT"
Session.findById("wnd[0]/tbar[1]/btn[38]").press
Session.findById("wnd[1]/usr/ssub%_SUBSCREEN_FREESEL:SAPLSSEL:1105/ctxt%%DYN001-LOW").Text = s1
Session.findById("wnd[1]/usr/ssub%_SUBSCREEN_FREESEL:SAPLSSEL:1105/ctxt%%DYN001-LOW").caretPosition = 2
Session.findById("wnd[1]").sendVKey 0
Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").currentCellColumn = ""
Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectedRows = "0"
Session.findById("wnd[0]/tbar[1]/btn[7]").press
Session.findById("wnd[0]/tbar[1]/btn[17]").press
Session.findById("wnd[0]/tbar[1]/btn[13]").press
Session.findById("wnd[1]/usr/chkTIFAB-ARBTAG").Selected = s4
Session.findById("wnd[1]/usr/ctxtTIFAB-DATUMVON").Text = s2
Session.findById("wnd[1]/usr/ctxtTIFAB-DATUMBIS").Text = s3
Session.findById("wnd[1]/usr/txtTFAIT-LTEXT").Text = s5
Session.findById("wnd[1]/usr/txtTFAIT-LTEXT").SetFocus
Session.findById("wnd[1]/usr/txtTFAIT-LTEXT").caretPosition = 11
Session.findById("wnd[1]").sendVKey 0



'===============================================================================================================
'END Paste your script here
Tabelle1.Cells(row, 15).Value = Session.findById("wnd[0]/sbar").Text

'7. Log processed entries in Tabelle1
        Tabelle1.Cells(row, 16).Value = "Done"
'8. Continue with next row
        row = row + 1

    Wend

    application.StatusBar = "Processing finished"

    On Error GoTo 0
    Exit Sub

info:
    MsgBox "error on line " & row

End Sub

Please edit your question and show what you have tried so far to achieve what you are asking for (including what errors you get and where, or where in your code you got stuck exactly): No attempt was made — Pᴇʜ
Thank you for your message. I have added the code to my post. What I want to accomplish is compare if the ID on the column being read and the ID on the following column are the same. — Shahir Afif Islam
① Don't use On Error Resume Next without error handling. This just hides all errors but they still occur, you just cannot see them. It makes you blind and you cannot fix your code, because you don't see the errors. Remove it. ② We know now what you are trying but you didn't say how you tried to achieve that and what goes wrong while trying it? Where do you get errors and which? — Pᴇʜ
Appreciate the feedback. Unfortunately, the problem that I have is I cannot figure out how to achieve what I am trying to accomplish. I do not have any visual basic background and I developed this code after going through some tutorial videos on youtube for SAP Gui Scripting. So the question that I have is, i) is this achievable from your POV? ii) can you please provide me with an example of an example code to compare two columns of data? If yes, then I work on this further and test out my script — Shahir Afif Islam

ScriptMan ScriptMan · Accepted Answer · 2018-07-03T07:08:43

No idea if I understood it correctly, what should be compared actually. But one possible solution might look like this:

. . .
'4. for all rows in active sheet (Tabelle1)
row = 2
While Tabelle1.Cells(row, 1).Value <> ""

    Dim s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19 As String
    Dim i As Integer

'5. Get value from first column of current row
    s1 = Tabelle1.Cells(row, 1).Value
    if Tabelle1.Cells(row + 1, 1).Value <> "" then
       if s1 = Tabelle1.Cells(row + 1, 1).Value then row = row + 1
    end if
    s2 = Tabelle1.Cells(row, 2).Value
    . . .

Regards, ScriptMan

Compare 2 cells in excel using VBA for SAP GUI scripting

1 Answers