AW: VBA - Unterschieliche Formate Code vs Mouse
27.03.2019 19:50:35
Arnd-Olav
Hier der ganze Code, na da bin ich jetzt aber gespannt ;)
Sub DCO_EXTRACT_PREQs()
Dim sPath As String
Dim WSui As Worksheet
Set WSui = Workbooks("BS_TOOL_UI.xlsm").Worksheets("Tabelle1")
sPath = Workbooks("BS_TOOL_UI.xlsm").Worksheets("Tabelle1").Cells(3, 9)
Application.DisplayAlerts = False
WSui.Range("C2") = Now
ActiveWorkbook.Application.Calculate
Application.DisplayAlerts = False
If Not IsObject(SAPGuiApp) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set SAPGuiApp = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
Set Connection = SAPGuiApp.Children(0)
End If
If Not IsObject(SAP_Session) Then
Set SAP_Session = Connection.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject SAP_Session, "on"
WScript.ConnectObject SAPGuiApp, "on"
End If
SAP_Session.findById("wnd[0]").Maximize 'maximise window to _
screen
SAP_Session.findById("wnd[0]/tbar[0]/okcd").Text = "/nZM_DCO_MATERIALS" 'transaction field _
input ZM_DCO_MATERIALS
SAP_Session.findById("wnd[0]").sendVKey 0 'Enter
SAP_Session.findById("wnd[0]").sendVKey 17 'SHIFT-F5
SAP_Session.findById("wnd[1]/usr/txtV-LOW").Text = "/PROC_POG_PREQ" ' * * * * * Input _
in Variant Name * * * * *
SAP_Session.findById("wnd[1]/usr/ctxtENVIR-LOW").Text = "" 'empty Environment _
for variant field
SAP_Session.findById("wnd[1]/usr/txtENAME-LOW").Text = "" 'delete any User _
Name
SAP_Session.findById("wnd[1]/usr/txtAENAME-LOW").Text = "" 'delete any User _
Name
SAP_Session.findById("wnd[1]/usr/txtMLANGU-LOW").Text = "" 'empty Language Key
SAP_Session.findById("wnd[1]/tbar[0]/btn[8]").press 'Enter
SAP_Session.findById("wnd[0]").sendVKey 8 'F8
SAP_Session.findById("wnd[1]/usr/btnBUTTON_1").press 'Enter in _
continuing window
SAP_Session.findById("wnd[0]/shellcont/shell").pressToolbarButton "&MB_VARIANT" _
'Select Toolbar LAYOUT
SAP_Session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/ _
shellcont/shell").currentCellRow = -1 'Select CHOOSE LAYOUT
SAP_Session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/ _
shellcont/shell").selectColumn "VARIANT" 'Select First Column
SAP_Session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/ _
shellcont/shell").contextMenu
SAP_Session.findById("wnd[1]/usr/ssubD0500_SUBSCREEN:SAPLSLVC_DIALOG:0501/cntlG51_CONTAINER/ _
shellcont/shell").selectContextMenuItem "&FILTER" 'Select FILTER
SAP_Session.findById("wnd[2]/usr/ssub%_SUBSCREEN_FREESEL:SAPLSSEL:1105/ctxt%%DYN001-LOW").Text = _
"/PROC_POG" 'Input /PROC_POG
SAP_Session.findById("wnd[2]").sendVKey 0 _
'Enter
SAP_Session.findById("wnd[1]").sendVKey 0 _
'Enter
SAP_Session.findById("wnd[0]/shellcont/shell").pressToolbarContextButton "&MB_EXPORT" _
'Select Toolbar EXPORT
SAP_Session.findById("wnd[0]/shellcont/shell").selectContextMenuItem "&PC"
SAP_Session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/ _
radSPOPLI-SELFLAG[1,0]").Select
SAP_Session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/ _
radSPOPLI-SELFLAG[1,0]").SetFocus
SAP_Session.findById("wnd[1]").sendVKey 0 _
'Enter
SAP_Session.findById("wnd[1]/usr/ctxtDY_PATH").Text = sPath 'Input SavePath
SAP_Session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "PREQs_EXTRACT.xls" 'Input _
Filename
SAP_Session.findById("wnd[1]").sendVKey 11 'Replace
WSui.Range("C3") = Now
ActiveWorkbook.Application.Calculate
Workbooks.Open sPath & "PREQs_EXTRACT.xls"
If WorksheetFunction.CountA(ActiveSheet.Range("B:B")) > 1 Then
With Workbooks("PREQs_EXTRACT.xls").Worksheets("PREQs_EXTRACT")
Dim rngCC As Range
Application.ScreenUpdating = False
For Each rngCC In Range(Cells(6, 2), Cells(1000, 2)).SpecialCells(xlCellTypeConstants)
Range(rngCC, Cells(rngCC.Row, Columns.Count).End(xlToLeft)).Cut _
Cells(rngCC.Row - 1, 27).Offset(, 0)
Next
End With
Else
End If
Workbooks("PREQs_EXTRACT.xls").Worksheets("PREQs_EXTRACT").Range("A:B, E:E, H:H, L:L, T:U, W:Y, _
AC:AD").Delete
Workbooks("PREQs_EXTRACT.xls").Worksheets("PREQs_EXTRACT").Rows("1:4").Delete
Workbooks("PREQs_EXTRACT.xls").Worksheets("PREQs_EXTRACT").Rows("2:2").Delete
Cells(1, 1).Value = "PRIO"
Cells(1, 2).Value = "ICAT"
Cells(1, 3).Value = "SALESO"
Cells(1, 4).Value = "SALESD"
Cells(1, 5).Value = "CUST"
Cells(1, 6).Value = "RDATE"
Cells(1, 7).Value = "PREQ"
Cells(1, 8).Value = "FVSL"
Cells(1, 9).Value = "FVPREQ"
Cells(1, 10).Value = "MATERIAL"
Cells(1, 11).Value = "MATGROUP"
Cells(1, 12).Value = "QTY"
Cells(1, 13).Value = "ORDQTY"
Cells(1, 14).Value = "UOM"
Cells(1, 15).Value = "PO"
Cells(1, 16).Value = "FIRSTLINE"
Cells(1, 17).Value = "SOTEXT"
Cells(1, 18).Value = "SLT"
Cells(1, 19).Value = "PGR"
Cells(1, 20).Value = "PREQAOGP"
Cells(1, 21).Value = "PNWOCC"
Sheets("PREQs_EXTRACT").Range("U2:U300").FormulaLocal = "=WENN(J20;Teil(J2;1;Länge(J2)-6);0)"
With Tabelle1
Range("A1:AG1000").Sort Key1:=Range("G1"), Order1:=xlDescending, _
Header:=xlYes, MatchCase:=True
End With
Workbooks("PREQs_EXTRACT.xls").SaveAs sPath & "preqs.xlsx", FileFormat:=51
Workbooks("preqs.xlsx").Save
Workbooks("preqs.xlsx").Close savechanges = True
'Call EXTRACT_Z_PNBC
Application.DisplayAlerts = True
End Sub
