topic Re: Script MB51 in Application Development and Automation Discussions

Script MB51

Former Member — Thu, 21 May 2020 02:30:56 GMT

I'm having trouble creating a script on the MB51 screen, the code looks like this:

Sub gerar_contagem()






Set session = GetObject("SAPGUI").GetScriptingEngine.Children(0).Children(0)
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/NMB51"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[17]").press
session.findById("wnd[1]/usr/txtENAME-LOW").Text = "w890230"
session.findById("wnd[1]/usr/txtENAME-LOW").SetFocus
session.findById("wnd[1]/usr/txtENAME-LOW").caretPosition = 7
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").currentCellColumn = "TEXT"
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").selectedRows = "0"
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").doubleClickCurrentCell
session.findById("wnd[0]/usr/ctxtBUDAT-LOW").SetFocus
session.findById("wnd[0]/usr/ctxtBUDAT-LOW").caretPosition = 0
session.findById("wnd[0]").sendVKey 4


Dim objExcel
Dim objSheet, intRow, i
Set objExcel = GetObject(, ”Excel.Application”)
Set objSheet = objExcel.ActiveWorkbook.ActiveSheet(1)


For i = 2 To objSheet.UsedRange.Rows.Count
G8 = Trim(CStr(objSheet.Cells(i, 7).Value)) ‘G8
I8 = Trim(CStr(objSheet.Cells(i, 7).Value)) ‘I8


session.findById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").focusDate = G8
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/usr/ctxtBUDAT-HIGH").SetFocus
session.findById("wnd[0]/usr/ctxtBUDAT-HIGH").caretPosition = 0
session.findById("wnd[0]").sendVKey 4
session.findById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").focusDate = I8
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/tbar[1]/btn[48]").press
session.findById("wnd[0]/mbar/menu[3]/menu[2]/menu[1]").Select
session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").setCurrentCell 27, "TEXT"
session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").firstVisibleRow = 18
session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").selectedRows = "27"
session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").clickCurrentCell
session.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[1]").Select
session.findById("wnd[1]/tbar[0]/btn[11]").press




End Sub

The problem is in these lines that have the objective of inserting the information from cells G8 and I8 of the spreadsheet in the date fields of screen MB51::

Dim objExcel
Dim objSheet, intRow, i
Set objExcel = GetObject(, ”Excel.Application”)
Set objSheet = objExcel.ActiveWorkbook.ActiveSheet(1)


For i = 2 To objSheet.UsedRange.Rows.Count
G8 = Trim(CStr(objSheet.Cells(i, 7).Value)) ‘G8
I8 = Trim(CStr(objSheet.Cells(i, 7).Value)) ‘I8

could someone help me with this issue?

Re: Script MB51

MateuszAdamus — Fri, 22 May 2020 06:11:17 GMT

Hi a317531

I mean, you're missing the "Next i" after "For" loop. Is that it?
Otherwise, please submit the error message, without it it's hard to say what is the issue.

Regards,
Mateusz

Re: Script MB51

Former Member — Mon, 25 May 2020 23:46:19 GMT

I intended to insert the cell information "G8" and "I8" from excel for SAP field, but I can't select only this cell and end the loop (Next i)(Eu pretendo setar a informação da celula G8 e I8 do excel para o campo data da tela MB51 no SAP, mas não tenho experiência em fazer esta conexão, e também não sei finalizar o loop, pois não encontro material para estudar sobre)

Re: Script MB51

Former Member — Tue, 26 May 2020 19:30:22 GMT

The error message:

Re: Script MB51

MateuszAdamus — Tue, 26 May 2020 19:42:15 GMT

Hello Alex,

Try searching the internet. VBA has been around for quite some time now and there is plenty of information on different forums. Your favorite search engine is your best friend in this case.

Also, check out the Microsoft pages about VBA: https://docs.microsoft.com/en-us/office/vba/api/overview/.

Kind regards,
Mateusz

Re: Script MB51

MateuszAdamus — Tue, 26 May 2020 19:43:25 GMT

Hello Alex,

I would suggest putting a break point and executing the code step-by-step to find the error source. At this moment the error message is too generic.

Kind regards,
Mateusz

Re: Script MB51

Sandra_Rossi — Tue, 26 May 2020 20:17:19 GMT

Please indicate the error text in English. It's an English forum. It corresponds to:

Object required 424

Re: Script MB51

Former Member — Wed, 27 May 2020 02:45:57 GMT

Hello again!

I edited my post again, and I'm in a new stage of VBA.

At this point the problem is in stopping the script in window SAP, when running VBA I can generate the file I want, but the SAP window that performed this task continues to execute it infinitely.

Any material or tip so that I can solve this?

Thanks for listening!

Sub Cria_nova_contagem()


Set session = GetObject("SAPGUI").GetScriptingEngine.Children(0).Children(0)


Rem  B1 INICIO***********************************************


Dim objExcel
Dim objSheet, intRow, i


Set objExcel = GetObject(, "Excel.Application")
Set objSheet = objExcel.ActiveWorkbook.ActiveSheet




For i = 2 To objSheet.UsedRange.Rows.Count
    If i = 0 Then
    End If
    
INICIO = Trim(CStr(objSheet.Cells(7, 6).Value)) 'DATA INICIO
FIM = Trim(CStr(objSheet.Cells(7, 9).Value)) 'DATA FIM


