注意事项
- MIGO界面只保留抬头、行项目(不展开行项目细节)
- 工厂设置为默认
- 库存地点设置为默认
- OK 按钮,默认自动打勾
Sub ZMIGO()
' ================================================
' Start Excel file
' ================================================
' Create Excel Application
Dim xlApp
Set xlApp = CreateObject("Excel.Application")
' Open Excel Fiels
Dim xlWorkbook, xlWorksheet
Set xlWorkbook = xlApp.Workbooks.Open("C:\Users\XXXX\Downloads\ZMIGO.xlsm")
Set xlWorksheet = xlWorkbook.Sheets("Sheet1")
' get areas of data
Dim RowCount,usedRng
set usedRng = xlWorksheet.UsedRange
RowCount = usedRng.Rows.Count
' msgbox usedRng.cells(2, 1).value
i = 0 'control count of first numPerScreen records of the first screen
m = 1 ' control count of other records screens
k = 0 ' control one material document records for different loop
count1 = 100 ' one document contains records, it depends.
numPerscreen = 9 ' depends on personal coumputer resolution,adjust the number
' session start
If Not IsObject(Application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set app = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
Set Connection = app.Children(0)
End If
If Not IsObject(session) Then
Set session = Connection.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject app, "on"
End If
' MIGO Starting, and initial MIGO First screen
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/NMIGO"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_FIRSTLINE:SAPLMIGO:0010/ctxtGODEFAULT_TV-BWART").Text = "561"
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_HEADER:SAPLMIGO:0101/subSUB_HEADER:SAPLMIGO:0100/tabsTS_GOHEAD/tabpOK_GOHEAD_GENERAL/ssubSUB_TS_GOHEAD_GENERAL:SAPLMIGO:0112/txtGOHEAD-BKTXT").text = usedRng.cells(j + 2, 7).value
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_HEADER:SAPLMIGO:0101/subSUB_HEADER:SAPLMIGO:0100/tabsTS_GOHEAD/tabpOK_GOHEAD_GENERAL/ssubSUB_TS_GOHEAD_GENERAL:SAPLMIGO:0112/ctxtGOHEAD-BLDAT").Text = usedRng.cells(j + 2, 8).value
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_HEADER:SAPLMIGO:0101/subSUB_HEADER:SAPLMIGO:0100/tabsTS_GOHEAD/tabpOK_GOHEAD_GENERAL/ssubSUB_TS_GOHEAD_GENERAL:SAPLMIGO:0112/ctxtGOHEAD-BUDAT").Text = usedRng.cells(j + 2, 9).value
' loop recods in Excel files
For j = 0 To RowCount
' skip first materil document, unttil next document will SAVE fist document and restart MIGO
If j > 0 And j Mod count1 = 0 Then
' Generate material documents euals numbPerScreen
session.findById("wnd[0]/tbar[1]/btn[23]").press
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/NMIGO"
session.findById("wnd[0]").sendVKey 0
' Filling in Header Data
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_FIRSTLINE:SAPLMIGO:0010/ctxtGODEFAULT_TV-BWART").Text = "561"
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_HEADER:SAPLMIGO:0101/subSUB_HEADER:SAPLMIGO:0100/tabsTS_GOHEAD/tabpOK_GOHEAD_GENERAL/ssubSUB_TS_GOHEAD_GENERAL:SAPLMIGO:0112/txtGOHEAD-BKTXT").text = usedRng.cells(j + 2, 7).value
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_HEADER:SAPLMIGO:0101/subSUB_HEADER:SAPLMIGO:0100/tabsTS_GOHEAD/tabpOK_GOHEAD_GENERAL/ssubSUB_TS_GOHEAD_GENERAL:SAPLMIGO:0112/ctxtGOHEAD-BLDAT").Text = usedRng.cells(j + 2, 8).value
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_HEADER:SAPLMIGO:0101/subSUB_HEADER:SAPLMIGO:0100/tabsTS_GOHEAD/tabpOK_GOHEAD_GENERAL/ssubSUB_TS_GOHEAD_GENERAL:SAPLMIGO:0112/ctxtGOHEAD-BUDAT").Text = usedRng.cells(j + 2, 9).value
i = 0 'reset first record of the first screen
m = 1 'reset other records and screens
k = 0 'reset one material document record for different loop
End If
'first screen ,only first k recods
If k < numPerscreen Then
' Material number
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_ITEMLIST:SAPLMIGO:0200/tblSAPLMIGOTV_GOITEM/ctxtGOITEM-MAKTX[1," & i & "]").Text = usedRng.cells(j + 2, 2).value
' Amount of Material
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_ITEMLIST:SAPLMIGO:0200/tblSAPLMIGOTV_GOITEM/txtGOITEM-ERFMG[3," & i & "]").Text = usedRng.cells(j + 2, 3).value
' Amount of currency
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_ITEMLIST:SAPLMIGO:0200/tblSAPLMIGOTV_GOITEM/txtGOITEM-EXBWR[48," & i & "]").Text = usedRng.cells(j + 2, 4).value
' Plant
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_ITEMLIST:SAPLMIGO:0200/tblSAPLMIGOTV_GOITEM/ctxtGOITEM-NAME1[11," & i & "]").text = usedRng.cells(j + 2, 5).value
' Storage Location
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_ITEMLIST:SAPLMIGO:0200/tblSAPLMIGOTV_GOITEM/ctxtGOITEM-LGOBE[5," & i & "]").text = usedRng.cells(j + 2, 6).value
i = i + 1
k = k + 1
Else
' other screen, other records
If k > 0 and ((k - 1) Mod (numPerscreen - 1) = 0) Then
'vertical scrollbar pagedown after k records
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_ITEMLIST:SAPLMIGO:0200/tblSAPLMIGOTV_GOITEM").verticalScrollbar.Position = k - 1
m = 1
End If
'records of others except first k
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_ITEMLIST:SAPLMIGO:0200/tblSAPLMIGOTV_GOITEM/ctxtGOITEM-MAKTX[1," & m & "]").Text = usedRng.cells(j + 2, 2).value
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_ITEMLIST:SAPLMIGO:0200/tblSAPLMIGOTV_GOITEM/txtGOITEM-ERFMG[3," & m & "]").Text = usedRng.cells(j + 2, 3).value
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_ITEMLIST:SAPLMIGO:0200/tblSAPLMIGOTV_GOITEM/txtGOITEM-EXBWR[48," & m & "]").Text = usedRng.cells(j + 2, 4).value
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_ITEMLIST:SAPLMIGO:0200/tblSAPLMIGOTV_GOITEM/ctxtGOITEM-NAME1[11," & m & "]").text = usedRng.cells(j + 2, 5).value
session.findById("wnd[0]/usr/ssubSUB_MAIN_CARRIER:SAPLMIGO:0002/subSUB_ITEMLIST:SAPLMIGO:0200/tblSAPLMIGOTV_GOITEM/ctxtGOITEM-LGOBE[5," & m & "]").text = usedRng.cells(j + 2, 6).value
m = m + 1
k = k + 1
End If
Next
'save the material document less than 50
session.findById("wnd[0]/tbar[1]/btn[23]").press
' ================================================
' Exit Excel file
' ================================================
' Close Excel files
xlWorkbook.Close False
Set xlWorksheet = Nothing
Set xlWorkbook = Nothing
' Exit Excel application
xlApp.Quit
Set xlApp = Nothing
End Sub
ZMIGO()