Skip to Content
author's profile photo Former Member
Former Member

Object is not created in VBA

Hello All,

I encountered a problem with my VBA code. Basically what I am trying to do is to change the posting date of parked documents in SAP by VBA code run from Excel. I created the original code about one and a half years ago. It works fine assuming that the user does not switch on the legend for the FBL1N T-code. Now I trying to develop the code further and tackle this issue. The basic problem is that the code navigates in the SAP user area by absolute addresses (explicit column and row numbers, with other words, 22 characters from left and 5 from top). The procedure requires to sort the report first and then click in each document. That's why the legend makes the trouble because it adds two rows to the top and then the sort does not work (instead of selecting a column header field for sorting it selects a blank screen field).

Now, I see two options to handle the matter: a, enhance the code to loop through all screen fields and determine where are the header fields or b, acquire user setting and swith off legend temporarily. Currently I am trying work out the first approach as I have no clue is it possible to get user settings.

I have inserted the macro itself and printscreens to understand the problem. The SAP FBL1N picture shows the report on screen. I would like to find the red marked field (the Doc.no) in my VBA collection. See VBA editor pic. The object named "Userarea" are created properly. This is the collection of all fields displayed on the SAP screen of user area. However I cannot access any member of this collection. This command: Set Screenfield = UserArea.Item(i) should work (as it does create the object from a member of a collection in other non-SAP related cases where I use this command in VBA).

I hope somebody can help me.

Thank you very much

Sandor

Ps:

the VBA code:

Sub Main()

Dim PtDate As Date, cocode As String, docno As Long, doc As Variant, Msg As Integer, endtext As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False

again2:
On Error Resume Next
PtDate = Application.InputBox("Pls Enter", "Posting date?", Left(Now(), 10))
If PtDate < Now() - 1 Then
Msg = MsgBox("You cannot give an earlier date than today. Please try again. Thank you.", vbOKCancel, "Error encountered"):
If Msg = vbCancel Then Exit Sub Else GoTo again2:
End If

again:
On Error Resume Next
cocode = InputBox("Please enter company code", "Which Company code to run on?")

On Error Resume Next
Msg = CInt(cocode)
If Err.Number <> 0 Then Msg = MsgBox("Not valid number. Try again?", vbOKCancel, "Error encountered"): If Msg = vbCancel Then Exit Sub Else GoTo again:

If Err.Number <> 0 Or Len(cocode) > 4 Or Len(cocode) < 3 Then
Msg = MsgBox("Not valid number. Try again?", vbOKCancel, "Error encountered")
If Msg = vbCancel Then Exit Sub Else GoTo again:
End If

Dim SapGuiAuto As Object, SapApp As Object, SapConn As Object, SapSession As Object, wnd As Object, wnd2 As Object, Sbar As Object
Dim UserArea As Object, Screenfield As Object, term As String

Set SapGuiAuto = GetObject("SAPGUI")
Set SapApp = SapGuiAuto.GetScriptingEngine
Set SapConn = SapApp.Children(0)
If SapConn Is Nothing Then
Msg = MsgBox("Please login to SAP, then press Ok", vbOKCancel, "SAP not started")
If Msg = vbCancel Then Exit Sub
Set SapConn = SapApp.Children(0)
End If
Set SapSession = SapConn.Children(0)
Set wnd = SapSession.findById("wnd[0]")
Set Sbar = SapSession.findById("wnd[0]/sbar")

Txt = "wnd[0]/usr/subHEADER_AND_ITEMS:SAPLMR1M:6005/tabsHEADER/tabpHEADER_TOTAL/ssubHEADER_SCREEN:SAPLFDCB:0010/ctxtINVFO-BUDAT"

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

SapSession.findById("wnd[0]/tbar[0]/okcd").Text = "/nfbl1n"
wnd.sendVKey 0

SapSession.findById("wnd[0]/usr/chkX_NORM").Selected = False
SapSession.findById("wnd[0]/usr/chkX_SHBV").Selected = False
SapSession.findById("wnd[0]/usr/chkX_MERK").Selected = False
SapSession.findById("wnd[0]/usr/chkX_APAR").Selected = False
SapSession.findById("wnd[0]/usr/chkX_PARK").Selected = True
SapSession.findById("wnd[0]/usr/radX_AISEL").Select

