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

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

Comments

comments