0

I have several transactions to automate and then paste into several tables. My code works for my first transaction but for the others I put the same code I just deleted the sheets in which I put it and it doesn't work at all. 

 Public SapGuiAuto, WScript, msgcol
  Public objGui  As GuiApplication
  Public objConn As GuiConnection
  Public objSess As GuiSession
  Public objSBar As GuiStatusbar
  Public objSheet As Worksheet
  Dim W_System
  Const fpath = "C:\Users\p100789\Documents\SAP\SAP GUI"
  Const ffilename = "text.txt"


   Sub OpenCSVFile()
  '
  ' Load the CSV extract

   With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & fpath & "\" & ffilename, Destination:=Range("$A$1"))
    .Name = "text"
    .FieldNames = True
    .RowNumbers = False
   [...]
End With

  With ActiveSheet
 .Columns(1).EntireColumn.Delete 
 'delete first column
 .Columns(1).EntireColumn.Insert
 .Rows("1:11").EntireRow.Delete 'delete first 9 rows
End With

End Sub



 Function Attach_Session() As Boolean
 Dim il, it
 Dim W_conn, W_Sess

 If W_System = "" Then
 Attach_Session = False
  Exit Function
 End If

  If Not objSess Is Nothing Then
    If objSess.Info.SystemName & objSess.Info.Client = W_System Then
    Attach_Session = True
    Exit Function
  End If
  End If

  If objGui Is Nothing Then
  Set SapGuiAuto = GetObject("SAPGUI")
  Set objGui = SapGuiAuto.GetScriptingEngine
  End If

     For il = 0 To objGui.Children.Count - 1
     Set W_conn = objGui.Children(il + 0)
      For it = 0 To W_conn.Children.Count - 1
      Set W_Sess = W_conn.Children(it + 0)
      If W_Sess.Info.SystemName & W_Sess.Info.Client = W_System Then
        Set objConn = objGui.Children(il + 0)
        Set objSess = objConn.Children(it + 0)
        Exit For
       End If
      Next
   Next

       If objSess Is Nothing Then
       MsgBox "No active session to system " + W_System + ", or scripting is not 
       enabled.", vbCritical + vbOKOnly
      Attach_Session = False
      Exit Function
      End If

      If IsObject(WScript) Then
      WScript.ConnectObject objSess, "on"
      WScript.ConnectObject objGui, "on"
      End If

      Set objSBar = objSess.findById("wnd[0]/sbar")
      objSess.findById("wnd[0]").maximize
      Attach_Session = True

     End Function

     Public Sub RunGUIScript()

     Dim W_Ret As Boolean
     Dim Société As String
     Sheets("Extraction").Select
      Société = Range("b9")

     ' Connect to SAP
     W_Ret = Attach_Session
     If Not W_Ret Then
     Exit Sub
     End If

   On Error GoTo myerr

   [....script]

   Exit Sub

    myerr:
    MsgBox "Error occured while retrieving data", vbCritical + vbOKOnly


  End Sub

  Sub StartExtract()

  ' Set the sid and client to connect to
    W_System = "P10320"
   ' Run the GUI script
     RunGUIScript
    ' End the GUI session
      objSess.EndTransaction
      'effacer contenu feuille temp
    Sheets("temp").Select
    Cells.Select
    Selection.Delete Shift:=xlUp
   ' Switch to the worksheet where the data is loaded to
    Sheets("temp").Select

   ' Load the CSV file
     OpenCSVFile


     [...]

      ' Update the time and date on the control worksheet
       Sheets("Extraction").Select
       Cells(2, 2).Value = Now()

in short, to automate another transaction I put the same code after, by filling the script with the new script. It doesn't work, does anyone have a solution?

Error

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
XDSSIOP
  • 91
  • 3
  • 11
  • 1
    "It doesn't work" is not enough information to help you with your problem. Do you get an error message? Do you get unexpected results? What actually happens? – Nacorid Jun 11 '19 at 11:32
  • @Nacorid The message that appears is "Error occured while retrieving data" (this is the error code I put in RunGUIScript) As a result, the first transation is automated but for the second one the script does not run. Then, the rest of the code works but pastes the data from the first transaction and not the new one. – XDSSIOP Jun 11 '19 at 11:49
  • You do realize you did not post the code that errors, right? That means we cannot help you. Have you stepped through the code (F8) yet to see where the error occurs? – Nacorid Jun 11 '19 at 11:57
  • @Nacorid No, but it's just a connection problem, I think. But how do we fix it? Because the code works very well with only one transaction – XDSSIOP Jun 11 '19 at 12:02
  • Step through the code to see where the error happens. We cannot help you because we don't have the sourcecode. – Nacorid Jun 11 '19 at 12:04
  • @Nacorid I edited my code with a screenshot of the error – XDSSIOP Jun 11 '19 at 12:14
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/194759/discussion-between-nacorid-and-xdssiop). – Nacorid Jun 11 '19 at 12:18

0 Answers0