SapSession.findById("wnd[0]/usr/ctxtKD_LIFNR-LOW").Text = "1000000"
SapSession.findById("wnd[0]/usr/ctxtKD_LIFNR-HIGH").Text = "9999999"
SapSession.findById("wnd[0]/usr/ctxtKD_BUKRS-LOW").Text = cocode
SapSession.findById("wnd[0]/usr/ctxtSO_BUDAT-LOW").Text = "010112"
SapSession.findById("wnd[0]/usr/ctxtSO_BUDAT-HIGH").Text = Convdate(Day(Now())) & Convdate(Month(Now() - 1)) & Right(Year(Now() - 1), 2) ' Convdate(Day(Now())) & Convdate(Month(Now())) & Right(Year(Now()) + 1, 2)
SapSession.findById("wnd[0]/usr/ctxtPA_VARI").Text = "/WMC."

wnd.sendVKey 8
st = Sbar.Text
If Right(Sbar.Text, 15) = "items displayed" Then endtext = "All selected docs changed": noofit = CInt(Left(Sbar.Text, Len(Sbar.Text) - 15))
If Left(Sbar.Text, 17) = "No items selected" Then endtext = "No items selected": GoTo finished

Set UserArea = Nothing
Set UserArea = SapSession.findById("wnd[0]/usr").Children

' THIS IS THE CODE USED EARLIER. IT SELECTS ONE OF THE HEADER FIELDS (I.E. THE DOC.NO AS THE MACRO SETS A SPECIFIC LAYOUT AS WELL)
'SapSession.findById("wnd[0]/usr/lbl[22,5]").SetFocus
'wnd.sendVKey 2
' AND THEN SORTS THE REPORT
'SapSession.findById("wnd[0]/tbar[1]/btn[41]").press

j = 0
For i = 0 To UserArea.Count

'Line = "wnd[0]/usr/lbl[22," & j & "]"
st = CStr(UserArea.Item(i).Text)

Set Screenfield = UserArea.Item(i)

If UserArea.Item(i).displayText = "DocumentNo" Then j = UserArea.Item(i).CharLeft
If j > 0 Then
On Error Resume Next

SapSession.findById("wnd[0]/usr/lbl[" & j & "," & j & "]").SetFocus
SapSession.findById("wnd[0]").sendVKey 2
SapSession.findById("wnd[0]/mbar/menu[5]/menu[1]").Select
SapSession.findById("wnd[0]/mbar/menu[1]/menu[0]").Select

'Set doc = SapSession.findById("wnd[0]/usr/tabsTS/tabpMAIN/ssubPAGE:SAPLFDCB:0010/txtINVFO-BELNR")
SapSession.findById("wnd[1]/tbar[0]/btn[0]").press
docno = 0
docno = CLng(SapSession.findById("wnd[0]/usr/tabsTS/tabpMAIN/ssubPAGE:SAPLFDCB:0010/txtINVFO-BELNR").displayedText)

If Left(docno, 2) = 19 Or Left(docno, 2) = 17 Then

' KRdoc
'SapSession.findById("wnd[0]").sendVKey 2
SapSession.findById("wnd[0]/mbar/menu[0]/menu[0]").Select
SapSession.findById("wnd[0]/usr/tabsTS/tabpMAIN/ssubPAGE:SAPLFDCB:0010/ctxtINVFO-BUDAT").Text = Convdate(Day(PtDate)) & Convdate(Month(PtDate)) & Right(Year(PtDate), 2)
SapSession.findById("wnd[0]/mbar/menu[0]/menu[5]").Select
If Sbar.messagetype = "W" Then wnd.sendVKey 0 ' posting period adjusted
SapSession.findById("wnd[1]/tbar[0]/btn[0]").press ' translation date adjusted
wnd.sendVKey 0 ' translation date adjusted
If Sbar.messagetype = "W" Then wnd.sendVKey 0 ' if invoice with doc exists
If Sbar.messagetype = "W" Then wnd.sendVKey 0 ' net due date in the past
' end KR
End If

