on 07-26-2018 6:43 AM
Olá amigos, bom dia!
Estou tentando fazer um script vba para copiar uma determinada informação do SAP para o Excel.
No segundo "for" eu defini For j = 0 To 10, no entanto, percebi que tem uns com mais e outros com menos linhas, depende do número do contrato.
Não tenho experiênica, gostaria que me ajudassem, queria uma solução onde o script verificasse que quando chegar no j = 6 (por exemplo) ele percebesse que não tem essa linha na tabela e voltasse para o laço anterior.
Poderiam me ajudar?
OBRIGADO
If Not IsObject(Application) Then
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
'abre planilha excel
Set objExcel = CreateObject("Excel.Application")
endereco = InputBox("Salve a relação dos Contratos aqui:", "Caminho", "C:\Users\c049839\Desktop\CONTRATO.xlsx")
objExcel.Application.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(endereco)
objExcel.Columns.AutoFit
' abre transação ZPDIS_MM_BMD
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "ZPDIS_MM_BMD"
session.findById("wnd[0]/tbar[0]/btn[0]").press
'clica em 2ª via BMD'
session.findById("wnd[0]/usr/subSUB01:ZPDIS_MM_BMD:1200/chkP_BMDN").Selected = True
session.findById("wnd[0]/usr/subSUB01:ZPDIS_MM_BMD:1200/chkP_BMDN").SetFocus
'busca contratos na planilha 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
CONTRATO = Trim(CStr(objSheet.Cells(i, 1).Value))
'entra nos contratos
session.findById("wnd[0]/usr/subSUB01:ZPDIS_MM_BMD:1200/chkP_BMDN").Selected = True
session.findById("wnd[0]/usr/subSUB01:ZPDIS_MM_BMD:1200/ctxtS_CONTR-LOW").Text = CONTRATO
session.findById("wnd[0]/usr/subSUB01:ZPDIS_MM_BMD:1200/ctxtS_CONTR-LOW").SetFocus
session.findById("wnd[0]/usr/subSUB01:ZPDIS_MM_BMD:1200/ctxtS_CONTR-LOW").caretPosition = 10
session.findById("wnd[0]/tbar[1]/btn[8]").press
'
'conta quantas linhas
session.findById("wnd[0]/usr/shell").setCurrentCell -1,"BMDNR"
session.findById("wnd[0]/usr/shell").selectColumn "BMDNR"
coluna = session.findById("wnd[0]/usr/shell").Text "BMDNR"
set ns1=createobject("WScript.shell")
ns1.AppActivate "Microsoft Excel"
objSheet.Cells(10,10) = coluna
'
'entra no BMD da primeira linha, que é a 0, da coluna 2, que é a PEP_PRIM
For j = 0 To 10
session.findById("wnd[0]/usr/shell").currentCellColumn = "PEP_PRIM"
session.findById("wnd[0]/usr/shell").currentCellRow = j
session.findById("wnd[0]/usr/shell").clickCurrentCell
'clica no numero do CONTRATO, copia e cola o numero do contrato
session.findById("wnd[0]/usr/txtWA_T2400-CONTRATO").caretPosition = 6
num = session.findById("wnd[0]/usr/txtWA_T2400-CONTRATO").Text
set ns1=createobject("WScript.shell")
ns1.AppActivate "Microsoft Excel"
objSheet.Cells(j+2, 6) = num
'clica no numero do BMD, copia e cola o numero do contrato
session.findById("wnd[0]/usr/txtWA_T2400-EBELN").setFocus
session.findById("wnd[0]/usr/txtWA_T2400-EBELN").caretPosition = 9
bmd = session.findById("wnd[0]/usr/txtWA_T2400-EBELN").Text
set ns1=createobject("WScript.shell")
ns1.AppActivate "Microsoft Excel"
objSheet.Cells(j+2, 7) = bmd
'clica na aba materiais, No VALOR TOTAL, copia e cola o valor
session.findById("wnd[0]/usr/tabsT_TABSTRIP/tabpT_TABSTRIP_FC2").select
session.findById("wnd[0]/usr/tabsT_TABSTRIP/tabpT_TABSTRIP_FC2/ssubT_TABSTRIP_SCA:SAPLZFDIS_MM_BMD:2420/txtWA_TDOCTAR-TOT_VLR_MEDMATC").setFocus
session.findById("wnd[0]/usr/tabsT_TABSTRIP/tabpT_TABSTRIP_FC2/ssubT_TABSTRIP_SCA:SAPLZFDIS_MM_BMD:2420/txtWA_TDOCTAR-TOT_VLR_MEDMATC").caretPosition = 15
vtotal = session.findById("wnd[0]/usr/tabsT_TABSTRIP/tabpT_TABSTRIP_FC2/ssubT_TABSTRIP_SCA:SAPLZFDIS_MM_BMD:2420/txtWA_TDOCTAR-TOT_VLR_MEDMATC").Text
set ns1=createobject("WScript.shell")
ns1.AppActivate "Microsoft Excel"
objSheet.Cells(j+2, 😎 = vtotal
session.findById("wnd[0]/tbar[0]/btn[3]").press
Next
Next
User | Count |
---|---|
84 | |
10 | |
10 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.