Pull up data from excel and update it in SAP then Save.

[Former Member]

Hi All,

I am a fresher to this scripting world....Can someone help me correct the below VB scripting as it is throwing up an error as below...?

What i exactly wanted to do is that to set output for list of invoice numbers in VF02 t-code in SAP and save it. I want this task to run on all the below invoices one by one after it is saved. Also the below list of invoice numbers are saved in"Book 2" as per below path.

C:\Users\mohank\Desktop\Book2.xlsx

My code is as below:-

***************************************************************************************************************************************************************************

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 = "/nvf02"

session.findById("wnd[0]").sendVKey 0

Set xclapp = GetObject(, "Excel.Application")

Set xclwbk = xclapp.Workbooks.Open("C:\Users\mohank\Desktop\Book2.xlsx")

Set xclsht = xclwbk.Sheets("Sheet1")

If Not IsObject(application) Then

for i = 2 to xclapp.ActiveCell.SpecialCells(11).Row

for j = 1 to xclapp.ActiveCell.SpecialCells(11).Column

  if j=1 then Transaction =  xclsht.Cells(i,j).Value

next

session.findById("wnd[0]/usr/ctxtVBRK-VBELN").text = Transaction

session.findById("wnd[0]/usr/ctxtVBRK-VBELN").caretPosition = 10

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/mbar/menu[2]/menu[0]/menu[3]").select

session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/ctxtDNAST-KSCHL[1,0]").text = "ZB17"

session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/ctxtDNAST-KSCHL[1,0]").caretPosition = 4

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3").getAbsoluteRow(0).selected = true

session.findById("wnd[0]").sendVKey 2

session.findById("wnd[0]/usr/ctxtNAST-LDEST").text = "in03"

session.findById("wnd[0]/usr/ctxtNAST-LDEST").caretPosition = 4

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]").sendVKey 3

session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3").getAbsoluteRow(0).selected = true

session.findById("wnd[0]").sendVKey 11

next

msgbox "All " & cstr(xclapp.ActiveCell.SpecialCells(11).Row - 1) & " Excel rows have been processed."

Set xclwbk = Nothing

Set xclsht = Nothing

xclapp.Quit

set xclapp = Nothing

***************************************************************************************************************************************************************************

Please help..thanks:)

[holger_khn · Accepted solution]

Hello.

There was an 'dead' IF-line which I have removed.

And I cleanup an unneccessary For-loop.

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 = "/nvf02"
    session.findById("wnd[0]").sendVKey 0

    Set xclapp = GetObject(, "Excel.Application")
    Set xclwbk = xclapp.Workbooks.Open("C:\Users\mohank\Desktop\Book2.xlsx")
    Set xclsht = xclwbk.Sheets("Sheet1")

    For i = 2 To xclapp.ActiveCell.SpecialCells(11).Row
        Transaction = xclsht.Cells(i, 1).Value
        session.findById("wnd[0]/usr/ctxtVBRK-VBELN").Text = Transaction
        session.findById("wnd[0]").sendVKey 0
        session.findById("wnd[0]/mbar/menu[2]/menu[0]/menu[3]").Select
        session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/ctxtDNAST-KSCHL[1,0]").Text = "ZB17"
        session.findById("wnd[0]").sendVKey 0
        session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3").getAbsoluteRow(0).Selected = True
        session.findById("wnd[0]").sendVKey 2
        session.findById("wnd[0]/usr/ctxtNAST-LDEST").Text = "in03"
        session.findById("wnd[0]").sendVKey 0
        session.findById("wnd[0]").sendVKey 3
        session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3").getAbsoluteRow(0).Selected = True
        session.findById("wnd[0]").sendVKey 11
    Next
   
    MsgBox "All " & CStr(xclapp.ActiveCell.SpecialCells(11).Row - 1) & " Excel rows have been processed."
    Set xclwbk = Nothing
    Set xclsht = Nothing
    xclapp.Quit
    Set xclapp = Nothing

[Former Member · Accepted solution]

Thanks a million Kohn:)

i appreciate it.. Just a quick question on similar lines...can you please correct the below script....?

I am trying to fetch data from each row to update it in SAP and save it to generate a doc reference. And it should continue the same process for the next line. Also It would be great if you can add a script to copy the document reference getting generated once you save it and update the same in the excel sheet on column K simultaneously.

My Script as follows:-

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 = "/nf-28"

session.findById("wnd[0]").sendVKey 0

    Set xclapp = GetObject(, "Excel.Application")

    Set xclwbk = xclapp.Workbooks.Open("C:\Users\mohank\Desktop\Excel Data to SAP.xlsx")

    Set xclsht = xclwbk.Sheets("Sheet1")

    For i = 2 To xclapp.ActiveCell.SpecialCells(11).Row

    For j = 1 to xclapp.ActiveCell.SpecialCells(11).Column

if j=1 then Document Date = xclsht.Cells(i,j).Value

if j=2 then Type = xclsht.Cells(i,j).Value

if j=3 then Company Code = xclsht.Cells(i,j).Value

if j=4 then Posting date = xclsht.Cells(i,j).Value

if j=5 then Period = xclsht.Cells(i,j).Value

if j=6 then Currency/Rate = xclsht.Cells(i,j).Value

if j=7 then Reference = xclsht.Cells(i,j).Value

if j=8 then Account = xclsht.Cells(i,j).Value

if j=9 then Amount = xclsht.Cells(i,j).Value

if j=10 then Transaction = xclsht.Cells(i,j).Value

next

session.findById("wnd[0]/usr/ctxtBKPF-BLDAT").text = Document Date

session.findById("wnd[0]/usr/ctxtBKPF-BLART").text = Type

