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 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