Sub SCF()

If Not IsObject(xxxx) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set xxxx = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = xxxx.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 xxxx, "on"
End If

Dim myPath As String: myPath = ThisWorkbook.Path
Dim vendors As Workbook
Dim source As Worksheet
Dim Names As Worksheet
Dim VenName As String
Dim VenNumber As String
Dim VenTerms As String
Dim docNumber As String
Dim myDate As Date
myDate = Date

Application.DisplayAlerts = False
Application.ScreenUpdating = False


 '------slownik z duplikatami-----'
basePath = ThisWorkbook.Path + "\docBase.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim duplicates As Scripting.Dictionary
Set duplicates = New Scripting.Dictionary
Dim Line As String

Set baseFile = objFSO.OpenTextFile(basePath, 1)
    Do While Not baseFile.AtEndOfStream
        Line = baseFile.ReadLine
        duplicates.Add Key:=Line, Item:=""
    Loop
baseFile.Close

 '------slownik z wyjatkami-----'
 Dim exceptPath As Workbook
 Set exceptPath = Workbooks.Open(ThisWorkbook.Path + "\SCF_Exceptions.xlsx")
 Dim exceptSheet As Worksheet
 Set exceptSheet = exceptPath.Worksheets(1)
 Dim Exceptions As Scripting.Dictionary
 Set Exceptions = New Scripting.Dictionary

    With exceptSheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    If lastRow > 1 Then
        For rowNr = 2 To lastRow
            Exceptions.Add Key:=CStr(exceptSheet.Cells(rowNr, 2).Value), Item:=CStr(exceptSheet.Cells(rowNr, 3).Value)
            'MsgBox (Exceptions("200079652"))
        Next rowNr
    End If

 'MsgBox exceptSheet.Cells(3, 1).Value

 '-----sprawdzanie formatu daty------
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nsu3"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpDEFA").Select
dateFormat = Left(session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpDEFA/ssubMAINAREA:SAPLSUID_MAINTENANCE:1105/cmbSUID_ST_NODE_DEFAULTS-DATFM").Text, 10)
myDate = Format(myDate, dateFormat)


'-----sciaganie numerow vendorow-----
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nse16"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtDATABROWSE-TABLENAME").Text = "LFB1"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtI2-LOW").Text = "IP50"
On Error Resume Next
session.findById("wnd[0]/usr/txtI9-LOW").Text = "F"
session.findById("wnd[0]/usr/txtI8-LOW").Text = "F"
session.findById("wnd[0]/usr/txtI7-LOW").Text = "F"
session.findById("wnd[0]/usr/txtI6-LOW").Text = "F"
On Error GoTo 0
session.findById("wnd[0]/usr/ctxtLIST_BRE").Text = ""
session.findById("wnd[0]/usr/txtMAX_SEL").Text = ""
session.findById("wnd[0]/tbar[1]/btn[8]").press
Err.Clear
On Error Resume Next
session.findById("wnd[0]/tbar[1]/btn[45]").press
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
If Err.Number <> 0 Then
    session.findById("wnd[0]/mbar/menu[6]/menu[5]/menu[2]/menu[1]").Select
    session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
End If
On Error GoTo 0
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = myPath
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "vendorsSCF.xls"
session.findById("wnd[1]/tbar[0]/btn[11]").press

Workbooks.Open (myPath & "\vendorsSCF.xls")
Set vendors = ActiveWorkbook
Set shVendors = vendors.Worksheets(1)
col = shVendors.Cells.Find("Vendor").Column
lastR = shVendors.UsedRange.Rows(shVendors.UsedRange.Rows.Count).Row
shVendors.Range(Cells(1, col), Cells(lastR, col)).Copy


