Extract information from SAP

Chico Junior
Chico Junior used Ask the Experts™
on
Hello. I use SAP to extract various information from the screen manually. I would like a help with a script (VBA / SAPGUI) that searches the entries in a worksheet and copies the SAP information and saves it in an excel spreadsheet. can you help me?

I enter a transaction (ZPDIS_MM_BMD), with the contract number (4600xxxxxx), which shows a list of projects, then I choose one in the second column of these processes and I click for more information, then it has the information I need, it is not editable . There are several contracts and each contract has a list of projects.
Steps.pdf
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kyle SantosSoftware Test Analyst I at Dassault Systemes

Commented:
Hi,

I am here to help you with your open question.  Do you still need help?  I have the ability to alert more experts if you still need help.

If you solved the problem on your own, would you please post the solution here in case others have the same problem?

If you need me to delete this question just say "Delete."

Thank you for using Experts Exchange.

Regards,

Kyle Santos
Customer Relations

Author

Commented:
Yes, I still need help. thank you
Do you have access/permission to record scripts in SAP?
If so, recording the steps that you take & posting the code would be a good starting point for you to modify and then someone on E-E may be able to help more.
I'm on Release 740 and when I want to start recording a script I go to the home screen, press [ALT+F12] then choosing "Script recording & playback". If you make a recording, upload the file, then someone will probably find it easier to help you.

Regards
Rob
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Author

Commented:
Dear broro183,
I have access to script recording.
I send more information requested, are both error messages and the script.
thank you
Cases---Error.docx
Script_CONT_BMD.txt
hi Chico Junior,

For the first error I suggest using the following function to test if a popup occurs & responding as required for example:

'...
        '???????? 1º Error
        If Is_Popup Then
            'push the green tick & progress to the next row in the spreadsheet
        Else
            'include the second loop in here for example...

            'enters from the first row onwards, which is at 0, from column 2, which is PEP_PRIM
            For j = 0 To    '???????? 2º Error
                '...
            Next j
        End If
'...

Function Is_Popup() As Boolean
'6/08/2018 RB: sourced from code written by Danny Garrett & Steve McKillen
'******************************************************************
'input:
'Output: return it there a POPUP windows
    Dim popup
    On Error Resume Next
    Set popup = SAP_Session.findById("wnd[1]")
    If IsObject(popup) Then
        Is_Popup = True
    Else
        Is_Popup = False
    End If
End Function

Open in new window



For the second error, it may not be the best approach, but I would use a Do...Loop with an exit (see the following unrelated example which you could modify) or a Do... Loop Until....
                    'select the last existing row
                    'reset the row counter for each outer loop
                    C223_RowNum = 0
                    Do

                        '###this is currently searching for the last row, but it could be modified to search for something like a specific PV code.
                        If Session.findById("wnd[0]/usr/ssubSUBSCR_1200:SAPLCMFV:1200/tblSAPLCMFVT_MKAL/ctxtMKAL_EXPAND-ADATU[10," & C223_RowNum & "]").Text = vbNullString Then
                            '  Stop
                            C223_RowNum = C223_RowNum - 1
                            Exit Do
                        Else
                            '                            Debug.Print Session.findById("wnd[0]/usr/ssubSUBSCR_1200:SAPLCMFV:1200/tblSAPLCMFVT_MKAL/ctxtMKAL_EXPAND-ADATU[10," & C223_RowNum & "]").Text
                            C223_RowNum = C223_RowNum + 1
                            If C223_RowNum = 100 Then Stop 'a safety check to allow stepping into a potentially infinite loop.
                        End If

                    Loop

Open in new window


Some other general comments:
- You can probably comment out, or delete, any recorded lines of code that include ".caretPosition =".
- I haven't done much coding using Shell, but I notice that you have included the following lines of code twice within the second loop. Are these lines needed (if running the code as vba from within Excel?), or does the code still work if you comment them out?
Set ns1 = CreateObject("WScript.shell")
            ns1.AppActivate "Microsoft Excel"

Open in new window


Regards
Rob
Kyle SantosSoftware Test Analyst I at Dassault Systemes

Commented:
Chico-Junior,

Experts have responded.  Is there anything else we may assist you with?

Author

Commented:
thank you so much!
Hi Chico,
I’m pleased I could help. Would you mind posting your final code to satisfy my curiosity of how you put it all together?

Rob

Author

Commented:
thank you...



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\Desktop\VALORES_BMD.xlsx")
objExcel.Application.Visible = False
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
'seleciona o range das áreas dos projetos
session.findById("wnd[0]/usr/subSUB01:ZPDIS_MM_BMD:1200/ctxtS_AREA-LOW").text = "DFD000797"
session.findById("wnd[0]/usr/subSUB01:ZPDIS_MM_BMD:1200/txtS_AREA-HIGH").text = "DFD001523"
session.findById("wnd[0]/usr/subSUB01:ZPDIS_MM_BMD:1200/ctxtS_DTEMIS-LOW").text = "01.08.2018"
session.findById("wnd[0]/usr/subSUB01:ZPDIS_MM_BMD:1200/ctxtS_DTEMIS-HIGH").text = "01.08.2018"
session.findById("wnd[0]/usr/subSUB01:ZPDIS_MM_BMD:1200/chkP_BMDN").setFocus
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/usr/shell").pressToolbarButton "&SORT_ASC"
session.findById("wnd[1]/usr/subSUB_DYN0500:SAPLSKBH:0610/cntlCONTAINER1_SORT/shellcont/shell").currentCellRow = 4
session.findById("wnd[1]/usr/subSUB_DYN0500:SAPLSKBH:0610/cntlCONTAINER1_SORT/shellcont/shell").selectedRows = "4"
session.findById("wnd[1]/tbar[0]/btn[0]").press

'busca contratos na planilha excel
Dim objExcel
Dim objSheet, intRow, i
Set objExcel = GetObject(, "Excel.Application")
Set objSheet = objExcel.ActiveWorkbook.ActiveSheet
	'entra no BMD da primeira linha, que é a 0, da coluna 2, que é a PEP_PRIM
	For j = 1 To 100000
	session.findById("wnd[0]/usr/shell").currentCellColumn = "PEP_PRIM"
	session.findById("wnd[0]/usr/shell").currentCellRow = j
	session.findById("wnd[0]/usr/shell").clickCurrentCell
	
	If Is_Popup Then
	Else
	End If

	'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, 3) = 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, 4) = bmd

	'clica no Valor de MO, copia e cola o valor
	session.findById("wnd[0]/usr/txtWA_T2400-VLR_TOT_RS").setFocus
	session.findById("wnd[0]/usr/txtWA_T2400-VLR_TOT_RS").caretPosition = 11
	mo = session.findById("wnd[0]/usr/txtWA_T2400-VLR_TOT_RS").Text
	set ns1=createobject("WScript.shell")
	ns1.AppActivate "Microsoft Excel"
	objSheet.Cells(j+2, 5) = mo
	
	'clica na aba materiais, VALOR DE MATERIAL, 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, 6) = vtotal
	
	session.findById("wnd[0]/tbar[0]/btn[3]").press
Next
objExcel.Application.Visible = True

MsgBox ("Parabéns! " & j & " BMDs exportados com sucesso - Abra o Arquivo e ESC")

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial