SAP Scripting & Excel Interaction in VBA

[#1]

Hi Viewer,

I want to export the data from SAP to Excel and start working on the exported excel via VBA but I need the code which will help VBA to wait until exported excel to open and then the next code runs.

Could you please help me on this?

The below codes i have used but it will wont wait until export file is ready to use:

Dim ThsWb As Workbook

Dim ThsWs As Worksheet

Set ThsWb = ThisWorkbook

Set ThsWs = ThsWb.Worksheets("Sheet1")

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

If Not IsObject(session) Then

Set session = Connection.Children(0)

End If

If IsObject(WScript) Then

WScript.ConnectObject session, "on"

WScript.ConnectObject App, "on"

End If

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

session.findById("wnd[0]/tbar[0]/okcd").Text = "fbl1n"

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

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

session.findById("wnd[1]/usr/txtENAME-LOW").Text = "GMAHN"

session.findById("wnd[1]/usr/txtENAME-LOW").SetFocus

session.findById("wnd[1]/usr/txtENAME-LOW").caretPosition = 5

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

session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").setCurrentCell 4, "TEXT"

session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").selectedRows = "4"

session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").doubleClickCurrentCell

session.findById("wnd[0]/usr/ctxtPA_STIDA").Text = ""

session.findById("wnd[0]/usr/ctxtPA_VARI").Text = "GKRNH OI KG"

session.findById("wnd[0]/usr/ctxtPA_VARI").SetFocus

session.findById("wnd[0]/usr/ctxtPA_VARI").caretPosition = 12

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]/usr/ctxtDY_PATH").Text = ThsWs.Range("E3").Value

session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = ThsWs.Range("E4").Value

session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 3

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

'Below Codes to work on exported excel

'activate the SAP exported data file

Workbooks(ThsWs.Range("E4").Value).Activate

Range("A1").CurrentRegion.Copy

'open the master file

Workbooks.Open (ThsWs.Range("E5").Value)

'add the sheet with current date and month

Worksheets.Add(after:=Sheets(Sheets.Count)).Name = Format(Date, "DD.MM")

'paste the data and concatenate

Range("A1").PasteSpecial

CutCopyMode = False

'delete last total row

Cells(Rows.Count, 1).End(xlUp).EntireRow.Delete

Columns(9).EntireColumn.Insert

Range("I1").Value = "CONCATENATE"

Range("I1").Font.Bold = True

Range("I1").Interior.Color = 65535

'Declaring a variable here because last row is calculated after the pasting the data

Dim rcount As Integer

rcount = Range("A1").CurrentRegion.Rows.Count

For i = 2 To rcount

Cells(i, 9).FormulaR1C1 = "=CONCATENATE(RC[-7],""_"",RC[-2])"

Next i

Range("A1").End(xlToRight).Select

Selection.Offset(0, 1).Select

Selection = Format(Date, "DD/MM/YYYY")

Range("AA1").Font.Bold = True

Range("AA1").Interior.Color = 65535

ActiveSheet.UsedRange.EntireColumn.AutoFit

'Vlookup to get the previouse comment

For J = 2 To rcount

On Error Resume Next

Cells(J, 27) = Application.WorksheetFunction.VLookup(Cells(J, 9), ActiveSheet.Previous.Range("H:AB"), 21, 0)

If Cells(J, 27).Value = "" Then

Cells(J, 27).Value = "NA"

End If

Next J

End Sub

[script_man]

Hi Joseph,

I tested again and I think it should work like this:

...
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]/usr/ctxtDY_PATH").Text = ThsWs.Range("E3").Value
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = ThsWs.Range("E4").Value
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 3
session.findById("wnd[1]/tbar[0]/btn[0]").press

myCount = Workbooks.Count
Do
 DoEvents
 Application.Wait Now + TimeSerial(0, 0, 5)
 DoEvents
 If Workbooks.Count > myCount Then Exit Do
Loop

'activate the SAP exported data file
Windows(ThsWs.Range("E4").Value).Activate
...

Regards, ScriptMan