0

I am trying to get export from SAP. Once export done, i need to close the export file.I have written below code to execute the same thing, but my problem is that code is not waiting until file gets load and it is skipping the export file close. kindly help me to sort out this problem.

   Sub FBL5N()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False

    Dim sApplication
    Dim session
    Dim file As String
    Dim objShell As Object
    Dim strFolder As String
    Dim wb As Workbook
    Dim wbk As Workbook
    Dim ws As Worksheet
    Dim lrw As Integer
    Dim Wshell As Object

    Set objShell = CreateObject("Wscript.Shell")
    strFolder = objShell.SpecialFolders("mydocuments") & "\Temp\"

    file = strFolder & "\ARREPORT.xlsx"
    If FileExists(file) Then 'See above
     SetAttr file, vbNormal
     On Error Resume Next
        Kill file
        On Error Resume Next
    End If


    If Not IsObject(sApplication) Then
       Set SapGuiAuto = GetObject("SAPGUI")
       Set sApplication = SapGuiAuto.GetScriptingEngine
    End If

    If Not IsObject(Connection) Then
       Set Connection = sApplication.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 sApplication, "on"
    End If

    session.findById("wnd[0]/tbar[0]/okcd").Text = "/nFBL5N"
    session.findById("wnd[0]").sendVKey 0
    session.findById("wnd[0]/usr/ctxtDD_KUNNR-LOW").Text = ThisWorkbook.Sheets("Formulated").Range("R9")
    session.findById("wnd[0]/usr/ctxtDD_KUNNR-HIGH").Text = ThisWorkbook.Sheets("Formulated").Range("R10")
    session.findById("wnd[0]/usr/ctxtDD_BUKRS-LOW").Text = ThisWorkbook.Sheets("Formulated").Range("R8")
    session.findById("wnd[0]/usr/ctxtPA_STIDA").Text = ThisWorkbook.Sheets("Formulated").Range("R11")
    session.findById("wnd[0]/usr/ctxtPA_VARI").Text = ThisWorkbook.Sheets("Formulated").Range("R12")
    session.findById("wnd[0]/usr/ctxtPA_VARI").SetFocus
    session.findById("wnd[0]/usr/ctxtPA_VARI").caretPosition = 12
    session.findById("wnd[0]/mbar/menu[0]/menu[0]").Select
    session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
    session.findById("wnd[1]/usr/ctxtDY_PATH").Text = strFolder
    session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "export.xlsx"
    session.findById("wnd[1]").sendVKey 0
    Application.Wait Now() + TimeValue("00:00:01")

    For Each wb In Application.Workbooks
    If wb.Name = "export.xlsx" Then
    wb.Close
     End If
     Next wb
     End Sub
Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
RK144
  • 47
  • 10
  • 1
    Look into properly using "Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)" alond with "DoEvents". Always a life saver for me. – Ricky Oct 03 '18 at 07:33
  • Hi Ricky, Thanks for help. I am not expert in VBA. Could you please explain how can resolve this with your solution. – RK144 Oct 03 '18 at 16:48
  • I have updated your code but it says it is under peer review until it post. If it does not update by tomorrow i will figure a new route. I gave you a good head start but i did not do all the leg work. That is the fun part of learning. I hope it gets posted soon. Have a great evening! – Ricky Oct 04 '18 at 06:54
  • Hi Ricky,Sorry for that, I tried with whatever you said in first comment, but still i am getting issue. It may because of wrong placement. So that i asked you. thanks for help. I am waiting for updated code. Hope it will post soon. – RK144 Oct 04 '18 at 14:17
  • Your biggest issue is the simple one actually. You have turned off everything and not turned it back on. at the bottom of your code add the following but change false to true With Application .ScreenUpdating = True .DisplayAlerts = True .AskToUpdateLinks = True end with – Ricky Oct 04 '18 at 16:36
  • Great!!!Thank you so much.. i forgot to do that.. and i included your sugessions (Kernel32 and Do events).. now it is working fine.once again thank you very much. – RK144 Oct 04 '18 at 16:55

1 Answers1

0

I will just make an example. Since i guess my edit a little over board and i respect this site is here to teach not just have people do all the work for someone. More so guide them so they actually learn what all the changes i made mean and why i did them.

'This is the fun stuff using your computer to sleep in milliseconds. Since we are teaching and i am not going to assume anything. Here is the code for both versions of excel.

'Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)' API Call for 64bit excel
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) ' API Call for 32bit excel

' This is turning off all your screen update functions

With Application
     .ScreenUpdating = False
     .DisplayAlerts = False
     .AskToUpdateLinks = False
End with

' Add Code here
' You can remove your application.wait and try

Sleep 100  ' this is milliseconds so you may have to fine tune it by adding more time or less
Doevents ' Doevents lets the macro keep running and will keep "Not Responding" from showing up at the top of your workbook.

BeforeExit: ' I always add a BeforeExit: So i Don't forget to turn my settings back on to see my hard work

Set objShell = nothing ' It is good practice to always release your objects as well

With Application
     .ScreenUpdating = true
     .DisplayAlerts = true
     .AskToUpdateLinks = true
End with

Can you explain to me a little about SAP? I see your code and i see your calling keys which i am not sure if that is for SAP or excel. It is not good practice if you can avoid it to ever call keyboard functions.

Ricky
  • 98
  • 4