Object is not created in VBA

[Former Member]

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

[holger_khn · Accepted solution]

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

[Former Member · Accepted solution]

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