scripting with Excel

Former Member — Tue, 18 Oct 2016 11:09:39 GMT

Hello,

I am struggling with VBA to get data from transaction COOIS.

I want to check in VBA if COOIS returns any data. If there will be message after Execute (F8) There is no data for the selection (message no. COIS024) i want to skip to some error handling (otherwise continue according to program).

I have done this script but it doesn't work

Can you please check it and let me know?

Many thanks in advance

David

Sub coois()


  Dim Application, SapGuiAuto As Object
  Dim connection, session As Object
  Set SapGuiAuto = GetObject("SAPGUI")
  Set Application = SapGuiAuto.GetScriptingEngine
  Set connection = Application.Children(0)
  Set session = connection.Children(0)

On Error GoTo ErrHandler


session.findById("wnd[0]/tbar[0]/okcd").Text = "/ncoois"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ssub%_SUBSCREEN_TOPBLOCK:PPIO_ENTRY:1100/chkPPIO_ENTRY_SC1100-SELECT_PLANNEDORDS").SetFocus
session.findById("wnd[0]/usr/ssub%_SUBSCREEN_TOPBLOCK:PPIO_ENTRY:1100/chkPPIO_ENTRY_SC1100-SELECT_PLANNEDORDS").Selected = True
session.findById("wnd[0]/usr/ssub%_SUBSCREEN_TOPBLOCK:PPIO_ENTRY:1100/chkPPIO_ENTRY_SC1100-SELECT_PRODORDS").SetFocus
session.findById("wnd[0]/usr/ssub%_SUBSCREEN_TOPBLOCK:PPIO_ENTRY:1100/chkPPIO_ENTRY_SC1100-SELECT_PRODORDS").Selected = False
session.findById("wnd[0]/usr/ssub%_SUBSCREEN_TOPBLOCK:PPIO_ENTRY:1100/ctxtPPIO_ENTRY_SC1100-ALV_VARIANT").Text = "000000000001"
session.findById("wnd[0]/usr/ssub%_SUBSCREEN_TOPBLOCK:PPIO_ENTRY:1100/ctxtPPIO_ENTRY_SC1100-ALV_VARIANT").SetFocus
session.findById("wnd[0]/usr/ssub%_SUBSCREEN_TOPBLOCK:PPIO_ENTRY:1100/ctxtPPIO_ENTRY_SC1100-ALV_VARIANT").caretPosition = 12
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_WERKS-LOW").Text = plant
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_COMPO-LOW").Text = material
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_CWERK-LOW").Text = plant
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_PLNUM-LOW").Text = ""
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_MATNR-LOW").Text = ""
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_PWERK-LOW").Text = ""
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_DISPO-LOW").Text = ""
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_FEVOR-LOW").Text = ""
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_LGORT-LOW").Text = ""
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_BDTER-LOW").Text = ""
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_ECKST-LOW").Text = ""
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_ECKEN-LOW").Text = ""
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_TERST-LOW").Text = ""
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_TEREN-LOW").Text = ""
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/txtS_RECKST-LOW").Text = ""
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/txtS_RECKEN-LOW").Text = ""
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/txtS_RTERST-LOW").Text = ""
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/txtS_RTEREN-LOW").Text = ""
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtSO_VERID-LOW").Text = ""
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtSO_M01_F-LOW").Text = ""
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_COMPO-LOW").SetFocus
session.findById("wnd[0]/usr/tabsTABSTRIP_SELBLOCK/tabpSEL_00/ssub%_SUBSCREEN_SELBLOCK:PPIO_ENTRY:1200/ctxtS_COMPO-LOW").caretPosition = 10
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 8


'----- here is stop when there are no data from COOIS


session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").setCurrentCell -1, "GLTRP"
session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").selectColumn "GLTRP"
session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").pressToolbarButton "&NAVIGATION_PROFILE_TOOLBAR_EXPAND"
session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").setCurrentCell -1, "GSTRP"
session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").selectColumn "GSTRP"
session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").pressToolbarButton "&SORT_ASC"
session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").selectContextMenuItem "&PC"
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = ActiveWorkbook.Path & "\"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "coois.txt"
session.findById("wnd[1]/usr/ctxtDY_FILE_ENCODING").Text = "0000"
session.findById("wnd[1]/usr/ctxtDY_FILE_ENCODING").SetFocus
session.findById("wnd[1]/usr/ctxtDY_FILE_ENCODING").caretPosition = 4
session.findById("wnd[1]/tbar[0]/btn[0]").press

ErrHandler:
Call stop_macro

End Sub


Sub stop_macro()

MsgBox "No output"
End

End Sub

Re: scripting with Excel

Stefan-Schnell — Thu, 20 Oct 2016 16:03:42 GMT

Hello David,

try this snippet:

If IsObject(session.findById("wnd[1]")) Then
  session.findById("wnd[1]/tbar[0]/btn[0]").press
  Call stop_macro
Else
  session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").setCurrentCell -1, "GLTRP"
  session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").selectColumn "GLTRP"
  session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").pressToolbarButton "&NAVIGATION_PROFILE_TOOLBAR_EXPAND"
  session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").setCurrentCell -1, "GSTRP"
  session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").selectColumn "GSTRP"
  session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").pressToolbarButton "&SORT_ASC"
  session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
  session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").selectContextMenuItem "&PC"
  session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").SetFocus
  session.findById("wnd[1]/tbar[0]/btn[0]").press
  session.findById("wnd[1]/usr/ctxtDY_PATH").Text = ActiveWorkbook.Path & "\"
  session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "coois.txt"
  session.findById("wnd[1]/usr/ctxtDY_FILE_ENCODING").Text = "0000"
  session.findById("wnd[1]/usr/ctxtDY_FILE_ENCODING").SetFocus
  session.findById("wnd[1]/usr/ctxtDY_FILE_ENCODING").caretPosition = 4
  session.findById("wnd[1]/tbar[0]/btn[0]").press
End If

I check with IsObject the existence of the dialog. If it exists there are no data available, I close the dialog and jump via Call to another sub procedure.

Let us know your results.

Cheers
Stefan

Re: scripting with Excel

Former Member — Tue, 25 Oct 2016 18:27:23 GMT

Hello Stefan,

thanks for reply but it doesn't work. But I have found very simple solution that helped me with solving my problem SAP GUI Scripting error popup

Regards David

Re: scripting with Excel

Stefan-Schnell — Tue, 25 Oct 2016 19:08:55 GMT

Hello David,

thanks for your reply and the sharing of your solution.

Cheers
Stefan

topic scripting with Excel in Application Development and Automation Discussions

scripting with Excel

Re: scripting with Excel

Re: scripting with Excel

Re: scripting with Excel