Script for export to excel from Post processing invoices in SAP

[Former Member]

HI. I have a problem i cant really solve. When i in SAP example propose many invoices from a big advice. If the advice dont match, means all the invoices are not in the processing invoices, i need to compare these invoices  in the table for post processing invoices with the advice i have.

There is no direct option to do this export from this table in SAP. And i have asked many, What we do now, Is that we copy each page and paste it in to excel. The screen which shows the post processing invoices , you need go page by page, which also make if difficult.

Its really some could help allot to be able to do with a script.,

I am not expert in that so hope for some help. I have recorded first how i enter to this table or screen, where i need the export from. To support the understanding i have attached pics also and its just and example not with so many invoices.

Some if some can help make a script for this or macro directly from excel would really be great.

Sincerely

Abjac

Below is 2 recorded scripts. first is how i enter the table.  The second script nearly the same,. but here i go to next page in the table,.

And more below pics to see it ...

Recorded script to open the table.

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]/usr/tabsSTR_BUCHG/tabpTB_BSID/ssubSTR_BUCHG_SCA:/HOAG/AK_POSTPROCESSING:0102/btnPB_MODAL").press

session.findById("wnd[1]/usr/tbl/HOAG/AK_POSTPROCESSINGTC_BSID_MODAL/txt/HOAG/AB_BSID_ST-BELNR[0,1]").setFocus

Recorded script open the table and go to next page in the table.

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]/usr/tabsSTR_BUCHG/tabpTB_BSID/ssubSTR_BUCHG_SCA:/HOAG/AK_POSTPROCESSING:0102/btnPB_MODAL").press

session.findById("wnd[1]/usr/tbl/HOAG/AK_POSTPROCESSINGTC_BSID_MODAL/txt/HOAG/AB_BSID_ST-BELNR[0,1]").setFocus

session.findById("wnd[1]/usr/tbl/HOAG/AK_POSTPROCESSINGTC_BSID_MODAL/txt/HOAG/AB_BSID_ST-BELNR[0,1]").caretPosition = 6

session.findById("wnd[1]/usr/btnPB_NEXT_PAGE").press

Post processing in main screen in SAP.

To get to the table i press this button in the mail screen.

Then i come to the table i need to export from.

In the right corner down on the right there is 5 buttons. One i can go to the next page, when i press it.

[script_man · Accepted solution]

Hi Abjac,

You have a common problem that may be of interest to other users. From your post, I gathered that it might be a GuiTableControl. If that were so, one would have to determine only the parameter myTable (also known as TableId).

My assumption is as follows:

myTable = session.findById("wnd[1]/usr/tbl/HOAG/AK_POSTPROCESSINGTC_BSID_MODAL")

You can identify themselves there as well. Start the SAP Script Recorder and click once in the scroll bar as follows:

Now exit the recording and look at the script. Locate the line with ...Vertical Scrollbar.Position. The desired parameter is left from here.

from example:

myTable.VerticalScrollbar.Position = 20

If you have determined the required parameters myTable itself, report it here in the forum.

Regards,

ScriptMan

[Former Member · Accepted solution]

Hi Scriptman and thanks allot for your answer.

I did make the recording, and the one you asked about is in the last line thanks

Hope you can help it from there.

Thanks

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]/usr/tabsSTR_BUCHG/tabpTB_BSID/ssubSTR_BUCHG_SCA:/HOAG/AK_POSTPROCESSING:0102/btnPB_MODAL").press

session.findById("wnd[1]/usr/tbl/HOAG/AK_POSTPROCESSINGTC_BSID_MODAL").verticalScrollbar.position = 20

[script_man · Accepted solution]

Hi Abjac,

I just wanted to make sure...

Requirements:

1. The table to be imported is located in the first mode on the screen.

2. The path c:\ tmp exists.

3. Excel is installed.

Now you can try the following:

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

'The following command should only guarantee that the table is at the top. If it does not work, you need to record here the command itself.

'Perhaps similar as : session.findById("wnd[1]/usr/btnPB_FIRST_PAGE").press

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

Set xclApp = CreateObject("Excel.Application")

xclApp.workbooks.add

xclApp.Visible = True

xclapp.DisplayAlerts = false

myFile = "c:\tmp\test.xlsx"

k = 1

do

set myTable = session.findById("wnd[1]/usr/tbl/HOAG/AK_POSTPROCESSINGTC_BSID_MODAL")

if k = 1 then

  'rows = myTable.RowCount

  cols = myTable.Columns.Count

  vRows = myTable.VisibleRowCount

  for j = 0 to cols - 1

   xclApp.ActiveWorkbook.sheets(1).Cells(k,j + 1).Value = myTable.columns.elementAt(j).title

  next

  k = k + 1

end if

for i = 0 to vRows - 1

