Hi to all, iam new here and stuck atone point. Desperately need help.
I have a macro which is working only for a single transaction.
What it does is, it take value from INPUTsheet.A2 cell then runs SAP gui, SAP gui exports a excelfile(SAPTO.XLS) to temp location.
Then macro opens the file SAPTO.XLS copies full data to Sheet TO1,sorts & Save.
I want this to continues and take value from INPUTsheet.A3 and copies full data to Sheet T02 and so on...till INPUTsheet.A10 & Sheet T10.
Every time SAP Gui runs it will replace the SAPTO.XLS file.
Iam not a expert s below is rough but Working perfect code. Please help me to loop this code.
I have a macro which is working only for a single transaction.
What it does is, it take value from INPUTsheet.A2 cell then runs SAP gui, SAP gui exports a excelfile(SAPTO.XLS) to temp location.
Then macro opens the file SAPTO.XLS copies full data to Sheet TO1,sorts & Save.
I want this to continues and take value from INPUTsheet.A3 and copies full data to Sheet T02 and so on...till INPUTsheet.A10 & Sheet T10.
Every time SAP Gui runs it will replace the SAPTO.XLS file.
Iam not a expert s below is rough but Working perfect code. Please help me to loop this code.
Code:
Public Function DefFolder() As String
DefFolder = Environ$("TEMP") & "\"
End Function
Public Function SapXlsFile() As String
SapXlsFile = "SAPTO.XLS"
End Function
Sub StartTransac()
ClearData
SAP_To1
ImportFileandFormat
SortData
End Sub
Public Sub SAP_To1()
Dim ToNos As String
Dim SapGuiAuto
Dim SetApp
Dim Connection
Dim Session
Set SapGuiAuto = GetObject("SAPGUI")
Set SetApp = SapGuiAuto.GetScriptingEngine
Set Connection = SetApp.Children(0)
Set Session = Connection.Children(0)
Session.findById("wnd[0]").maximize
ToNos = ActiveWorkbook.Sheets(1).Range("A2").Value
Session.findById("wnd[0]/tbar[0]/okcd").Text = "/nlt23"
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]/usr/radT1_ALLTA").Select
Session.findById("wnd[0]/usr/ctxtT1_LGNUM").Text = "ADI"
Session.findById("wnd[0]/usr/txtT1_TANUM-LOW").Text = ToNos
Session.findById("wnd[0]/usr/ctxtLISTV").Text = "/CZ28 LT22"
Session.findById("wnd[0]/usr/ctxtLISTV").SetFocus
Session.findById("wnd[0]/usr/ctxtLISTV").caretPosition = 10
Session.findById("wnd[0]/tbar[1]/btn[8]").press
Session.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[2]").Select
Session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
Session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").SetFocus
Session.findById("wnd[1]/tbar[0]/btn[0]").press
Session.findById("wnd[1]/usr/ctxtDY_PATH").Text = DefFolder
Session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = SapXlsFile
Session.findById("wnd[1]/usr/ctxtDY_PATH").SetFocus
Session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 24
Session.findById("wnd[1]/tbar[0]/btn[11]").press
Session.findById("wnd[0]/tbar[0]/btn[3]").press
Session.findById("wnd[0]/tbar[0]/btn[3]").press
End Sub
Sub ImportFileandFormat()
Application.DisplayAlerts = False
Dim ToNos As Long
myProjFile = ThisWorkbook.Name
Workbooks.Open DefFolder & SapXlsFile
Windows("SAPTO.XLS").Activate
Cells.Select
Selection.Copy
Windows(myProjFile).Activate
Sheets("TO1").Select
Cells.Select
ActiveSheet.Paste
Columns("G:H").Select
Application.CutCopyMode = False
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("I:I").Select
Selection.Cut
Columns("D:D").Select
ActiveSheet.Paste
Columns("G:G").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("J:J").Select
Selection.Cut
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Columns("I:S").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
Cells.EntireColumn.AutoFit
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:4").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
ActiveCell.FormulaR1C1 = "TO NO"
Range("B1").Select
ActiveCell.FormulaR1C1 = "ORDER NO"
Range("C1").Select
ActiveCell.FormulaR1C1 = "MATERIAL"
Range("D1").Select
ActiveCell.FormulaR1C1 = "QTY"
Range("E1").Select
ActiveCell.FormulaR1C1 = "BIN LOC"
Range("F1").Select
ActiveCell.FormulaR1C1 = "UOM"
Range("G1").Select
ActiveCell.FormulaR1C1 = "DESCRIPTION"
Range("H1").Select
ActiveCell.FormulaR1C1 = "STG"
Range("A1:H1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Columns("F:F").Select
Selection.ColumnWidth = 5.43
Range("A2").Select
ActiveWorkbook.Save
Windows("SAPTO.XLS").Activate
ActiveWorkbook.Close
Sheets("input").Range("b2").Value = "Completed"
End Sub
Sub SortData()
Sheets("TO1").Select
Rows("1:1").Select
ActiveWorkbook.Worksheets("TO1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("TO1").Sort.SortFields.Add2 Key:=Range("E2:E1000") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("TO1").Sort
.SetRange Range("A1:H1000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A2").Select
Sheets("input").Select
Range("A2").Select
End Sub
Sub ClearData()
Sheets("TO1").Select
Cells.Select
Selection.ClearContents
End Sub