on 2018 Jan 18 6:58 AM
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
Request clarification before answering.
User | Count |
---|---|
41 | |
15 | |
10 | |
9 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.