10 Replies Latest reply: Feb 10, 2011 10:18 PM by Brian Devereaux RSS

Call Transaction from Excel VBA macro and download ALV list object results

Glenn Peterson
Currently Being Moderated

I have a situation that must be very common u2013 but I canu2019t find any clear information on how to get it done! 

 

We frequently run SAP transactions, download the results (orders or inventory) into Excel, do some calculations and create a spreadsheet report. 

 

I would like to automate this process using Excel VBA so that a macro will perform these steps:

 

1. Run our custom SAP report "YSD033" that summarizes orders using the previous day as the [From Date] parameter.  (The user already has an active ECC 6 R3 session running.)   If possible, can the TC be run using a specific variant "G111BIZ" ?

 

2. Download the list object that appears in an ALV grid as a table to an empty spreadsheet in the active workbook (export XXL list object)

 

3. Save the resulting workbook and close Excel.

 

Should the solution use u201Ccall transactionu201D or a GuiXT script?

 

Any help would be much appreciated, and some sample VBA code would be great!

Thanks.

 

Glenn

  • Re: Call Transaction from Excel VBA macro and download ALV list object results
    Mario Rappi
    Currently Being Moderated

    You should be able to do that with VBS from SAP itself (and not with VBA from Excel):

     

    Go to the script recdorder: ALT+F12 > "Script Recording and playback", press the recording (red) button

    Perform all you actions: go to your YSD033 screen, pull the variant, run the report, save it in Excel, close transaction.

    Then stop recording.

    The script (a VBS program) will be saved in your default SAP directory (but you can change this location esaily).

    To run the script either play it from SAP (ALT+F12, etc except that instead of recrding it, play it) or you can also run it from Windows (if SAP is open) by double clicking the vbs file.

     

    Edited by: Mario Rappi on Feb 26, 2010 7:18 PM

    • Re: Call Transaction from Excel VBA macro and download ALV list object results
      Glenn Peterson
      Currently Being Moderated

      Mario, Thanks for the reply but I have two hurdles:

      1- the alt-F12 record option is grayed out (security)  and the <record> button on GuiXT did not create a VBS.

      2- I will run much additional processing with the data using VBA after the download(s) complete.

        Even if a separate script ran the download, it would be cleaner with it all in one VBA package.

       

      Also, having control over the initiation of the transaction allows me to change the parameters (company holidays, various number of days, etc).

       

       

      FYI - GuiXT Version 2009 Q1 1 did create InputScript.txt, but I think it is of little use.  here's a sample:

      // ABAP: Variant Directory of Program YSD033

      Screen SAPLSVAR.0600

        Enter control="CONTAINERCTRL.GridViewCtrl" item="0000000003;VARIANT;3;1" event="3"

      • Re: Call Transaction from Excel VBA macro and download ALV list object results
        Mario Rappi
        Currently Being Moderated

        If the "Script Recording..." options is grayed out then they probably won't let you do anything there.

        See if changing the scripting options helps, but I doubt it will (ALT+F12 > Options > Scripting make sure scripting is enabled).

         

        See if they let you run this:

        (save this as script.vbs using notepad)

        ********************

        If Not IsObject(application) Then

           Set SapGuiAuto  = GetObject("SAPGUI")

           Set application = SapGuiAuto.GetScriptingEngine

        End If

        If Not IsObject(connection) Then

           Set connection = application.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 application, "on"

        End If

        session.findById("wnd[0]/tbar[0]/okcd").text = "/nYSD033"

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

        ************************

         

        f you can, there is hope.

         

        I am not familar with GuiXT but it apparently creates an ABAP program, as opposed to a VBS

        • Re: Call Transaction from Excel VBA macro and download ALV list object results
          Glenn Peterson
          Currently Being Moderated

          Good suggestion, but

          I get "permission denied" for   SapGuiAuto.GetScriptingEngine

           

          I also tried the method below, but received this RFC error message:

          User PPPPPPP  has no RFC authorization for function group SYST.

           

          Sub LoginCheck()

          If login = False Then

              ' Setting the necessary variables for R/3 connection

              Set objBAPICortrol = CreateObject("SAP.Functions")

              Set objConnection = objBAPICortrol.Connection

              ' Establish a connection

          If objConnection.Logon(0, False) Then

              login = True

              MsgBox "Connection Established"

              CommandButton1.Caption = "Disconnect"

          End If

          Else

              CommandButton1.Caption = "Connect 2 SAP"

              login = False

              objConnection.Logoff

              Set objConnection = Nothing

              Set objBAPICortrol = Nothing

          End If

          End Sub

           

          I was told that these kinds of authority open up too big of a window that can't be monitored adequately...

           

          I'm considering an approach like what is below if I can't convince security to grant me permissions...

              Application.Wait Now + TimeValue("00:00:01")

              SendKeys EnterKey, False

           

           

          Since blocked RFC security settings are preventing the solution from being installed, I am markgin this question as answered. 

          I will post different questions about 1. how to convince the security team that it will be safe to allow the use of RFC calls, and /or 2. how use some windows-level scripting code to run the SAP jobs.

          Thanks.

           

          Edited by: GlennWebster on Mar 1, 2010 4:34 PM

  • Re: Call Transaction from Excel VBA macro and download ALV list object results
    Glenn Peterson
    Currently Being Moderated

    Since blocked RFC security settings are preventing the solution from being installed, I am marking this question as answered.

    I will post different questions about 1. how to convince the security team that it will be safe to allow the use of RFC calls, and /or 2. how to use some windows-level scripting code to run the SAP jobs.

    Thanks.

    • Re: Call Transaction from Excel VBA macro and download ALV list object results
      Mario Rappi
      Currently Being Moderated

      Hitting the IT/security wall is always hard. Workarounds are always difficult.

       

      As far as the Windows level scripting you can start with something like this:

      Set WshShell = WScript.CreateObject("WScript.Shell")

      wshshell.sendkeys "%"

      wshshell.sendkeys "/nYSD033"

      wshshell.sendkeys ""

       

      Since YSD033 is a custom transaction I have no idea how to continue. However, doing it without the SAP Gui will be very difficult even for standard transactions, I think.

      Good Luck.

      • Re: Call Transaction from Excel VBA macro and download ALV list object results
        Brian Devereaux
        Currently Being Moderated

        Hmm ive tried these options and ive had to use the send keys solution but im having trouble with A.) switching to the downloaded sheet from SAP if i try to call for it while its exporting the sheet shows up blank and if put in a wait function it shows up blank. the best ive been able to do is simply call for excel with appactive but this dosent always bring the downloaded spreadsheet to the front. B.) calling back to Sap regardless of what window its on so i do something else. C.) getting the code to wait until sap has loaded the report.  Does anyone have any suggestions?

         

        SendKeys ShiftKey & "", True

        DoEvents

        Application.Wait Now() + TimeValue("00:00:01")

        SendKeys "", True DoEvents Application.Wait Now() + TimeValue("00:00:01") SendKeys "", True

        DoEvents

        Application.Wait Now() + TimeValue("00:00:01")

        SendKeys "", True

        DoEvents

        AppActivate "Microsoft Excel"

        'Windows("Worksheet in Basis (1)").Activate

  • Re: Call Transaction from Excel VBA macro and download ALV list object results
    JD li
    Currently Being Moderated

    can this be done by using DOI interface?

    you can upload the excel file with VBA into SAP,

    then you can write code using DOI to fill in data for calculation.

Actions