SAP Scripting & Excel Interaction in VBA

[former_member794876]

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

[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

[former_member794876]

Hi ScriptMan,

Thank you for your answer.

The SAP exported file is not opening and it is waiting status though i waited for 15 min.

It takes around 20 secs to open the excel file from SAP, Is it like i need to mention the secs?

Kindly suggest and the below code is the complete one after adding your code:

Sub TestComplete()

Dim ThsWb As Workbook

Dim ThsWs As Worksheet

Set ThsWb = ThisWorkbook

Set ThsWs = ThsWb.Worksheets("0073")

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]").maximize

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

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

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 3, "TEXT"

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

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

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

Do

Application.Wait now + TimeSerial(0, 0, 1)

On Error Resume Next

Set xlApp = GetObject(ThsWs.Range("E4").Value).Application

If Err.Number = 0 Then Exit Do

On Error GoTo 0

Loop

On Error GoTo 0

'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

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/MMM/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:AA"), 20, 0)

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

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

End If

Next J

End Sub

[script_man]

Hi Joseph,

one could solve it with the following queue.

...
'Below Codes to work on exported excel
'activate the SAP exported data file

Do
 Application.Wait Now + TimeSerial(0, 0, 1)
 On Error Resume Next
 Set xlApp = GetObject(ThsWs.Range("E4").Value).Application
 If Err.Number = 0 Then Exit Do
 On Error GoTo 0
Loop
On Error GoTo 0

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

Regards, ScriptMan