session.findById("wnd[0]/usr/ctxtBKPF-BUKRS").text = Company Code

session.findById("wnd[0]/usr/ctxtBKPF-BUDAT").text = Posting date

session.findById("wnd[0]/usr/txtBKPF-MONAT").text = Period

session.findById("wnd[0]/usr/ctxtBKPF-WAERS").text = Currency/Rate

session.findById("wnd[0]/usr/txtBKPF-XBLNR").text = Reference

session.findById("wnd[0]/usr/ctxtRF05A-KONTO").text = Account

session.findById("wnd[0]/usr/txtBSEG-WRBTR").text = Amount

session.findById("wnd[0]/usr/txtRF05A-SPESH").setFocus

session.findById("wnd[0]/usr/txtRF05A-SPESH").caretPosition = 0

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[1]/usr/sub:SAPMF05A:0608/radRF05A-XPOS1[1,0]").select

session.findById("wnd[1]/usr/sub:SAPMF05A:0608/radRF05A-XPOS1[1,0]").setFocus

session.findById("wnd[1]").sendVKey 0

session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[0,0]").text = Transaction

session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[0,0]").caretPosition = 10

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/tbar[1]/btn[16]").press

session.findById("wnd[0]/mbar/menu[0]/menu[1]").select

session.findById("wnd[0]").sendVKey 11

session.findById("wnd[0]/sbar").doubleClick

session.findById("wnd[0]/shellcont").close

Next

    MsgBox "All " & CStr(xclapp.ActiveCell.SpecialCells(11).Row - 1) & " Excel rows have been processed."

    Set xclwbk = Nothing

    Set xclsht = Nothing

    xclapp.Quit

    Set xclapp = Nothing

*******************************************************************************************************************

I really appreciate your help on this!!!

[holger_khn · Accepted solution]

Hello.

The question here will where this document number appear. You can get field-name with Scripting Tracker tool (search on Google for download).

I have redefined your code a Little bit. You just Need to add one codeline for getting document number from SAP into Excel:

***********************************************************************************************************

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 = "/nf-28"
session.findById("wnd[0]").sendVKey 0

Set xclapp = GetObject(, "Excel.Application")
Set xclwbk = xclapp.Workbooks.Open("C:\Users\mohank\Desktop\Excel Data to SAP.xlsx")
Set xclsht = xclwbk.Sheets("Sheet1")

For i = 2 To xclapp.ActiveCell.SpecialCells(11).Row
strDocument_Date = xclsht.Cells(i,1).Value
strType = xclsht.Cells(i,2).Value
strCompany_Code = xclsht.Cells(i,3).Value
strPosting_date = xclsht.Cells(i,4).Value
strPeriod = xclsht.Cells(i,5).Value
strCurrency_Rate = xclsht.Cells(i,6).Value
strReference = xclsht.Cells(i,7).Value
strAccount = xclsht.Cells(i,8).Value
strAmount = xclsht.Cells(i,9).Value
strTransaction = xclsht.Cells(i,10).Value
session.findById("wnd[0]/usr/ctxtBKPF-BLDAT").text = strDocument_Date
session.findById("wnd[0]/usr/ctxtBKPF-BLART").text = strType
session.findById("wnd[0]/usr/ctxtBKPF-BUKRS").text = strCompany_Code
session.findById("wnd[0]/usr/ctxtBKPF-BUDAT").text = strPosting_date
session.findById("wnd[0]/usr/txtBKPF-MONAT").text = strPeriod
session.findById("wnd[0]/usr/ctxtBKPF-WAERS").text = strCurrency_Rate
session.findById("wnd[0]/usr/txtBKPF-XBLNR").text = strReference
session.findById("wnd[0]/usr/ctxtRF05A-KONTO").text = strAccount
session.findById("wnd[0]/usr/txtBSEG-WRBTR").text = strAmount
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[1]/usr/sub:SAPMF05A:0608/radRF05A-XPOS1[1,0]").select
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[0,0]").text = strTransaction
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[16]").press
session.findById("wnd[0]/mbar/menu[0]/menu[1]").select
session.findById("wnd[0]").sendVKey 11

'xclsht.Cells(i,11).Value = session.findById("wnd[0]/usr/CORRECT_FIELDNAME_BY_ScrTr").text
session.findById("wnd[0]/sbar").doubleClick
session.findById("wnd[0]/shellcont").close
Next

MsgBox "All " & CStr(xclapp.ActiveCell.SpecialCells(11).Row - 1) & " Excel rows have been processed."
Set xclwbk = Nothing
Set xclsht = Nothing
xclapp.Quit
Set xclapp = Nothing

*********************************************************************************************

xclsht.Cells(i,10).Value for this line you Need to identify correct fieldname using Sripting Tracker. And allocate code line into correct row of script. Then it should work.

[Former Member · Accepted solution]

Thanks a ton once again Kohn....It worked fine I will get back to you later with some more questions:) I really appreciate your assistances

Thanks,

Karthik

[Former Member · Accepted solution]

How to save a pdf copy of invoice from vf03 t-code in SAP using the vbscript?

Hi Holger Kohn,

I am trying to save PDF copy of invoices from VF03 screen to desktop.

When i record the scripts for the navigation which i do...it records the script till the print preview screen as mentioned below and i am not able to proceed further to save it in desktop.

Invoice print preview screen.JPG

Can you please help me get the VB scripts until it saves it in desktop.

I appreciate your help.

Codes as below:-

********************************************************************************************

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 = "/nvf03"

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/ctxtVBRK-VBELN").text = "5100078962"

session.findById("wnd[0]/mbar/menu[0]/menu[11]").select

session.findById("wnd[1]").sendVKey 37

****************************************************************************************************

T

Thanks,

Karthik