'-----generowanie raportu w fbl1n-----
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nfbl1n"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/btn%_KD_LIFNR_%_APP_%-VALU_PUSH").press
session.findById("wnd[1]/tbar[0]/btn[16]").press
session.findById("wnd[1]/tbar[0]/btn[24]").press
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[0]/usr/ctxtKD_BUKRS-LOW").Text = "IP50"
session.findById("wnd[0]/usr/ctxtPA_VARI").Text = "PRT_SUA"
session.findById("wnd[0]/usr/txtPA_NMAX").Text = ""
session.findById("wnd[0]/usr/radX_OPSEL").Select
session.findById("wnd[0]/usr/ctxtPA_STIDA").Text = myDate
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/mbar/menu[6]/menu[5]/menu[2]/menu[1]").Select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = myPath
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "sourceSCF.xls"
session.findById("wnd[1]/tbar[0]/btn[11]").press


'-----zmiana sortowania i sumowania W SAPie-----
session.findById("wnd[0]/tbar[1]/btn[25]").press
session.findById("wnd[1]/usr/btnAPP_FL_ALL").press
session.findById("wnd[1]/usr/btnB_SEARCH").press
session.findById("wnd[2]/usr/txtGD_SEARCHSTR").Text = "vendor"
session.findById("wnd[2]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/btnAPP_WL_SING").press
session.findById("wnd[1]/usr/tblSAPLSKBHTC_WRITE_LIST_820/chkGT_WRITE_LIST-SUBTOT[3,0]").Selected = False
session.findById("wnd[1]/tbar[0]/btn[0]").press


'-----obrobka pliku zrodlowego-----
Workbooks.Open (myPath & "\sourceSCF.xls")
Set sourceWbook = ActiveWorkbook
Set source = sourceWbook.Worksheets(1)
sourceWbook.Sheets.Add After:=sourceWbook.Sheets(sourceWbook.Sheets.Count)
sourceWbook.Sheets(sourceWbook.Sheets.Count).Name = "Names"
Set Names = sourceWbook.Sheets("Names")
source.Activate

lastRow = source.UsedRange.Rows(source.UsedRange.Rows.Count).Row
rowNr = source.Cells.Find("Company Code").Row - 1
colNr = source.Cells.Find("Company Code").Column

If colNr > 1 Then
For i = 1 To (colNr - 1)
    source.Columns(1).Delete
Next
End If

vendorColNr = 2
Do Until source.Cells(rowNr, vendorColNr).Value <> ""
    vendorColNr = vendorColNr + 1
Loop


'-----kopiowanie nazw vendorow do arkusza Names-----
Application.CutCopyMode = False
source.Rows(1).Insert
source.Range(Cells(1, 1), Cells(lastRow, 1)).AutoFilter Field:=1, Criteria1:="Vendor"
source.Range(Cells(2, vendorColNr), Cells(lastRow, vendorColNr)).SpecialCells(xlCellTypeVisible).Copy Names.Range("A2")
source.Range(Cells(1, 1), Cells(lastRow, 1)).AutoFilter Field:=1, Criteria1:="Name"
source.Range(Cells(2, vendorColNr), Cells(lastRow, vendorColNr)).SpecialCells(xlCellTypeVisible).Copy Names.Range("B2")
source.Columns(1).AutoFilter
'--------------------------------------------------

rowNr = source.Cells.Find("Document").Row
source.Rows("1:" & rowNr - 1).Delete

i = 1
Do Until i = 9
    If source.Cells(1, i).Value = "" Then
        source.Columns(i).Delete
    Else
        i = i + 1
    End If
Loop

source.Columns(1).AutoFilter Field:=1, Criteria1:="", Criteria2:="Account*", Operator:=xlOr
If source.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 0 Then
    source.Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible).Delete
End If
source.Columns(1).AutoFilter Field:=1, Criteria1:="Vendor"
If source.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 0 Then
    source.Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible).Delete
End If
source.Columns(1).AutoFilter
source.Columns(4).AutoFilter Field:=1, Criteria1:="9999"
If source.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 0 Then
    source.Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible).Delete
End If
source.Columns(4).AutoFilter
source.Columns(6).AutoFilter Field:=1, Criteria1:="A"
If source.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 0 Then
    source.Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible).Delete
End If
source.Columns(6).AutoFilter
lastRow = source.UsedRange.Rows.Count
source.Range("J2: J" & lastRow).Formula = "=VLOOKUP(A2,Names!A:B,2,FALSE)"


