Skip to Content
0
Former Member
Jan 18, 2018 at 06:58 AM

Keeping my excel macro running after popup

275 Views

Hello everyone.
I'm new to this community and new to the VBA and SAP languages.
My problem is the following.
I have a macro in excel that extracts many reports from a DB in SAP, the problem is that some of the reports I tried to extrats have no data and a Popup shows and tells me that no data is in that period and my macro stops running and does not extract the rest of the reports that I need.
I thought that I have to put some validation code with some If Then sentences, but I cannot find the right way to do it.

Private Sub CommandButton1_Click()
    
    Dim SAPGUI
    Dim Applic
    Dim connection
    Dim Session
    Dim WSHShell

    
    Dim elementoLista
    Dim mensaje As String
    Dim ceco As Variant
    Dim esteLibro As Workbook
    Dim estaHoja As Worksheet
    Set esteLibro = Application.ActiveWorkbook
    Set estaHoja = esteLibro.Worksheets("CONFIGURACION")
    
    Dim sapMandate: sapMandate = estaHoja.Range("C5").Value
    Dim usuarioSAP: usuarioSAP = estaHoja.Range("C6").Value
    Dim userPassword: userPassword = estaHoja.Range("C7").Value
    Dim cecoResponsable: cecoResponsable = estaHoja.Range("C13").Value
    Dim sociedadCO: sociedadCO = estaHoja.Range("C15").Value
    Dim periodoDesde: periodoDesde = estaHoja.Range("C16").Value
    Dim periodoHasta: periodoHasta = estaHoja.Range("C17").Value
    
    Dim indicadorIG: indicadorIG = estaHoja.Range("C19").Value
    Dim anhoEjercicio: anhoEjercicio = estaHoja.Range("C20").Value
    Dim baseDatos: baseDatos = estaHoja.Range("C21").Value
    
    
    Dim I As Long
    Dim X As Long
    Dim arrValues()
        If ListBox1.ListIndex <> -1 Then
            For I = 0 To ListBox1.ListCount - 1
                If ListBox1.Selected(I) Then
                ReDim Preserve arrValues(X)
                arrValues(X) = ListBox1.List(I)
                X = X + 1
                End If
            Next I
        End If
    arreglo = arrValues()
    
    Dim dirRaiz: dirRaiz = esteLibro.Path

Application.DisplayAlerts = False

Shell "C:\Program Files\SAP\FrontEnd\SAPgui\saplogon.exe", vbNormalFocus

Set WSHShell = CreateObject("WScript.Shell")
  Do Until WSHShell.AppActivate("SAP Logon ")
    Application.Wait Now + TimeValue("0:00:01")
  Loop



Set WSHShell = Nothing
Set SAPGUI = GetObject("SAPGUI")
Set Applic = SAPGUI.GetScriptingEngine
Set connection = Applic.OpenConnection("00013   PRD  Producción Latam", True)
Set Session = connection.Children(0)
            
'  Session.findById("wnd[0]").iconify
  Session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = sapMandate
  Session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = usuarioSAP 'Environ("Username") 'eventualmente capturar desde excel
  Session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = userPassword 'sysstart.psswrd
  Session.findById("wnd[0]").sendVKey 0

        Session.findById("wnd[0]").maximize
        Session.findById("wnd[0]/tbar[0]/okcd").Text = baseDatos
        Session.findById("wnd[0]").sendVKey 0
        Session.findById("wnd[1]/usr/btnOPCION1").press
        For Each ceco In arreglo
        Dim nombreArchivo: nombreArchivo = "CECO" & ceco & "_" & Day(Now) & "-" & Month(Now) & "-" & Year(Now) & ".xls"
            Session.findById("wnd[0]/usr/chkP_DESPLE").Selected = True
            Session.findById("wnd[0]/usr/ctxtKOKRS").Text = sociedadCO
            Session.findById("wnd[0]/usr/ctxtFKSTL").Text = ceco
            Session.findById("wnd[0]/usr/txtANNO").Text = anhoEjercicio
            Session.findById("wnd[0]/usr/ctxtPOPER-LOW").Text = periodoDesde
            Session.findById("wnd[0]/usr/ctxtPOPER-HIGH").Text = periodoHasta
            Session.findById("wnd[0]/usr/ctxtZZINVGAS").Text = indicadorIG
            Session.findById("wnd[0]/usr/ctxtZZINVGAS").SetFocus
            Session.findById("wnd[0]/usr/ctxtZZINVGAS").caretPosition = 1
            Session.findById("wnd[0]/tbar[1]/btn[8]").press
            ******* HERE IS WHERE THE POPUP APPEARS WHEN NO DATA IS FOUND ******
            Session.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[2]").Select
            Session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
            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 = dirRaiz
            Session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = nombreArchivo
            Session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 15
            Session.findById("wnd[1]/tbar[0]/btn[11]").press
            Session.findById("wnd[0]").sendVKey 0
            Session.findById("wnd[0]/tbar[0]/btn[15]").press
          Next ceco
              
'Closes SAP connection
Set Session = Nothing
connection.CloseSession ("ses[0]")
Set connection = Nothing
    
    If Len(mensaje) = 0 Then
        mensaje = "No hay elementos seleccionados"
    End If
    
End Sub

This is the popup that stops my script from keeping the extraction

Can anyone please help me with this??
Thanks

Attachments

popup.png (12.9 kB)