Pin It

Một số thao tác VBA với SAP

Share Button
  1. Lưu file export trong Sap vào thư mục chỉ định
    Tên file khi export thường có tên là Worksheets in Basic… nên ta kiểm kiểm tra các file đang mở có tên là Worksheets … không băng lệnh InStr

    For each a in workbooks
    if instr(a.name,"Worksheer") > 1 then
    a.activate
    a.saves("C:\a.xlsx")
    end if
    

    Next

  2. Tự động đăng nhập SAP
    ‘Opens SAP connection
    Dim SAPGUI
    Dim Applic
    Dim connection
    Dim Session
    Dim WSHShellApplication.DisplayAlerts = FalseShell “C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe”, vbNormalFocusSet WSHShell = CreateObject(“WScript.Shell”)
    Do Until WSHShell.AppActivate(“SAP Logon “)
    Application.Wait Now + TimeValue(“0:00:01”)
    Loop

    Set WSHShell = Nothing
    Set SAPGUI = GetObject(“SAPGUI”)
    Set Applic = SAPGUI.GetScriptingEngine
    Set connection = Applic.OpenConnection(“04. R3 PRD []”, True)
    Set Session = connection.Children(0)
    ‘ Session.findById(“wnd[0]”).iconify
    Session.findById(“wnd[0]/usr/txtRSYST-MANDT”).Text = “100”
    Session.findById(“wnd[0]/usr/txtRSYST-BNAME”).Text = Environ(“Username”)
    Session.findById(“wnd[0]/usr/pwdRSYST-BCODE”).Text = sysstart.psswrd
    Session.findById(“wnd[0]”).sendVKey 0

    Session.findById(“wnd[0]”).maximize
    Session.findById(“wnd[0]/tbar[0]/okcd”).Text = “/nZUI_SELECTION”
    Session.findById(“wnd[0]”).sendVKey 0

    ”Performs some filters and opens data

    ‘ Export from SAP to .xlsx file.
    Session.findById(“wnd[0]/usr/cntlGRID1/shellcont/shell”).contextMenu
    Session.findById(“wnd[0]/usr/cntlGRID1/shellcont/shell”).selectContextMenuItem “&XXL”
    Session.findById(“wnd[1]/tbar[0]/btn[0]”).press
    Session.findById(“wnd[1]/usr/ctxt[0]”).Text = “C:\Users\” & Environ(“Username”) & “\Downloads”
    Session.findById(“wnd[1]/usr/ctxt[1]”).Text = “SAP_export.XLSX”
    Session.findById(“wnd[1]/tbar[0]/btn[11]”).press

    ‘Closes SAP connection
    Set Session = Nothing
    connection.CloseSession (“ses[0]”)
    Set connection = Nothing

     

    ‘Clear table from SMS Input
    ThisWorkbook.Worksheets(“SMS Input”).Select
    Cells.Select
    Selection.ClearContents

    ‘Insert .xlsx file data
    Cells.Select
    Selection.Delete Shift:=xlUp
    Range(“A6”).Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
    “OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password=””””;User ID=Admin;Data Source=C:\Users\” & Environ(“Username”) & “\Downloads\SAP_export.XLSX;Mode=Share Deny None” _
    , _
    “;Extended Properties=””HDR=YES;””;Jet OLEDB:System database=””””;Jet OLEDB:Registry Path=””””;Jet OLEDB:Database Password=””””;Jet OL” _
    , _
    “EDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Je” _
    , _
    “t OLEDB:New Database Password=””””;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don’t Copy Lo” _
    , _
    “cale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Je” _
    , “t OLEDB:Bypass UserInfo Validation=False”), Destination:=Range(“$A$6”)). _
    QueryTable
    .CommandType = xlCmdTable
    .CommandText = Array(“Sheet1$”)
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .SourceDataFile = “C:\Users\” & Environ(“Username”) & “\Downloads\SAP_export.XLSX”
    .ListObject.DisplayName = “Table_export3”
    .Refresh BackgroundQuery:=False
    .MaintainConnection = False
    End With

Share Button

Comments

comments