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

[former_member794876]

Hi ScriptMan,

Thank you so much for your response.

Sure I will check this well.

However I have got below codes from my colleague and it is working now.

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

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

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

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

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

'Codes given by Kushal

Call Wait(12)

DoEvents

Dim wb_Output As Workbook

For Each Wb In Application.Workbooks

If Wb.Name = Range("d4").Value Then

Set wb_Output = Workbooks(Wb.Name)

wb_Output.Activate

End If

Next Wb

If wb_Output Is Nothing Then

Call Wait(12)

End If

'Add the below codes in different Sub procedure

Sub Wait(seconds As Integer)

Dim now As Long

now = Timer()

Do

DoEvents

Loop While (Timer < now + seconds)

End Sub