Extract information from SAP

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.
Chico JuniorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle SantosSoftware Test Analyst ICommented:

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.


Kyle Santos
Customer Relations
Chico JuniorAuthor Commented:
Yes, I still need help. thank you
Rob BrockettCommented:
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.

Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Chico JuniorAuthor Commented:
Dear broro183,
I have access to script recording.
I send more information requested, are both error messages and the script.
thank you
Rob BrockettCommented:
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
            '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
'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
        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

                        '###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
                            '                            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


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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kyle SantosSoftware Test Analyst ICommented:

Experts have responded.  Is there anything else we may assist you with?
Chico JuniorAuthor Commented:
thank you so much!
Rob BrockettCommented:
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?

Chico JuniorAuthor 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)

' abre transação ZPDIS_MM_BMD
session.findById("wnd[0]/tbar[0]/okcd").Text = "ZPDIS_MM_BMD"
'clica em 2ª via BMD'
session.findById("wnd[0]/usr/subSUB01:ZPDIS_MM_BMD:1200/chkP_BMDN").Selected = True
'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/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"

'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
	If Is_Popup Then
	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").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").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/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
objExcel.Application.Visible = True

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

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.