VBA: Saving the VA05 result sheet as an Excel file

[former_member1097344]

Dear SAP experts,

So far I am using a VBA macro to transfer VA05 results into an Excel file.
I select the result table and copy it into the clipboard. From there I paste it into an Excel file.
Code in short (no error handling and so on):

Set SapGuiAuto = GetObject("SAPGUI")
Set SAPApplication = SapGuiAuto.GetScriptingEngine
Set Connection = SAPApplication.Children(0)
Set Session = Connection.Children(0)

'VA05
Session.findById("wnd[0]/tbar[0]/okcd").Text = "/nva05"
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]/usr/ctxtVBCOM-KUNDE").Text = "1000000000"
Session.findById("wnd[0]/usr/ctxtVBCOM-AUDAT").Text = "01.12.2021"
Session.findById("wnd[0]").sendVKey 0

'copy into clipboard
 Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").SelectAll
 Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").contextMenu
 Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectContextMenuItemByPosition "0" 'wählt den ersten Eintrag im Context-Menu aus

'paste into Excel file:
ThisWorkbook.Sheets("SAPtmp").Range("A2").PasteSpecial SkipBlanks:=True

This works well, but there are two main limitations:

1. The clipboard transfers only a limited number of rows
2. If I copy before VA05 has completely filled the result sheet rows, I am missing rows

For that reason I would prefer to directly save the VA05 result sheet in Excel via the macro. Is there a simple way of doing so? Any other idea to overcome above mentioned limitations?

Thank you,
Jolem