PVBELN (quotation number) to Excel

[Former Member]

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

[stefan_schnell · Accepted solution]

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

[Former Member · Accepted solution]

Hello Stefan,

I am grateful and happy. Everything works!