'-----przejscie przez wszystkie linie w pliku-----
j = 2
Do Until j > lastRow

    VenName = source.Cells(j, 10).Value
    VenNumber = source.Cells(j, 1).Value
    docNumber = source.Cells(j, 2).Value

    If duplicates.Exists(docNumber) = False And source.Cells(j, 9).Value = "" Then

        VenTerms = CStr(source.Cells(j, 4).Value)
            If Len(VenTerms) = 1 Then
                VenTerms = "000" & VenTerms
            ElseIf Len(VenTerms) = 2 Then
                VenTerms = "00" & VenTerms
            ElseIf Len(VenTerms) = 3 Then
                VenTerms = "0" & VenTerms
            End If
        'Set baseFile = objFSO.OpenTextFile(basePath, 8)
        '    baseFile.WriteLine docNumber
        'baseFile.Close

            'If VenName Like "PROTOCALL*" Then
            If Exceptions.Exists(VenNumber) = True Then

            'End If


                'If source.Cells(j, 4).Value <> "18" Then
                If VenTerms <> Exceptions(VenNumber) Then
                    session.findById("wnd[0]/tbar[0]/btn[71]").press
                    session.findById("wnd[1]/usr/txtRSYSF-STRING").Text = docNumber
                    session.findById("wnd[1]/tbar[0]/btn[0]").press
                    session.findById("wnd[2]/usr/lbl[4,2]").SetFocus
                    session.findById("wnd[2]/tbar[0]/btn[2]").press
                    session.findById("wnd[0]/tbar[1]/btn[44]").press
                    Err.Clear
                    paymentOrder = ""
                    On Error Resume Next
                        paymentOrder = session.findById("wnd[0]/usr/txtRF05L-TXTZA").Text
                    On Error GoTo 0

                    'Dim lol As Integer
                    'lol = session.findById("wnd[0]/usr/ctxtBSEG-ZTERM").Text

                    If paymentOrder = "Payment order created" Then

                        Set baseFile = objFSO.OpenTextFile(basePath, 8)
                        baseFile.WriteLine docNumber
                        baseFile.Close
                        session.findById("wnd[0]/tbar[0]/btn[3]").press

                    Else
                        session.findById("wnd[0]/usr/ctxtBSEG-ZTERM").Text = Exceptions(VenNumber)
                        Do Until session.findById("wnd[0]/sbar").Text = "Changes have been saved"
                            session.findById("wnd[0]/tbar[0]/btn[11]").press 'zapisywanie
                        Loop
                    End If
                End If

            ''konieccc wyjatkow

            Else

                If VenTerms <> "0024" Then

                session.findById("wnd[0]/tbar[0]/btn[71]").press
                session.findById("wnd[1]/usr/txtRSYSF-STRING").Text = docNumber
                session.findById("wnd[1]/tbar[0]/btn[0]").press
                session.findById("wnd[2]/usr/lbl[4,2]").SetFocus
                session.findById("wnd[2]/tbar[0]/btn[2]").press
                session.findById("wnd[0]/tbar[1]/btn[44]").press
                Err.Clear
                paymentOrder = ""
                On Error Resume Next
                    paymentOrder = session.findById("wnd[0]/usr/txtRF05L-TXTZA").Text
                On Error GoTo 0
                If paymentOrder = "Payment order created" Then

                        Set baseFile = objFSO.OpenTextFile(basePath, 8)
                        baseFile.WriteLine docNumber
                        baseFile.Close
                        session.findById("wnd[0]/tbar[0]/btn[3]").press

                'session.findById("wnd[0]/tbar[0]/btn[3]").press
                Else
                    session.findById("wnd[0]/usr/ctxtBSEG-ZTERM").Text = "0024"
                    Do Until session.findById("wnd[0]/sbar").Text = "Changes have been saved"
                        session.findById("wnd[0]/tbar[0]/btn[11]").press 'zapisywanie
                    Loop
                End If
                End If
            End If

    End If

        j = j + 1

Loop

sourceWbook.Close
vendors.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Done"
End Sub