Hi guys,
I am new to creating macro tools using excel VBA and SAP and I am studying on my own.
I just want to ask if you can help me with creating my macro tool for clearing in SAP tcode f-03 because we are having backlogs of open items and this would help.
Here is my recorded VBA from SAP.
If Not IsObject(application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
Set Connection = application.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 application, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "f-03"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/sub:SAPMF05A:0131/radRF05A-XPOS1[4,0]").Select
session.findById("wnd[0]/usr/ctxtRF05A-AGKON").Text = "1992010"
session.findById("wnd[0]/usr/ctxtBKPF-BUDAT").Text = "07.09.2014"
session.findById("wnd[0]/usr/txtBKPF-MONAT").Text = "9"
session.findById("wnd[0]/usr/ctxtBKPF-BUKRS").Text = "lb01"
session.findById("wnd[0]/usr/ctxtBKPF-WAERS").Text = "USD"
session.findById("wnd[0]/usr/sub:SAPMF05A:0131/radRF05A-XPOS1[4,0]").SetFocus
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[0,0]").Text = "2000007080"
session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[1,0]").Text = "2000007101"
session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[1,0]").SetFocus
session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[1,0]").caretPosition = 10
session.findById("wnd[0]/tbar[1]/btn[16]").press
The data that I want to use is coming from my excel file. Below is the sample of the data.
[TABLE="class: jiveBorder, width: 547"]
<thead style="margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; font-family: inherit; vertical-align: baseline; background-color: transparent; ">[TR="bgcolor: transparent"]
[TH="class: header, bgcolor: #6690BC, align: center"]Header 1[/TH]
[TH="class: header, bgcolor: #6690BC, align: center"]Header 2[/TH]
[TH="class: header, bgcolor: #6690BC, align: center"]Header 3[/TH]
[TH="class: header, bgcolor: #6690BC, align: center"]Header 4[/TH]
[TH="class: header, bgcolor: #6690BC, align: center"]Header 5[/TH]
[TH="class: header, bgcolor: #6690BC, align: center"]Header 6[/TH]
[/TR]
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; font-family: inherit; vertical-align: baseline; background-color: transparent; ">[TR="bgcolor: transparent"]
[TD="bgcolor: transparent"][TABLE="width: 64"]
<tbody style="margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; font-family: inherit; vertical-align: baseline; background-color: transparent; ">[TR="bgcolor: transparent"]
[TD]GL Account[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="bgcolor: transparent"][TABLE="width: 64"]
<tbody style="margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; font-family: inherit; vertical-align: baseline; background-color: transparent; ">[TR="bgcolor: transparent"]
[TD]Company code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="bgcolor: transparent"]Year[/TD]
[TD="bgcolor: transparent"]Currency[/TD]
[TD="bgcolor: transparent"]Document number[/TD]
[TD="bgcolor: transparent"]Clearing Date[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="bgcolor: transparent"]1810205[/TD]
[TD="bgcolor: transparent"]7000[/TD]
[TD="bgcolor: transparent"]2014[/TD]
[TD="bgcolor: transparent"]USD[/TD]
[TD="bgcolor: transparent"]2000325777[/TD]
[TD="bgcolor: transparent"]09.09.2014[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]3400112282[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
After the pair of document numbers are cleared, I also want to copy the message from SAP saying that is cleared and paste it in Excel.
I hope I was able to give you a clear view of what I want to achieve.. I also want the script to loop until all updated document numbers are cleared.
Hope you could help guys!
Thanks alot!
I am new to creating macro tools using excel VBA and SAP and I am studying on my own.
I just want to ask if you can help me with creating my macro tool for clearing in SAP tcode f-03 because we are having backlogs of open items and this would help.
Here is my recorded VBA from SAP.
If Not IsObject(application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
Set Connection = application.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 application, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "f-03"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/sub:SAPMF05A:0131/radRF05A-XPOS1[4,0]").Select
session.findById("wnd[0]/usr/ctxtRF05A-AGKON").Text = "1992010"
session.findById("wnd[0]/usr/ctxtBKPF-BUDAT").Text = "07.09.2014"
session.findById("wnd[0]/usr/txtBKPF-MONAT").Text = "9"
session.findById("wnd[0]/usr/ctxtBKPF-BUKRS").Text = "lb01"
session.findById("wnd[0]/usr/ctxtBKPF-WAERS").Text = "USD"
session.findById("wnd[0]/usr/sub:SAPMF05A:0131/radRF05A-XPOS1[4,0]").SetFocus
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[0,0]").Text = "2000007080"
session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[1,0]").Text = "2000007101"
session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[1,0]").SetFocus
session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[1,0]").caretPosition = 10
session.findById("wnd[0]/tbar[1]/btn[16]").press
The data that I want to use is coming from my excel file. Below is the sample of the data.
[TABLE="class: jiveBorder, width: 547"]
<thead style="margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; font-family: inherit; vertical-align: baseline; background-color: transparent; ">[TR="bgcolor: transparent"]
[TH="class: header, bgcolor: #6690BC, align: center"]Header 1[/TH]
[TH="class: header, bgcolor: #6690BC, align: center"]Header 2[/TH]
[TH="class: header, bgcolor: #6690BC, align: center"]Header 3[/TH]
[TH="class: header, bgcolor: #6690BC, align: center"]Header 4[/TH]
[TH="class: header, bgcolor: #6690BC, align: center"]Header 5[/TH]
[TH="class: header, bgcolor: #6690BC, align: center"]Header 6[/TH]
[/TR]
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; font-family: inherit; vertical-align: baseline; background-color: transparent; ">[TR="bgcolor: transparent"]
[TD="bgcolor: transparent"][TABLE="width: 64"]
<tbody style="margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; font-family: inherit; vertical-align: baseline; background-color: transparent; ">[TR="bgcolor: transparent"]
[TD]GL Account[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="bgcolor: transparent"][TABLE="width: 64"]
<tbody style="margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; font-family: inherit; vertical-align: baseline; background-color: transparent; ">[TR="bgcolor: transparent"]
[TD]Company code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="bgcolor: transparent"]Year[/TD]
[TD="bgcolor: transparent"]Currency[/TD]
[TD="bgcolor: transparent"]Document number[/TD]
[TD="bgcolor: transparent"]Clearing Date[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="bgcolor: transparent"]1810205[/TD]
[TD="bgcolor: transparent"]7000[/TD]
[TD="bgcolor: transparent"]2014[/TD]
[TD="bgcolor: transparent"]USD[/TD]
[TD="bgcolor: transparent"]2000325777[/TD]
[TD="bgcolor: transparent"]09.09.2014[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]3400112282[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
After the pair of document numbers are cleared, I also want to copy the message from SAP saying that is cleared and paste it in Excel.
I hope I was able to give you a clear view of what I want to achieve.. I also want the script to loop until all updated document numbers are cleared.
Hope you could help guys!
Thanks alot!