l = 1

  for j = 0 to Cols - 1

    on error resume next

    myVariable = trim(myTable.GetCell(i,j).Text)

    if err.number <> 0 then  exit for     

    on error goto 0

    if left(right(myVariable,3),1) = "," then

       myVariable = replace(myVariable, "." , "")

       myVariable = replace(myVariable, "," , "")

       xclApp.ActiveWorkbook.sheets(1).Cells(k,l).Value = myVariable/100

    else

       xclApp.ActiveWorkbook.sheets(1).Cells(k,l).Value = myVariable

    end if

    l = l + 1

  next

  if err.number <> 0 then  exit for      

  k = k + 1

next

if err.number <> 0 then  exit do      

myTable.VerticalScrollbar.Position = myTable.VerticalScrollbar.Position + vRows

Loop

xclapp.ActiveWorkbook.SaveAs myFile

'xclapp.ActiveWorkbook.Close

Set xclwbk = Nothing

Set xclsheet = Nothing

'xclapp.Quit

set xclapp = Nothing

Regards,

ScriptMan

[Former Member · Accepted solution]

HI Scriptman. I am home but will try tomorrow at my work with this one. Hope i can find a item with many invoices to try it. I will let you know for sure.

Thanks allot for now

Regards  Abjac

[Former Member · Accepted solution]

Hi. Scriptman your script are working really great. I just changed beginning so it take it from the screen before and put this screen infront. But it worked really,. Thanks allot.

I have some few questions.

Could this script be changed to VBA and run directly from a macro in a workbook.?

In the workbook i have for big advices it have to go to the sheet "Advice"

Is that possible. Would be really great.

Thanks in advance.

Abjac

Your script with the beginning i changed with the recorded look like 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]/usr/tabsSTR_BUCHG/tabpTB_BSID/ssubSTR_BUCHG_SCA:/HOAG/AK_POSTPROCESSING:0102/btnPB_MODAL").press

session.findById("wnd[1]/usr/tbl/HOAG/AK_POSTPROCESSINGTC_BSID_MODAL/txt/HOAG/AB_BSID_ST-BELNR[0,1]").setFocus

'The following command should only guarantee that the table is at the top. If it does not work, you need to record here the command itself.

'Perhaps similar as : session.findById("wnd[1]/usr/btnPB_FIRST_PAGE").press

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

Set xclApp = CreateObject("Excel.Application")

xclApp.workbooks.add

xclApp.Visible = True

xclapp.DisplayAlerts = false

myFile = "c:\tmp\test.xlsx"

k = 1

do

set myTable = session.findById("wnd[1]/usr/tbl/HOAG/AK_POSTPROCESSINGTC_BSID_MODAL")

if k = 1 then

  'rows = myTable.RowCount

  cols = myTable.Columns.Count

  vRows = myTable.VisibleRowCount

  for j = 0 to cols - 1

   xclApp.ActiveWorkbook.sheets(1).Cells(k,j + 1).Value = myTable.columns.elementAt(j).title

  next

  k = k + 1

end if

for i = 0 to vRows - 1

l = 1

  for j = 0 to Cols - 1

    on error resume next

    myVariable = trim(myTable.GetCell(i,j).Text)

    if err.number <> 0 then  exit for    

    on error goto 0

    if left(right(myVariable,3),1) = "," then

       myVariable = replace(myVariable, "." , "")

       myVariable = replace(myVariable, "," , "")

       xclApp.ActiveWorkbook.sheets(1).Cells(k,l).Value = myVariable/100

    else

       xclApp.ActiveWorkbook.sheets(1).Cells(k,l).Value = myVariable

    end if

    l = l + 1

  next

  if err.number <> 0 then  exit for     

  k = k + 1

next

if err.number <> 0 then  exit do     

myTable.VerticalScrollbar.Position = myTable.VerticalScrollbar.Position + vRows

Loop

xclapp.ActiveWorkbook.SaveAs myFile

'xclapp.ActiveWorkbook.Close

Set xclwbk = Nothing

Set xclsheet = Nothing

'xclapp.Quit

set xclapp = Nothing

[script_man · Accepted solution]

Hi Abjac,

this can be done also with VBScript. For example, as follows:

. . .

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

myFile = "c:\tmp\test.xlsx"

mySheet = "Advice"

Set xclApp = CreateObject("Excel.Application")

'xclApp.workbooks.add

Set xclwbk = xclapp.Workbooks.Open(myFile)

set xclsht = xclwbk.Sheets(mySheet)

xclApp.Visible = True

xclapp.DisplayAlerts = false

k = 1

do

set myTable = session.findById("wnd[0]/usr/ssubITEMS:SAPLFSKB:0100/tblSAPLFSKBTABLE")

