How to download tables from different systems with one SAP GUI script

[Stefan-Schnell]

Here I show you how to select a special session to do SAP GUI Scripting activities inside it: http://scn.sap.com/docs/DOC-33425.

Here I show you how to write each table with SAP GUI Scripting to a CSV file: http://scn.sap.com/docs/DOC-26251.

Now I merge these tips to download tables from different systems. It is only necessary to be logged on the systems. We start with the main program which is followed by an include file, which stores the well-known routines of the tips above.

'-Begin-----------------------------------------------------------------

'-

'- This example shows how to download a table in CSV format from the

'- first accessible session of all open connections. With one call you

'- get any table in CSV format from all open connections.

'- Hint: If several connections to one system are open, the identical

'- data content from the table is stored multiple times.

'-

'- Author: Stefan Schnell

'- Site: www.stschnell.de

'-

'-----------------------------------------------------------------------



  '-Directives----------------------------------------------------------

    Option Explicit

   

  '-Global variables----------------------------------------------------

    Dim RunDir 



  '-Sub Include---------------------------------------------------------

    Sub Include(IncludeName)

   

      '-Locale variables------------------------------------------------

        Dim FSO, oFile

   

      Set FSO = CreateObject("Scripting.FileSystemObject")

      If IsObject(FSO) Then

        If FSO.FileExists(IncludeName) Then

          Set oFile = FSO.OpenTextFile(IncludeName)

          If IsObject(oFile) Then

            ExecuteGlobal oFile.ReadAll()

            oFile.Close

            Set oFile = Nothing

          End If

        End If

        Set FSO = Nothing

      End If



    End Sub



  '-Include-------------------------------------------------------------

    RunDir = WScript.ScriptFullName

    RunDir = Left(RunDir, InStr(RunDir, WScript.ScriptName) - 2)

    Include Rundir & "\Routines.inc"



  '-Main----------------------------------------------------------------

  '-

  '- Call here the table name and the path to store the content of the

  '- table as file in CSV format

  '-

  '---------------------------------------------------------------------

    GetTable "SFLIGHT", "D:\\Dummy\\"

   

'-End-------------------------------------------------------------------

The include file Routines.inc starts here:



'-Begin-----------------------------------------------------------------

'-

'- This include file stores routines to get table content from a

'- session and to save it as CSV file

'-

'- Author: Stefan Schnell

'- Site: www.stschnell.de

'-

