Sap script- t-code AS02

[Former Member]

Hello Everyone,

I am a real beginner in the VBA & SAP Scripting.

Below is my VBA Script  for changing the asset master data in t-code AS02.

It works really fine but I would like to make it better.

First thing I would like to do is to  create a loop which will make possible to change lot of  assets numbers which will be filled in the excel sheet Do you have any suggestions ?

The second thing I would like to do is to make visible in the excel  (where script is run) the SAP output message  after the change is done  -  for instance      “Asset number XXXX has been changed” or if for instance sth went wrong – “Asset number does not belong to selected company code” etc

Thank you in advance for any help.

Regards

MJ

Private Sub CommandButton1_Click()
If Not IsObject(Application1) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set Application1 = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = Application1.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 Application1, "on"
End If
Set xclapp = CreateObject("Excel.Application")
Set xclwbk = xclapp.Workbooks.Open("c:\Users\Public\AS02.xlsm")
Set xclsht = xclwbk.Sheets("Sheet1")

For i = 2 To xclapp.ActiveCell.SpecialCells(11).Row
For j = 1 To xclapp.ActiveCell.SpecialCells(11).Column
If j = 1 Then Asset = xclsht.Cells(i, j).Value
If j = 2 Then Company = xclsht.Cells(i, j).Value
If j = 3 Then Zmiana = xclsht.Cells(i, j).Value
Next
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/n as02"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtANLA-ANLN1").Text = Asset
session.findById("wnd[0]/usr/ctxtANLA-BUKRS").Text = Company
session.findById("wnd[0]/usr/ctxtANLA-BUKRS").SetFocus
session.findById("wnd[0]/usr/ctxtANLA-BUKRS").caretPosition = 4
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/subTABSTRIP:SAPLATAB:0100/tabsTABSTRIP100/tabpTAB01/ssubSUBSC:SAPLATAB:0200/subAREA1:SAPLAIST:1140/txtANLA-TXA50").Text = Zmiana
session.findById("wnd[0]/usr/subTABSTRIP:SAPLATAB:0100/tabsTABSTRIP100/tabpTAB01/ssubSUBSC:SAPLATAB:0200/subAREA1:SAPLAIST:1140/txtANLA-TXA50").SetFocus
session.findById("wnd[0]/usr/subTABSTRIP:SAPLATAB:0100/tabsTABSTRIP100/tabpTAB01/ssubSUBSC:SAPLATAB:0200/subAREA1:SAPLAIST:1140/txtANLA-TXA50").caretPosition = 3
session.findById("wnd[0]/tbar[0]/btn[11]").press

Next
MsgBox "All " & CStr(xclapp.ActiveCell.SpecialCells(11).Row - 1) & " Excel rows have been processed."

Set xclwbk = Nothing
Set xclsht = Nothing
xclapp.Quit
Set xclapp = Nothing

End Sub

[script_man]

Hi MJ MJ,

the script architecture I know from somewhere...

1. The first question I do not understand correctly. In an Excel sheet are processed up to 1'048'576 rows. Do you want to process the data from multiple Excel sheets?

2. If the SAP messages appear in the status line, you could use this as follows.

for example:

. . .

session.findById("wnd[0]/tbar[0]/btn[11]").press

xclsht.Cells(i, 4).Value = session.findById("wnd[0]/sbar").text

next

. . .

Regards,

ScriptMan

[Former Member]

HI ScriptMan, 

Thanks for answer

For the first question - please have a look below how my excell sheet look like for now.

What I would like to do is just to be able to process the data up to required number of rows. 

So if I need to change the 200 assets I will put them starting from cell A3 to  A203 - then fill in also "Comapny code" and fill column "Zmiana"  - which in my case represent the change of one of the field in asset master data. Then I can run the macro and see the  SAP mesagge for the every particluar line - (it can happened that for some or other reason change will not be executed for some lines )  - Hope it is a little bit clear now for you.

For the second question I believe that you now know what I need to get.  In screen below you see column SAP Output message - and I want to have the SAP mesagge there after each line is processed.

For your refferance I attached the screen how this SAP message look like.

[script_man]

Hi MJ MJ,

1. Please try out the following:

. . .

Set xclsht = xclwbk.Sheets("Sheet1")

i_min = inputbox("Enter the number of first line:","first line")

i_max = inputbox("Enter the number of last line:","last line")

if i_min <> 0 and i_max <> 0 then

For i = i_min To i_max

For j = 1 To xclapp.ActiveCell.SpecialCells(11).Column

. . .

MsgBox "All " & CStr(xclapp.ActiveCell.SpecialCells(11).Row - 1) & " Excel rows have been processed."

End if

Set xclwbk = Nothing

. . .

2.

. . .

session.findById("wnd[0]/tbar[0]/btn[11]").press

on error resume next

xclsht.Cells(i, 4).Value = session.findById("wnd[0]/sbar").text

on error goto 0

next

. . .

If there occurs an error during script execution, you have to see it first to decide what to do.

Regards,

ScriptMan

[Former Member]

Hello Script Man,

Thank you...that was very helpfull answer !!! 

..but I have got still few remarks....

Let's concentrate first on question 1. 

I have adapted the script and it works fantastic - this is what I expected 🙂   - I can define as many rows to procces as required ...but if there is an error in one line scripts stops and do not continue. Is there any quick solution for it ?   

With referenace to second question I can not see the SAP message in Excell fie. I have used below for it - maybe I did sth wrong ?

could you please have a look ?

.............................

session.findById("wnd[0]/usr/subTABSTRIP:SAPLATAB:0100/tabsTABSTRIP100/tabpTAB01/ssubSUBSC:SAPLATAB:0200/subAREA1:SAPLAIST:1140/txtANLA-TXA50").caretPosition = 3
session.findById("wnd[0]/tbar[0]/btn[11]").press

On Error Resume Next
xclsht.Cells(i, 4).Value = session.findById("wnd[0]/sbar").Text
On Error GoTo 0

Next
MsgBox "All " & CStr(xclapp.ActiveCell.SpecialCells(11).Row - 1) & " Excel rows have been processed."

End If

Set xclwbk = Nothing
Set xclsht = Nothing
xclapp.Quit
Set xclapp = Nothing

End Sub

[script_man]

Hi MJ MJ,

1. Whenever an error occurs, an error message from Windows Script Host is reported. Take a close look at the faulty line.

2. It could be that the script has to wait a little until the message appears.

. . .

session.findById("wnd[0]/tbar[0]/btn[11]").press

wscript.sleep 1000

On Error Resume Next
xclsht.Cells(i, 4).Value = session.findById("wnd[0]/sbar").Text
On Error GoTo 0

Next

. . .

Regards,

ScriptMan

[Former Member]

HI Script Man,

1. Really easy one,  should not bother you about that...

2. There  is sth strange with it - when I run the script  I see no sap messages despite the fact that all assets have been processed , but afterwards I save the file and then try to open it. System ask me to save it (??), so I save it under diffrent name and when open all  the SAP messages are visible  - can this be somehow explained ?

Thanks & regards

MJ MJ

[script_man]

Hi MJ MJ,

then you did accomplished already a lot. You could still try the following.

for example:

. . .

MsgBox "All " & CStr(imax - imin + 1) & " Excel rows have been processed."

End If

xclapp.ScreenUpdating = true

xclapp.ActiveWorkbook.Save

Set xclwbk = Nothing

. . .

Regards,

ScriptMan

[Former Member]

This message was moderated.