if k = 1 then

  'rows = myTable.RowCount

  cols = myTable.Columns.Count

  vRows = myTable.VisibleRowCount

  for j = 0 to cols - 1

   'xclApp.ActiveWorkbook.sheets(1).Cells(k,j + 1).Value = myTable.columns.elementAt(j).title

    xclsht.Cells(k,j + 1).Value = myTable.columns.elementAt(j).title

  next

  k = k + 1

end if

for i = 0 to vRows - 1

l = 1

  for j = 0 to Cols - 1

    on error resume next

    myVariable = trim(myTable.GetCell(i,j).Text)

    if err.number <> 0 then  exit for     

    on error goto 0

    if left(right(myVariable,3),1) = "," then

       myVariable = replace(myVariable, "." , "")

       myVariable = replace(myVariable, "," , "")

      'xclApp.ActiveWorkbook.sheets(1).Cells(k,l).Value = myVariable/100

       xclsht.Cells(k,l).Value = myVariable/100

    else

      'xclApp.ActiveWorkbook.sheets(1).Cells(k,l).Value = myVariable

       xclsht.Cells(k,l).Value = myVariable

    end if

    l = l + 1

  next

  if err.number <> 0 then  exit for      

  k = k + 1

next

if err.number <> 0 then  exit do      

myTable.VerticalScrollbar.Position = myTable.VerticalScrollbar.Position + vRows

Loop

'xclapp.ActiveWorkbook.SaveAs myFile

xclapp.ActiveWorkbook.Save

'xclapp.ActiveWorkbook.Close

Set xclwbk = Nothing

Set xclsheet = Nothing

'xclapp.Quit

set xclapp = Nothing

One can of course convert this script in VBA. Here in the forum there are enough examples to. What if you're looking himself and tried to learn it well.

for example:

If your question is answered herewith, please mark them as such. Thank's.

Regards,

ScriptMan

[Former Member · Accepted solution]

Hi. Scriptman so much thanks for your answer. You are really expert in this. I moderated it a little but its working brilliant.

So great really and thanks allot for your brilliant help. Really appreciated.

Sincerely  Abjac

I ended in this one 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]/usr/tabsSTR_BUCHG/tabpTB_BSID/ssubSTR_BUCHG_SCA:/HOAG/AK_POSTPROCESSING:0102/btnPB_MODAL").press

session.findById("wnd[1]/usr/tbl/HOAG/AK_POSTPROCESSINGTC_BSID_MODAL/txt/HOAG/AB_BSID_ST-BELNR[0,1]").setFocus

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

myFile = "C:\Users\xxx_xxxxx\Desktop\SapBigAdvizer.xlsm"

mySheet = "Advice"

Set xclApp = CreateObject("Excel.Application")

'xclApp.workbooks.add

Set xclwbk = xclapp.Workbooks.Open(myFile)

set xclsht = xclwbk.Sheets(mySheet)

xclApp.Visible = True

xclapp.DisplayAlerts = false

k = 1

do

set myTable = session.findById("wnd[1]/usr/tbl/HOAG/AK_POSTPROCESSINGTC_BSID_MODAL")

if k = 1 then

  'rows = myTable.RowCount

  cols = myTable.Columns.Count

  vRows = myTable.VisibleRowCount

  for j = 0 to cols - 1

   'xclApp.ActiveWorkbook.sheets(mySheet).Cells(k,j + 1).Value = myTable.columns.elementAt(j).title

    xclsht.Cells(k,j + 1).Value = myTable.columns.elementAt(j).title

  next

  k = k + 1

end if

for i = 0 to vRows - 1

l = 1

  for j = 0 to Cols - 1

    on error resume next

    myVariable = trim(myTable.GetCell(i,j).Text)

    if err.number <> 0 then  exit for   

    on error goto 0

    if left(right(myVariable,3),1) = "," then

       myVariable = replace(myVariable, "." , "")

       myVariable = replace(myVariable, "," , "")

      'xclApp.ActiveWorkbook.sheets(mySheet).Cells(k,l).Value = myVariable/100

       xclsht.Cells(k,l).Value = myVariable/100

    else

      'xclApp.ActiveWorkbook.sheets(mySheet).Cells(k,l).Value = myVariable

       xclsht.Cells(k,l).Value = myVariable

    end if

    l = l + 1

  next

  if err.number <> 0 then  exit for    

  k = k + 1

next

if err.number <> 0 then  exit do    

myTable.VerticalScrollbar.Position = myTable.VerticalScrollbar.Position + vRows

Loop

'xclapp.ActiveWorkbook.SaveAs myFile

'xclapp.ActiveWorkbook.Save

'xclapp.ActiveWorkbook.Close

Set xclwbk = Nothing

Set xclsheet = Nothing

'xclapp.Quit

set xclapp = Nothing