Looping Macro to get Data from SAP using criteria on INPUT sheet A2 to A10

S

sanketsk

New Member
Joined
Apr 3, 2019
Messages
4
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.
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

T
  • Question Question
Macro to download SAP report to Excel
Replies
0
Views
3K
texasguy66
T
K
  • Solved
formating question
Replies
3
Views
324
Micron
M
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
600
RoryA
RoryA
P
  • Question Question
Issue with button not running in spread sheet only in test mode
Replies
1
Views
379
6StringJazzer
6StringJazzer
K
  • Solved
create a button that do actions
2 3
Replies
22
Views
1K
kerm007
K

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top