G2A Many GEOs
SHARE
TWEET

Untitled

a guest Apr 10th, 2020 246 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Sub SCF()
  2.  
  3. If Not IsObject(xxxx) Then
  4.    Set SapGuiAuto = GetObject("SAPGUI")
  5.    Set xxxx = SapGuiAuto.GetScriptingEngine
  6. End If
  7. If Not IsObject(Connection) Then
  8.    Set Connection = xxxx.Children(0)
  9. End If
  10. If Not IsObject(session) Then
  11.    Set session = Connection.Children(0)
  12. End If
  13. If IsObject(wscript) Then
  14.    wscript.ConnectObject session, "on"
  15.    wscript.ConnectObject xxxx, "on"
  16. End If
  17.  
  18. Dim myPath As String: myPath = ThisWorkbook.Path
  19. Dim vendors As Workbook
  20. Dim source As Worksheet
  21. Dim Names As Worksheet
  22. Dim VenName As String
  23. Dim VenNumber As String
  24. Dim VenTerms As String
  25. Dim docNumber As String
  26. Dim myDate As Date
  27. myDate = Date
  28.  
  29. Application.DisplayAlerts = False
  30. Application.ScreenUpdating = False
  31.  
  32.  
  33.  '------slownik z duplikatami-----'
  34. basePath = ThisWorkbook.Path + "\docBase.txt"
  35. Set objFSO = CreateObject("Scripting.FileSystemObject")
  36. Dim duplicates As Scripting.Dictionary
  37. Set duplicates = New Scripting.Dictionary
  38. Dim Line As String
  39.  
  40. Set baseFile = objFSO.OpenTextFile(basePath, 1)
  41.     Do While Not baseFile.AtEndOfStream
  42.         Line = baseFile.ReadLine
  43.         duplicates.Add Key:=Line, Item:=""
  44.     Loop
  45. baseFile.Close
  46.  
  47.  '------slownik z wyjatkami-----'
  48. Dim exceptPath As Workbook
  49.  Set exceptPath = Workbooks.Open(ThisWorkbook.Path + "\SCF_Exceptions.xlsx")
  50.  Dim exceptSheet As Worksheet
  51.  Set exceptSheet = exceptPath.Worksheets(1)
  52.  Dim Exceptions As Scripting.Dictionary
  53.  Set Exceptions = New Scripting.Dictionary
  54.  
  55.     With exceptSheet
  56.         lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
  57.     End With
  58.    
  59.     If lastRow > 1 Then
  60.         For rowNr = 2 To lastRow
  61.             Exceptions.Add Key:=CStr(exceptSheet.Cells(rowNr, 2).Value), Item:=CStr(exceptSheet.Cells(rowNr, 3).Value)
  62.             'MsgBox (Exceptions("200079652"))
  63.        Next rowNr
  64.     End If
  65.    
  66.  'MsgBox exceptSheet.Cells(3, 1).Value
  67.  
  68.  '-----sprawdzanie formatu daty------
  69. session.findById("wnd[0]/tbar[0]/okcd").Text = "/nsu3"
  70. session.findById("wnd[0]").sendVKey 0
  71. session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpDEFA").Select
  72. dateFormat = Left(session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpDEFA/ssubMAINAREA:SAPLSUID_MAINTENANCE:1105/cmbSUID_ST_NODE_DEFAULTS-DATFM").Text, 10)
  73. myDate = Format(myDate, dateFormat)
  74.  
  75.  
  76. '-----sciaganie numerow vendorow-----
  77. session.findById("wnd[0]").maximize
  78. session.findById("wnd[0]/tbar[0]/okcd").Text = "/nse16"
  79. session.findById("wnd[0]").sendVKey 0
  80. session.findById("wnd[0]/usr/ctxtDATABROWSE-TABLENAME").Text = "LFB1"
  81. session.findById("wnd[0]").sendVKey 0
  82. session.findById("wnd[0]/usr/ctxtI2-LOW").Text = "IP50"
  83. On Error Resume Next
  84. session.findById("wnd[0]/usr/txtI9-LOW").Text = "F"
  85. session.findById("wnd[0]/usr/txtI8-LOW").Text = "F"
  86. session.findById("wnd[0]/usr/txtI7-LOW").Text = "F"
  87. session.findById("wnd[0]/usr/txtI6-LOW").Text = "F"
  88. On Error GoTo 0
  89. session.findById("wnd[0]/usr/ctxtLIST_BRE").Text = ""
  90. session.findById("wnd[0]/usr/txtMAX_SEL").Text = ""
  91. session.findById("wnd[0]/tbar[1]/btn[8]").press
  92. Err.Clear
  93. On Error Resume Next
  94. session.findById("wnd[0]/tbar[1]/btn[45]").press
  95. session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
  96. If Err.Number <> 0 Then
  97.     session.findById("wnd[0]/mbar/menu[6]/menu[5]/menu[2]/menu[1]").Select
  98.     session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
  99. End If
  100. On Error GoTo 0
  101. session.findById("wnd[1]/tbar[0]/btn[0]").press
  102. session.findById("wnd[1]/usr/ctxtDY_PATH").Text = myPath
  103. session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "vendorsSCF.xls"
  104. session.findById("wnd[1]/tbar[0]/btn[11]").press
  105.  
  106. Workbooks.Open (myPath & "\vendorsSCF.xls")
  107. Set vendors = ActiveWorkbook
  108. Set shVendors = vendors.Worksheets(1)
  109. col = shVendors.Cells.Find("Vendor").Column
  110. lastR = shVendors.UsedRange.Rows(shVendors.UsedRange.Rows.Count).Row
  111. shVendors.Range(Cells(1, col), Cells(lastR, col)).Copy
  112.  
  113.  
  114. '-----generowanie raportu w fbl1n-----
  115. session.findById("wnd[0]/tbar[0]/okcd").Text = "/nfbl1n"
  116. session.findById("wnd[0]").sendVKey 0
  117. session.findById("wnd[0]/usr/btn%_KD_LIFNR_%_APP_%-VALU_PUSH").press
  118. session.findById("wnd[1]/tbar[0]/btn[16]").press
  119. session.findById("wnd[1]/tbar[0]/btn[24]").press
  120. session.findById("wnd[1]/tbar[0]/btn[8]").press
  121. session.findById("wnd[0]/usr/ctxtKD_BUKRS-LOW").Text = "IP50"
  122. session.findById("wnd[0]/usr/ctxtPA_VARI").Text = "PRT_SUA"
  123. session.findById("wnd[0]/usr/txtPA_NMAX").Text = ""
  124. session.findById("wnd[0]/usr/radX_OPSEL").Select
  125. session.findById("wnd[0]/usr/ctxtPA_STIDA").Text = myDate
  126. session.findById("wnd[0]/tbar[1]/btn[8]").press
  127. session.findById("wnd[0]/mbar/menu[6]/menu[5]/menu[2]/menu[1]").Select
  128. session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
  129. session.findById("wnd[1]/tbar[0]/btn[0]").press
  130. session.findById("wnd[1]/usr/ctxtDY_PATH").Text = myPath
  131. session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "sourceSCF.xls"
  132. session.findById("wnd[1]/tbar[0]/btn[11]").press
  133.  
  134.  
  135. '-----zmiana sortowania i sumowania W SAPie-----
  136. session.findById("wnd[0]/tbar[1]/btn[25]").press
  137. session.findById("wnd[1]/usr/btnAPP_FL_ALL").press
  138. session.findById("wnd[1]/usr/btnB_SEARCH").press
  139. session.findById("wnd[2]/usr/txtGD_SEARCHSTR").Text = "vendor"
  140. session.findById("wnd[2]/tbar[0]/btn[0]").press
  141. session.findById("wnd[1]/usr/btnAPP_WL_SING").press
  142. session.findById("wnd[1]/usr/tblSAPLSKBHTC_WRITE_LIST_820/chkGT_WRITE_LIST-SUBTOT[3,0]").Selected = False
  143. session.findById("wnd[1]/tbar[0]/btn[0]").press
  144.  
  145.  
  146. '-----obrobka pliku zrodlowego-----
  147. Workbooks.Open (myPath & "\sourceSCF.xls")
  148. Set sourceWbook = ActiveWorkbook
  149. Set source = sourceWbook.Worksheets(1)
  150. sourceWbook.Sheets.Add After:=sourceWbook.Sheets(sourceWbook.Sheets.Count)
  151. sourceWbook.Sheets(sourceWbook.Sheets.Count).Name = "Names"
  152. Set Names = sourceWbook.Sheets("Names")
  153. source.Activate
  154.  
  155. lastRow = source.UsedRange.Rows(source.UsedRange.Rows.Count).Row
  156. rowNr = source.Cells.Find("Company Code").Row - 1
  157. colNr = source.Cells.Find("Company Code").Column
  158.  
  159. If colNr > 1 Then
  160. For i = 1 To (colNr - 1)
  161.     source.Columns(1).Delete
  162. Next
  163. End If
  164.  
  165. vendorColNr = 2
  166. Do Until source.Cells(rowNr, vendorColNr).Value <> ""
  167.     vendorColNr = vendorColNr + 1
  168. Loop
  169.  
  170.  
  171. '-----kopiowanie nazw vendorow do arkusza Names-----
  172. Application.CutCopyMode = False
  173. source.Rows(1).Insert
  174. source.Range(Cells(1, 1), Cells(lastRow, 1)).AutoFilter Field:=1, Criteria1:="Vendor"
  175. source.Range(Cells(2, vendorColNr), Cells(lastRow, vendorColNr)).SpecialCells(xlCellTypeVisible).Copy Names.Range("A2")
  176. source.Range(Cells(1, 1), Cells(lastRow, 1)).AutoFilter Field:=1, Criteria1:="Name"
  177. source.Range(Cells(2, vendorColNr), Cells(lastRow, vendorColNr)).SpecialCells(xlCellTypeVisible).Copy Names.Range("B2")
  178. source.Columns(1).AutoFilter
  179. '--------------------------------------------------
  180.  
  181. rowNr = source.Cells.Find("Document").Row
  182. source.Rows("1:" & rowNr - 1).Delete
  183.  
  184. i = 1
  185. Do Until i = 9
  186.     If source.Cells(1, i).Value = "" Then
  187.         source.Columns(i).Delete
  188.     Else
  189.         i = i + 1
  190.     End If
  191. Loop
  192.  
  193. source.Columns(1).AutoFilter Field:=1, Criteria1:="", Criteria2:="Account*", Operator:=xlOr
  194. If source.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 0 Then
  195.     source.Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible).Delete
  196. End If
  197. source.Columns(1).AutoFilter Field:=1, Criteria1:="Vendor"
  198. If source.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 0 Then
  199.     source.Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible).Delete
  200. End If
  201. source.Columns(1).AutoFilter
  202. source.Columns(4).AutoFilter Field:=1, Criteria1:="9999"
  203. If source.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 0 Then
  204.     source.Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible).Delete
  205. End If
  206. source.Columns(4).AutoFilter
  207. source.Columns(6).AutoFilter Field:=1, Criteria1:="A"
  208. If source.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 0 Then
  209.     source.Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible).Delete
  210. End If
  211. source.Columns(6).AutoFilter
  212. lastRow = source.UsedRange.Rows.Count
  213. source.Range("J2: J" & lastRow).Formula = "=VLOOKUP(A2,Names!A:B,2,FALSE)"
  214.  
  215.  
  216. '-----przejscie przez wszystkie linie w pliku-----
  217. j = 2
  218. Do Until j > lastRow
  219.    
  220.     VenName = source.Cells(j, 10).Value
  221.     VenNumber = source.Cells(j, 1).Value
  222.     docNumber = source.Cells(j, 2).Value
  223.    
  224.     If duplicates.Exists(docNumber) = False And source.Cells(j, 9).Value = "" Then
  225.    
  226.         VenTerms = CStr(source.Cells(j, 4).Value)
  227.             If Len(VenTerms) = 1 Then
  228.                 VenTerms = "000" & VenTerms
  229.             ElseIf Len(VenTerms) = 2 Then
  230.                 VenTerms = "00" & VenTerms
  231.             ElseIf Len(VenTerms) = 3 Then
  232.                 VenTerms = "0" & VenTerms
  233.             End If
  234.         'Set baseFile = objFSO.OpenTextFile(basePath, 8)
  235.        '    baseFile.WriteLine docNumber
  236.        'baseFile.Close
  237.    
  238.             'If VenName Like "PROTOCALL*" Then
  239.            If Exceptions.Exists(VenNumber) = True Then
  240.  
  241.             'End If
  242.  
  243.                    
  244.                 'If source.Cells(j, 4).Value <> "18" Then
  245.                If VenTerms <> Exceptions(VenNumber) Then
  246.                     session.findById("wnd[0]/tbar[0]/btn[71]").press
  247.                     session.findById("wnd[1]/usr/txtRSYSF-STRING").Text = docNumber
  248.                     session.findById("wnd[1]/tbar[0]/btn[0]").press
  249.                     session.findById("wnd[2]/usr/lbl[4,2]").SetFocus
  250.                     session.findById("wnd[2]/tbar[0]/btn[2]").press
  251.                     session.findById("wnd[0]/tbar[1]/btn[44]").press
  252.                     Err.Clear
  253.                     paymentOrder = ""
  254.                     On Error Resume Next
  255.                         paymentOrder = session.findById("wnd[0]/usr/txtRF05L-TXTZA").Text
  256.                     On Error GoTo 0
  257.                    
  258.                     'Dim lol As Integer
  259.                    'lol = session.findById("wnd[0]/usr/ctxtBSEG-ZTERM").Text
  260.                    
  261.                     If paymentOrder = "Payment order created" Then
  262.                    
  263.                         Set baseFile = objFSO.OpenTextFile(basePath, 8)
  264.                         baseFile.WriteLine docNumber
  265.                         baseFile.Close
  266.                         session.findById("wnd[0]/tbar[0]/btn[3]").press
  267.                        
  268.                     Else
  269.                         session.findById("wnd[0]/usr/ctxtBSEG-ZTERM").Text = Exceptions(VenNumber)
  270.                         Do Until session.findById("wnd[0]/sbar").Text = "Changes have been saved"
  271.                             session.findById("wnd[0]/tbar[0]/btn[11]").press 'zapisywanie
  272.                        Loop
  273.                     End If
  274.                 End If
  275.                
  276.             ''konieccc wyjatkow
  277.            
  278.             Else
  279.            
  280.                 If VenTerms <> "0024" Then
  281.                
  282.                 session.findById("wnd[0]/tbar[0]/btn[71]").press
  283.                 session.findById("wnd[1]/usr/txtRSYSF-STRING").Text = docNumber
  284.                 session.findById("wnd[1]/tbar[0]/btn[0]").press
  285.                 session.findById("wnd[2]/usr/lbl[4,2]").SetFocus
  286.                 session.findById("wnd[2]/tbar[0]/btn[2]").press
  287.                 session.findById("wnd[0]/tbar[1]/btn[44]").press
  288.                 Err.Clear
  289.                 paymentOrder = ""
  290.                 On Error Resume Next
  291.                     paymentOrder = session.findById("wnd[0]/usr/txtRF05L-TXTZA").Text
  292.                 On Error GoTo 0
  293.                 If paymentOrder = "Payment order created" Then
  294.                
  295.                         Set baseFile = objFSO.OpenTextFile(basePath, 8)
  296.                         baseFile.WriteLine docNumber
  297.                         baseFile.Close
  298.                         session.findById("wnd[0]/tbar[0]/btn[3]").press
  299.                        
  300.                 'session.findById("wnd[0]/tbar[0]/btn[3]").press
  301.                Else
  302.                     session.findById("wnd[0]/usr/ctxtBSEG-ZTERM").Text = "0024"
  303.                     Do Until session.findById("wnd[0]/sbar").Text = "Changes have been saved"
  304.                         session.findById("wnd[0]/tbar[0]/btn[11]").press 'zapisywanie
  305.                    Loop
  306.                 End If
  307.                 End If
  308.             End If
  309.        
  310.     End If
  311.    
  312.         j = j + 1
  313.        
  314. Loop
  315.  
  316. sourceWbook.Close
  317. vendors.Close
  318. Application.DisplayAlerts = True
  319. Application.ScreenUpdating = True
  320. MsgBox "Done"
  321. End Sub
RAW Paste Data
Ledger Nano X - The secure hardware wallet
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top