session.findById("wnd[0]").maximize


Rem B1 FIM *************************************************


session.findById("wnd[0]/tbar[0]/okcd").Text = "/NMB51"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[17]").press
session.findById("wnd[1]/usr/txtENAME-LOW").Text = "W890230"
session.findById("wnd[1]/usr/txtENAME-LOW").SetFocus
session.findById("wnd[1]/usr/txtENAME-LOW").caretPosition = 7
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").currentCellColumn = "TEXT"
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").selectedRows = "0"
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").doubleClickCurrentCell
session.findById("wnd[0]/usr/ctxtBUDAT-LOW").Text = INICIO
session.findById("wnd[0]/usr/ctxtBUDAT-HIGH").Text = FIM
session.findById("wnd[0]/usr/ctxtBUDAT-HIGH").SetFocus
session.findById("wnd[0]/usr/ctxtBUDAT-HIGH").caretPosition = 10
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/tbar[1]/btn[48]").press
session.findById("wnd[0]/mbar/menu[3]/menu[2]/menu[1]").Select
session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").setCurrentCell 27, "TEXT"
session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").firstVisibleRow = 18
session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").selectedRows = "27"
session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").clickCurrentCell
session.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[1]").Select
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "\\msbrtole004\data\Logistica\ESTOQUE\SAP"
session.findById("wnd[1]/usr/ctxtDY_PATH").SetFocus
session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 40
session.findById("wnd[1]/tbar[0]/btn[11]").press




Rem B2 ************************************************


Next i(0)


End Sub




Rem ****************************************************

Re: Script MB51

MateuszAdamus — Wed, 27 May 2020 06:43:40 GMT

Hello

I have not programmed in Visual Basic for years...

If the FOR loop goes forever then I'd check the objSheet.UsedRange.Rows.Count value. Maybe it's a very large number? Otherwise put a breakpoint in the code and see what is going on.

It will never be zero, so this condition will never be met.

For i = 2 To objSheet.UsedRange.Rows.Count
    If i = 0 Then
    End If

You've got a row constant here. Should it be an I variable instead?

INICIO = Trim(CStr(objSheet.Cells(7, 6).Value)) 'DATA INICIO
FIM = Trim(CStr(objSheet.Cells(7, 9).Value)) 'DATA FIM

Do you want to maximize the SAP GUI for every loop pass? Isn't it enough to maximize it before the loop?

session.findById("wnd[0]").maximize

Do you want to open the transaction for every Excel row with data? Isn't it enough to open it before the FOR loop?

session.findById("wnd[0]/tbar[0]/okcd").Text = "/NMB51"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[17]").press

Is zero in brackets required?

Next i(0)

Kind regards,

Mateusz

Re: Script MB51

Former Member — Wed, 27 May 2020 19:06:26 GMT

Thank you very much Mateusz !!

Your comment was very enlightening, I understood what was causing my complication, and I managed to reach my goal, in the end the code was like this:

Sub Cria_nova_contagem()


Set session = GetObject("SAPGUI").GetScriptingEngine.Children(0).Children(0)
session.findById("wnd[0]/tbar[0]/okcd").Text = "/NMB51"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[17]").press
session.findById("wnd[1]/usr/txtENAME-LOW").Text = "W890230"
session.findById("wnd[1]/usr/txtENAME-LOW").SetFocus
session.findById("wnd[1]/usr/txtENAME-LOW").caretPosition = 7
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").currentCellColumn = "TEXT"
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").selectedRows = "0"
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").doubleClickCurrentCell




Rem  B1 INICIO***********************************************


Dim objExcel
Dim objSheet, intRow, i


Set objExcel = GetObject(, "Excel.Application")
Set objSheet = objExcel.ActiveWorkbook.ActiveSheet




For i = 2 To objSheet.UsedRange.Rows.Count
    If i <> 2 Then
    End If
    
INICIO = Trim(CStr(objSheet.Cells(7, 6).Value)) 'DATA INICIO
FIM = Trim(CStr(objSheet.Cells(7, 9).Value)) 'DATA FIM


session.findById("wnd[0]").maximize


Rem B1 FIM *************************************************






session.findById("wnd[0]/usr/ctxtBUDAT-LOW").Text = INICIO
session.findById("wnd[0]/usr/ctxtBUDAT-HIGH").Text = FIM


Rem B2 ************************************************


Next i


Rem ****************************************************


session.findById("wnd[0]/usr/ctxtBUDAT-HIGH").SetFocus
session.findById("wnd[0]/usr/ctxtBUDAT-HIGH").caretPosition = 10
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/tbar[1]/btn[48]").press
session.findById("wnd[0]/mbar/menu[3]/menu[2]/menu[1]").Select
session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").setCurrentCell 27, "TEXT"
session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").firstVisibleRow = 18
session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").selectedRows = "27"
session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/shellcont/shell").clickCurrentCell
session.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[1]").Select
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "\\msbrtole004\data\Logistica\ESTOQUE\SAP"
session.findById("wnd[1]/usr/ctxtDY_PATH").SetFocus
session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 40
session.findById("wnd[1]/tbar[0]/btn[11]").press










End Sub

Obrigado!!

Re: Script MB51

MateuszAdamus — Wed, 27 May 2020 19:38:55 GMT

You're welcome! 🙂

Kind regards,
Mateusz