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