Script MB51

[Former Member]

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?

[MateuszAdamus]

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

[Former Member]

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)

[MateuszAdamus]

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

[Former Member]

The error message:

[MateuszAdamus]

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

[Sandra_Rossi]

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

Object required 424

[Former Member]

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 ****************************************************

[MateuszAdamus]

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

[Former Member]

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!!

[MateuszAdamus]

You're welcome! 🙂

Kind regards,
Mateusz