'-----------------------------------------------------------------------



  '-Sub ReadTableInFile-------------------------------------------------

  '-

  '- Read table from the session and save the content in CSV format

  '-

  '---------------------------------------------------------------------

    Sub ReadTableInFile(session, TableName, FileName)

   

      '-Local variables-------------------------------------------------

        Dim Menu, Einstellungen, BenutzerPar, ALVGridView, table

        Dim Rows, Cols, oFile, CSVFile, Columns, i, j

     

      '-Reset the session-----------------------------------------------

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

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



      '-Open TAC SE16---------------------------------------------------

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

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

       

      '-Set table name--------------------------------------------------

        session.findById("wnd[0]/usr/ctxtDATABROWSE-TABLENAME").text = _

          TableName



      '-View table------------------------------------------------------

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



      '-Set witdh of output list----------------------------------------

        session.findById("wnd[0]/usr/ctxtLIST_BRE").text = "9999"



      '-Set the maximum number of hits----------------------------------

        session.findById("wnd[0]/usr/txtMAX_SEL").text = "999999"



      '-Execute--------------------------------------------------------- 

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

       

      '-Set display to ALV Grid view------------------------------------

        '-Open user specific parameters dialog--------------------------

        '-

        '- Attention: Here is a language specific code, customize it.

        '- Actual in German, as comments in English

        '-

        '---------------------------------------------------------------

          Set Menu = session.findById("wnd[0]/mbar")

          Set Einstellungen = Menu.FindByName("Einstellungen", "GuiMenu")

          'Set Einstellungen = Menu.FindByName("Settings", "GuiMenu")

          Set BenutzerPar = Einstellungen.FindByName("Benutzerparameter...", _

            "GuiMenu")

          'Set BenutzerPar = Einstellungen.FindByName("User Parameters...", _

          '  "GuiMenu")

          BenutzerPar.Select()

         

        '-Set the output list format------------------------------------

          Set ALVGridView = session.findById("wnd[1]/usr/tabsG_TABSTRIP/" & _

            "tabp0400/ssubTOOLAREA:SAPLWB_CUSTOMIZING:0400/radRSEUMOD-TBALV_GRID")

          If ALVGridView.Selected = vbFalse Then

            ALVGridView.select()

          End If



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

         

        Set BenutzerPar = Nothing

        Set Einstellungen = Nothing

        Set Menu = Nothing

       

      '-Get rows and columns--------------------------------------------

        Set table = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell")

        Rows = table.RowCount() - 1

        Cols = table.ColumnCount() - 1

       

      '-Write the table to a CSV file-----------------------------------

        Set oFile = CreateObject("Scripting.FileSystemObject")

        If IsObject(oFile) Then

          Set CSVFile = oFile.CreateTextFile(FileName, True)

          If IsObject(CSVFile) Then

         

            '-Get the title of all columns in the first line------------

              Set Columns = table.ColumnOrder()

              For j = 0 To Cols

                If j = Cols Then

                  CSVFile.Write(CStr(Columns(j)))

                Else

                  CSVFile.Write(CStr(Columns(j)) & ";")

                End If

              Next

              CSVFile.WriteLine("")

             

            For i = 0 To Rows



              For j = 0 To Cols

                If j = Cols Then

                  CSVFile.Write(table.GetCellValue(i, _

                    CStr(Columns(j))))

                Else

                  CSVFile.Write(table.GetCellValue(i, _

                    CStr(Columns(j))) & ";")

                End If

              Next

             

              '-Each 32 lines actualize the grid------------------------

                If i Mod 32 = 0 Then

                  table.SetCurrentCell i, CStr(Columns(0))

                End If

               

              '-Carriage and return after a line------------------------

                If i <> Rows Then

                  CSVFile.WriteLine("")

                End If

               

            Next

            CSVFile.Close

            Set CSVFile = Nothing

           

          End If

          Set oFile = Nothing

        End If

       

        Set ALVGridView = Nothing

        Set Columns = Nothing

        Set table = Nothing

       

    End Sub



  '-Sub GetTable--------------------------------------------------------

  '-

  '- Get a table from the first accessible session of all open

  '- connections

  '-

  '---------------------------------------------------------------------

    Sub GetTable(TableName, Path)

   

      '-Local variables-------------------------------------------------

        Dim SapAppl, SapGuiAuto, CollCon, i, oCon, CollSes, j, oSes

        Dim oSesInf, SID, SesNo, FileName



      Set SapGuiAuto = GetObject("SAPGUI")

      If Not IsObject(SapGuiAuto) Then

        Exit Sub

      End If



      Set SapAppl = SapGuiAuto.GetScriptingEngine

      If Not IsObject(SapAppl) Then

        Exit Sub

      End If



      Set CollCon = SapAppl.Connections()

      If Not IsObject(CollCon) Then

        Exit Sub

      End If

     

      '-Loop over connections-------------------------------------------

        For i = 0 To CollCon.Count() - 1



          Set oCon = SapAppl.Children(CLng(i))

          If Not IsObject(oCon) Then

            Exit Sub

          End If 



          Set CollSes = oCon.Sessions()

          If Not IsObject(CollSes) Then

            Exit Sub

          End If 

       

          '-Loop over sessions------------------------------------------

            For j = 0 To CollSes.Count() - 1

       

              Set oSes = oCon.Children(CLng(j))

              If Not IsObject(oSes) Then

                Exit Sub

              End If 



              If oSes.Busy() = vbFalse Then

                Set oSesInf = oSes.Info()

                If IsObject(oSesInf) Then

                  SID = oSesInf.SystemName()

                  FileName = Replace(TableName, "/", "_")

                  ReadTableInFile oSes, TableName, Path & FileName & _

                    "." & SID & ".csv"

                  Exit For 

                End If

              End If



            Next



        Next



    End Sub



'-End-------------------------------------------------------------------