Test if an Excel cell is empty

Question

I created below SAPGUI script for creation of PO's.

The script is working fine most of the times. However I would like to improve it a bit more.

First question:

For instance if the script stops in the middle of the process were you have a list (Excel input sheet) of 50 materials for which you need to create same amount of PO's. If the script stops for example at material number 30 I can't just start the script again because it will start from the beginning again but PO's for those materials are already created.

Now as you can see in the code the script puts in comment "Finished" in column 6 in each row after PO is created: objSheet.Cells(i, 6) = "Finished"

It also fetches the created PO number from SAP and puts it in the column next to it (column 7): objSheet.Cells(i, 7).Value = Mid(session.findById("wnd[0]/sbar").Text, 38)

I would like to add an "if code" saying that if there is PO number in column 7 that is skips those rows from the excel input sheet and instead go to the next row for which cell in column 7 is empty.

Second question:

session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[0]/btn[11]").press

Above is Enter recorded 3 times followed by the Save. I would like to add to the script if this save is not successful that it presses save one more time. Sometimes the script just stops at this step. However if Save is pressed manually one more time the PO is saved and created. I have no clue why the script stops at this step sometimes but for certain materials it does. Again everything is filled in by the script and all that is left is to press save which is what I do if this problem occurs. So I save the PO manually and put PO number into Excel input sheet and since we solved 1st problem I can just restart the script again and it will continue again from first empty cell in column 7 (if isEmpty(objSheet.cells(i,7)) then).

So I would like the script to press Save only when the first save is unsuccessful. If I would to add session.findById("wnd[0]/tbar[0]/btn[11]").press two times after one another the script will be stopped if the first save is successful. Then for the second save the SAP will say there is nothing to be saved, hence the script will stop working due to this unsuspected event/message. So if possible I would like the script to only press save one more time if the first save is unsuccessful. Basically a code saying iferror after first save then repeat the save again.

    Set SapGuiAuto  = GetObject("SAPGUI")
    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
    session.findById("wnd[0]").maximize

    REM ADDED BY EXCEL *************************************

    Dim objExcel
    Dim objSheet, intRow, i
    Set objExcel = GetObject(,"Excel.Application")
    Set objSheet = objExcel.ActiveWorkbook.ActiveSheet

    For i = 2 to objSheet.UsedRange.Rows.Count
    COL2 = Trim(CStr(objSheet.Cells(i, 2).Value)) 'Column2 Agreement number
    COL3 = Trim(CStr(objSheet.Cells(i, 3).Value)) 'Column3 Agreement Line Item
    COL4 = Trim(CStr(objSheet.Cells(i, 4).Value)) 'Column4 PO Quantity
    COL5 = Trim(CStr(objSheet.Cells(i, 5).Value)) 'Column5 Delivery date

    REM ADDED BY EXCEL *************************************

    session.findById("wnd[0]/tbar[0]/okcd").text = "/nme21n"
    session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/ctxtMEPO1211-KONNR[29,0]").text = COL2
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/txtMEPO1211-KTPNR[30,0]").text = COL3
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/txtMEPO1211-KTPNR[30,0]").setFocus
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/txtMEPO1211-KTPNR[30,0]").caretPosition = 0
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0020/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/txtMEPO1211-MENGE[6,0]").text = COL4
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0020/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/ctxtMEPO1211-EEIND[9,0]").text = COL5
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0020/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/ctxtMEPO1211-EEIND[9,0]").setFocus
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0020/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/ctxtMEPO1211-EEIND[9,0]").caretPosition = 0
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0020/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/txtMEPO1211-MENGE[6,0]").text = COL4
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[0]/btn[11]").press

      objSheet.Cells(i, 6) = "Finished"

      objSheet.Cells(i, 7).Value = Mid(session.findById("wnd[0]/sbar").Text, 38)


      REM FINALIZATION CONTROL CHECK ************************

      aux=col2 & " " & col3 & " " & col4 & " " & col5
      CreateObject("WScript.Shell").run("cmd /c @echo %date% %time% " & aux & " >> 
      C:\SCRIPT\PlOrCreationLog.txt")
      next

      msgbox "Process Completed Forza Juve! :)"

      REM FINALIZATION CONTROL CHECK ************************

Test if an Excel cell is empty

Answers (1)

Related Questions