I'm trying to automate SAP GUI with VBA to extract some reports from Transactions (FBL5N-FBL3N).
There are two main problems:
1- Once SAP GUI Extraction is done it automatically opens the Excel sheet for this extraction
2- Excel Gives an error in the end of the loop but it extracts everything successfully (run error ..619 )
I want to Code the VBA as it extracts everything without opening the excel sheet and gives a MsgBox says "Done" once the extraction is done or once it encounters a blank Cell.
The code below is what I've done so far.
Public Sub fbl3n()
'setting the connection with sap:
Dim App, Connection, session As Object
Set SapGuiAuto = GetObject("SAPGUI")
Set App = SapGuiAuto.GetScriptingEngine
Set Connection = App.Children(0)
Set session = Connection.Children(0)
'Fbl3n
Dim lastrow As Integer
Dim Z As Long
lastrow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row
For Z = 1 To lastrow
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = Cells(Z + 1, 4).Value
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 17
session.findById("wnd[1]/usr/txtV-LOW").Text = Cells(Z + 1, 1).Value
session.findById("wnd[1]/usr/txtV-LOW").caretPosition = 6
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[0]/usr/ctxtPA_STIDA").Text = Cells(Z + 1, 2).Value
session.findById("wnd[0]/usr/ctxtPA_STIDA").SetFocus
session.findById("wnd[0]/usr/ctxtPA_STIDA").caretPosition = 10
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = Cells(Z + 1, 3).Value
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 22
session.findById("wnd[1]/tbar[0]/btn[0]").press
Call excel
Next
If Not session.findById("wnd[1]/tbar[0]/btn[0]", False) Is Nothing Then
session.findById("wnd[1]/tbar[0]/btn[0]").press
End If
MsgBox ("Done")
End Sub
Public Sub clear()
Range("A2:D10000").Select
Selection.ClearContents
End Sub
Public Sub excel()
Dim xlApp As Object
Application.Wait Now + TimeSerial(0, 0, 5)
myPath = Cells(1, "K").Value
myFileName = Cells(Z + 1, 3).Value
Set xlApp = GetObject(myPath & myFileName).Application
xlApp.Workbooks(1).Close False
xlApp.Quit
End Sub