If Left(docno, 2) = 0 Then
' RE doc
SapSession.findById(Txt).Text = Convdate(Day(PtDate)) & Convdate(Month(PtDate)) & Right(Year(PtDate), 2)
SapSession.findById("wnd[0]").sendVKey 0
st = Sbar.Text
If Sbar.messagetype = "W" Then wnd.sendVKey 0 ' posting period adjusted

st = Sbar.Text
SapSession.findById("wnd[0]").sendVKey 0 ' translation date adjusted
SapSession.findById("wnd[0]").sendVKey 0
If Sbar.messagetype = "W" Then wnd.sendVKey 0 ' net due date in the past
If Sbar.messagetype = "E" Then Msg = MsgBox("Please resolve in SAP, then press Ok to continue with the next item", vbOKOnly, "Error message came up in SAP")

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

If Sbar.Text = "Invoice document still contains messages" Then
Msg = MsgBox("Please resolve in SAP, then press Ok to continue with the next item", vbOKOnly, "Error message came up in SAP")
Else: SapSession.findById("wnd[0]/tbar[0]/btn[3]").press
End If

On Error Resume Next
Set wnd2 = SapSession.findById("wnd[1]")
' end RE
End If

End If

'j = j + 1

Next i

finished:

Set SapGuiAuto = Nothing: Set SapApp = Nothing: Set SapConn = Nothing: Set SapSession = Nothing: Set wnd = Nothing: Set wnd2 = Nothing: Set Sbar = Nothing:
Set UserArea = Nothing: Set Screenfield = Nothing:

MsgBox endtext, vbOKOnly

End Sub

Sub Pagedown(SapSession)

SapSession.findById("wnd[0]").sendVKey 82

End Sub

Private Function Convdate(dval As Integer) As String

If Len(CStr(dval)) = 2 Then Convdate = dval Else Convdate = "0" & dval

End Function

The SAP screen:

the VBA editor

FBL1N.png (55.4 kB)
VBA editor.png (246.6 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Jun 03, 2014 at 09:28 PM


    Hi Sandor.

    There are several options to achieve what you need.

    Set UserArea = Session.FindById("wnd[0]/usr/").Children
    MsgBox Session.FindById("wnd[0]/usr/").VerticalScrollbar.Maximum
    b = 2
    For iCounter = 1 To (UserArea.Count - 1)
    If UserArea.Item(iCounter - 1).Text = "DocumentNo" Then
    Range("A" & b).Value = CStr(UserArea.Item(iCounter - 1).Text)
    Range("B" & b).Value = CStr(UserArea.Item(iCounter - 1).ID)
    b = b + 1
    End If
    Next

    When you go in result list to 'Settings' => 'Switch List' the result list will Switch to Shell ALV grid. This can be read much easier. So may you can use it. If you need more explaination please clarify your requirements. Thanks.

    Holger

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Holger,

      thanks for your answer. I have also found this in the meantime.

      Just one note, for me because of whatever reason the direct comparison does not work. I could not use this

      If UserArea.Item(iCounter - 1).Text = "DocumentNo" Then

      I needed a variable and read the member attribute into it. Then I could compare and the IF statement worked right (lines 4-6).

      Another landmine was that I have declared i as Integer. It must be coverted to Long for indexing a member (lines 5,7,8).

      1 Set usr = SapSession.findById("wnd[0]/usr/")

      2 Set usr = usr.Children

      3 For i = 1 To usr.Count

      4 docno = ""

      5 docno = usr.Item(CLng(i)).Text

      6 If docno = "DocumentNo" Then

      7 j = CInt(usr.Item(CLng(i)).CharTop) + 2: chrleft = usr.Item(CLng(i)).CharLeft

      8 SapSession.findById("wnd[0]/usr/lbl[" & usr.Item(CLng(i)).CharLeft & "," & usr.Item(CLng(i)).CharTop & "]").SetFocus

      9 wnd.sendVKey 2

      10 ' AND THEN SORTS THE REPORT

      11 SapSession.findById("wnd[0]/tbar[1]/btn[41]").press

      12 Exit For

      13 End If

      14 Next i

      Thanks anyway.

      Kind regards,

      Sandor

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.