Showing results for 
Search instead for 
Did you mean: 


Former Member
0 Kudos

Now the real issue. I have beaten myself on my searching abilities for the last few days, and I just decided to give up looking, and just ask. I know there have been others that have posted about the Save As in SAP when pulling a report down to excel, but nothing has matched the issue I am having.

In reality, my script works flawlessly for myself and one other in my department. Three others, have encountered issues. I was able to troubleshoot a little bit and I think I know what is causing it, but I dont know how to go about fixing it to ensure smooth functionality for everyone.

Sometimes when downloading reports into an excel format from SAP (using the MB_Export" and "XXL"), the dialog to enter a directory/file name shows up. This allows someone to generate a new file, replace an existing file, Extend (append) an existing report. This is the dialog window that works for me, and has been programmed into my script.

Other times (seems random to me), the Windows Save As dialog box shows up, which causes an issue for others that are helping me test. Since the fields in the script are not in the Save As dialog, i get an issue. Does anyone know why this dialog would show up vs a windows save as, or what determines it?

Is there any way to program in VBS/VBA to get this to default to this dialog box? "Wnd[1]".

Please help.. Its hurting my head.

<code>Option Explicit
Public SAPGuiAuto As Object, WScript, WShell, WinTitle
Public objApp  As GuiApplication
Public objConn As GuiConnection
Public objSess As GuiSession
Public objSBar As GuiStatusbar
Public objWS As Worksheet, objWB As Workbook
Dim FilePath

Function Attach()

Dim W_System, W_Conn, W_Sess, SysID, TCode, TVar, SaveAsPath, SaveAsName
Dim x, s, i, j
Dim GOSAP(2) As Integer
Set objWB = ActiveWorkbook
Set objWS = Control
W_System = objWS.Range("System_Client").Value
WinTitle = objWS.Range("System").Value
SysID = objWS.Range("System_ID").Value
TCode = objWS.Range("T_Code").Value
TVar = objWS.Range("T_Variant").Value
SaveAsPath = objWS.Range("Output_Dest_Path")
SaveAsName = objWS.Range("Output_Name")

Debug.Print W_System, WinTitle, SysID, TCode, TVar
Set SAPGuiAuto = Nothing

'Check to see if SAPGui is running.
On Error GoTo SAPOPEN 'Go to errorhandling
Set SAPGuiAuto = GetObject("SAPGUI")
On Error GoTo 0
'If SAPGui is not running, Run SAPLOGON.EXE using Windows
If SAPGuiAuto Is Nothing Then

    Set WShell = CreateObject("")

    If IsObject(WShell) Then
' Location of SAP Folder
        WShell.exec "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe"

    End If
'Wait for the SAPGui Login to load before continuing script
    While Not WShell.AppActivate(WinTitle)
        Application.Wait (Now + TimeValue("0:00:1"))
End If

'Activate the GUI
Set SAPGuiAuto = GetObject("SAPGUI")
Set objApp = SAPGuiAuto.GetScriptingEngine

'Check to see if there is any instances of SAP Open. If not, open a new connection to ERP
If objApp.Children.Count() = 0 Then
    Set objConn = objApp.OpenConnection(SysID)
    Set objSess = objConn.Children(0)
    objSess.FindById("wnd[0]").SendVKey 0
    Debug.Print "New Window Opened"
ElseIf objApp.Children.Count > 0 Then 'If there is an open connection, loop through to find if there is an active connection to ERP
        Debug.Print "Find if there is a correct Connection/Session"
   For x = 0 To objApp.Children.Count() - 1 'Each open connection
        Set W_Conn = objApp.Children(x + 0)
            Debug.Print W_Conn.Description
               If W_Conn.Description = SysID Then 'Check if the connection is to ERP.
                    For s = 0 To W_Conn.Children.Count() - 1 'If ERP is a connection, check sessions for blank/correct transaction sessions
                        Set W_Sess = W_Conn.Children(s + 0)
                            If W_Sess.Info.SystemName & W_Sess.Info.Client = W_System And W_Sess.Info.Transaction = TCode Then
                                Set objConn = objApp.Children(x + 0)
                                Set objSess = objConn.Children(s + 0)
                                Attach = 1
                                GoTo SAPReady ' Exit. Code 1
                            ElseIf W_Sess.Info.SystemName & W_Sess.Info.Client = W_System And W_Sess.Info.Transaction = "SESSION_MANAGER" Then
                                    Set objConn = objApp.Children(x + 0)
                                    Set objSess = objConn.Children(s + 0)
                                    Attach = 2
                                    Debug.Print "Correct System Found & Blank Session"
                                    Debug.Print x
                                    Debug.Print s
                                    GoTo SAPReady
                                ElseIf W_Sess.Info.SystemName & W_Sess.Info.Client = W_System Then
                                Set objConn = objApp.Children(x + 0)
                                    j = objConn.Children.Count
                                Set objSess = objConn.Children(s + 0)
                                s = s + 1
                                   While objConn.Children.Count <= j
                                      Application.Wait (Now + TimeValue("0:00:1"))
                                 Debug.Print "J= " & j
                                 Debug.Print objConn.Children.Count
                                 Debug.Print "XXXXXX - " & objApp.Connections.Item(0).Sessions.Count
                                     Set objSess = objConn.Children(s + 0)
                                Attach = 3
                                Debug.Print "Correct System Found.No Blank Sessions. New Window Created"
                                Debug.Print x
                                Debug.Print s
                                GoTo SAPReady
                            End If
                End If
    Set objConn = objApp.OpenConnection(SysID)
    Set objSess = objConn.Children(0)
    objSess.FindById("wnd[0]").SendVKey 0
    Debug.Print x
    Debug.Print s
    Debug.Print objSess.Info.SystemName
    Debug.Print objSess.Info.Client
    Attach = 4
End If

objSess.FindById("wnd[0]/tbar[0]/okcd").Text = "/n" & TCode
objSess.FindById("wnd[0]").SendVKey 0
Dim layout, r As Integer, LayoutVariant, arows

objSess.FindById("wnd[1]/usr/txtENAME-LOW").Text = ""
Set layout = objSess.FindById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell")
    arows = layout.RowCount()
        For r = 0 To arows - 1

            LayoutVariant = layout.GetCellValue(r, "VARIANT")
            If LayoutVariant = TVar Then
                layout.CurrentCellRow = r
                Exit For
            End If
objSess.FindById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").SelectedRows = r
objSess.FindById("wnd[0]/usr/shell/shellcont/shell").PressToolbarContextButton "&MB_EXPORT"
objSess.FindById("wnd[0]/usr/shell/shellcont/shell").SelectContextMenuItem "&XXL"
objSess.FindById("wnd[1]/usr/cmbG_LISTBOX").Key = "10"

objSess.FindById("wnd[1]/usr/ctxtDY_PATH").Text = SaveAsPath & "\"

objSess.FindById("wnd[1]/usr/ctxtDY_FILENAME").Text = SaveAsName & ".xlsx"


Debug.Print "Attach= " & Attach

Set SAPGuiAuto = Nothing
Set objApp = Nothing
Set objSess = Nothing
Set objConn = Nothing
Exit Function

Debug.Print "SAP IS NOT OPEN"

GoTo ResumeSAP

On Error GoTo 0

End Function

Accepted Solutions (0)

Answers (1)

Answers (1)

Active Contributor
0 Kudos

Hi Michael,

the first thing to check is:

For your script to work properly, all options should be disabled.