on 09-15-2016 6:02 AM
Hello gurus.
Can you please help me to find a code which will assign value of this PVBELN parameter to any vairable in my VBA code.
I am totally new into SAP and coding, so please do not blame. I work in sales and have to use VA21 transaction to record data and recieve quotation number. Previously I did it manually, but then discovered Script recording function in SAP and managed to integrate some code into my excel file which opens SAP, establish connection, opens VA21 transaction, sends all relative information into SAP from my excel file and saves. But I can not save quotation number which pops up after saving SAP in left bottom corner.
Have to manually type it into excel. As I understood this number is saved in PVBELN parameter of /SAPBOQ/SAVE_AFTER_RELEASE function module.
Sub SAP()
Dim App, Connection, session, objShell As Object
' establish connection
If IsProcessRunning("saplogon.exe") = True _
Then
Call Shell("taskkill /F /IM saplogon.exe", vbHide)
End If
Call Shell("Explorer.exe " & [RepositoryPath] & "SAP Session.sap", vbNormalFocus)
50
On Error GoTo 50
On Error GoTo -1
If Not IsObject(App) _
Then
Set SapGuiAuto = GetObject("SAPGUI")
Set App = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) _
Then
Set Connection = App.Children(0)
End If
Set session = Connection.Children(0)
If IsObject(WScript) _
Then
WScript.ConnectObject session, "on"
WScript.ConnectObject Application, "on"
End If
iRet = MsgBox("Do you want to open existing offer record?", vbYesNo, strTitle)
If iRet = vbYes _
Then
'Open transaction
session.findById("wnd[0]/tbar[0]/okcd").Text = "VA22"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtVBAK-VBELN").Text = "20" & [QBnumber]
session.findById("wnd[0]/usr/ctxtVBAK-VBELN").caretPosition = 8
session.findById("wnd[0]").sendVKey 0
iRet = MsgBox("Do you want to update record?", vbYesNo, strTitle)
If iRet = vbYes _
Then
SAP_Fill_in
Exit Sub
Else
Exit Sub
End If
Else
End If
iRet = MsgBox("Do you want to create new offer record?", vbYesNo, strTitle)
If iRet = vbYes _
Then
'open transaction
session.findById("wnd[0]/tbar[0]/okcd").Text = "VA21"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtVBAK-AUART").Text = "ZQT" 'Quotation type
session.findById("wnd[0]/usr/ctxtVBAK-VKORG").Text = "0065" 'Sales organization
session.findById("wnd[0]/usr/ctxtVBAK-VTWEG").Text = "65" 'Deistribution channel
session.findById("wnd[0]/usr/ctxtVBAK-SPART").Text = "65" 'Division
session.findById("wnd[0]/usr/ctxtVBAK-VKBUR").Text = [Sales_Office] 'Sales office
session.findById("wnd[0]/usr/ctxtVBAK-VKGRP").Text = [Sales_Group] 'Sales group
session.findById("wnd[0]/tbar[1]/btn[5]").press
SAP_Fill_in
Else
Call Shell("taskkill /F /IM saplogon.exe", vbHide)
End If
End Sub
Sub SAP_Fill_in()
Dim App, Connection, session As Object
10
On Error GoTo 10
On Error GoTo -1
If Not IsObject(App) _
Then
Set SapGuiAuto = GetObject("SAPGUI")
Set App = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) _
Then
Set Connection = App.Children(0)
End If
Set session = Connection.Children(0)
If IsObject(WScript) _
Then
WScript.ConnectObject session, "on"
WScript.ConnectObject Application, "on"
End If
If [PO_nb] <> "" _
Then
session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/ctxtVBKD-BSTDK").Text = [PO_Expect_Date] '"12.05.2016" 'PO date
Else
session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/txtVBKD-BSTKD").Text = [PO_nb] 'PO nb
End If
session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/subPART-SUB:SAPMV45A:4701/ctxtKUAGV-KUNNR").Text = [Company_SAP_nb] 'Sold-To party
session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/subPART-SUB:SAPMV45A:4701/ctxtKUWEV-KUNNR").Text = [Company_SAP_nb] 'Ship-To party
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/ssubHEADER_FRAME:SAPMV45A:4440/ctxtRV45A-KETDAT").Text = Day(Now()) & "." & Month(Now()) & "." & Year(Now()) + 1 'Req. deliv. date - in 1 year
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/ssubHEADER_FRAME:SAPMV45A:4440/ctxtVBKD-ZTERM").Text = [Payment_Code] 'Payment terms
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/ssubHEADER_FRAME:SAPMV45A:4440/ctxtRV45A-DWERK").Text = "0650" 'Deliver. Plant
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/ssubHEADER_FRAME:SAPMV45A:4440/ctxtVBAK-BNDDT").Text = Day(Now() + [ValidityTime]) & "." & Month(Now() + [ValidityTime]) & "." & Year(Now() + [ValidityTime]) 'Validity"
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/ssubHEADER_FRAME:SAPMV45A:4440/ctxtVBKD-INCO1").Text = Left([Incoterms], 3) ' Incoterms
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/ssubHEADER_FRAME:SAPMV45A:4440/txtVBKD-INCO2").Text = Left([Destination], 28) 'Delivery place
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/ctxtRV45A-MABNR[1,0]").Text = [Material_Code] 'Materials
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/txtRV45A-KWMENG[2,0]").Text = "1" 'Qty
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/ctxtVBAP-VRKME[3,0]").Text = "PC" 'pcs
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/txtVBAP-ARKTX[5,0]").Text = "Materials" 'meterial description
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/txtKOMV-KBETR[15,0]").Text = [Price] 'Price
session.findById("wnd[0]").sendVKey 0 'Freight is deterined OK button
20
On Error GoTo 20
On Error GoTo -1
Application.Wait (Now + TimeValue("0:00:1"))
session.findById("wnd[0]").sendVKey 0 'Freight is deterined OK button
'session.findById("wnd[0]/mbar/menu[2]/menu[0]").Select 'overview page
session.findById("wnd[0]/mbar/menu[2]/menu[1]/menu[0]").Select 'Navigate to sales pane
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4301/ctxtVBAK-VKBUR").Text = [Sales_Office] 'Sales office
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4301/ctxtVBAK-VKGRP").Text = [Sales_Group] 'Sales group
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\10").Select 'Navigate to project name
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\10/ssubSUBSCREEN_BODY:SAPMV45A:4351/txtVBAK-BNAME").Text = Left([ProjectName], 35) 'Project name
If [QBnumber] <> "XXXXXX" _
Then
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\10/ssubSUBSCREEN_BODY:SAPMV45A:4351/txtVBAK-SUBMI").Text = [QBnumber] 'Collective nb.
End If
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08").Select 'Navigate to sales person
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08/ssubSUBSCREEN_BODY:SAPMV45A:4352/subSUBSCREEN_PARTNER_OVERVIEW:SAPLV09C:1000/tblSAPLV09CGV_TC_PARTNER_OVERVIEW/ctxtGVS_TC_DATA-REC-PARTNER[1,5]").Text = [Staff_nb] 'Sales person nb.
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\13").Select 'Navigate to working hours
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\13/ssubSUBSCREEN_BODY:SAPMV45A:4312/sub8309:SAPMV45A:8309/txtVBAK-ZZWARBEI").Text = [Hours_Level1] + [Hours_Level2] + [Hours_Level3] + [Hours_Level4] 'Working hours
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\14").Select 'Navigate to AIM
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\14/ssubSUBSCREEN_BODY:SAPMV45A:4323/subCUSTOMER_SCREEN:ZRSD_SO_HDR_CUSTOM_TAB:0100/ctxtVBAK-ZZAIMAPPLN").Text = [AIM] 'AIM Application
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\14/ssubSUBSCREEN_BODY:SAPMV45A:4323/subCUSTOMER_SCREEN:ZRSD_SO_HDR_CUSTOM_TAB:0100/ctxtZVBAK-ZZBUSINESSLINE").Text = [Business_Line] 'Business Line
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\14/ssubSUBSCREEN_BODY:SAPMV45A:4323/subCUSTOMER_SCREEN:ZRSD_SO_HDR_CUSTOM_TAB:0100/ctxtZVBAK-ZZINDUSAGE").Text = [Industry] 'Industry
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\14/ssubSUBSCREEN_BODY:SAPMV45A:4323/subCUSTOMER_SCREEN:ZRSD_SO_HDR_CUSTOM_TAB:0100/ctxtZVBAK-ZZCHANNEL").Text = [Channel] 'Channel
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\01").Select 'Navigate to version
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4301/txtVBAK-VSNMR_V").Text = [Ver] 'Version
session.findById("wnd[0]/tbar[0]/btn[11]").press 'Save
End Sub
Hello Anton,
welcome in the Scripting Language forum.
It is very easy to read the text from the statusbar.
Try this to get the quotation number
Dim sbartext As String
Dim sbarArray() As String
Dim QuotNum As String
'-Here you read the text from the statusbar
sbartext = session.findById("wnd[0]/sbar/pane[0]").text
'-Here you split the text at the spaces
sbarArray = Split(sbarText, " ")
'-Here you read the 4th entry from behind
QuotNum = sbarArray(UBound(sbarArray()) - 3)
In a normal case you need a little bit time until you get the message after saving. You can code a loop which checks the text from the statusbar and if it is not empty you can go on. Also you can check if it is success or error. You can find an explanation about messagetypes from Holger Köhn here.
Let us know your results.
Cheers
Stefan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.