Hi Everyone.. Been a member for almost 5 years, and have been amazed at all the incredible help that this forum has been able to provide me on my development over the years. From knowing nothing about VBA to providing incredible solutions for my coworkers, all thanks to everyone here. In fact, almost always, the best solutions to my issues have been found here.

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.


2017-10-25_10-27-14.jpg

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. 
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


ResumeSAP:
'If SAPGui is not running, Run SAPLOGON.EXE using Windows
If SAPGuiAuto Is Nothing Then

    Set WShell = CreateObject("WScript.shell")

    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"))
    Wend
    
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)
                                objSess.CreateSession
                                s = s + 1
                                   While objConn.Children.Count <= j
                                      Application.Wait (Now + TimeValue("0:00:1"))
                                   Wend
                                 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
                    Next
                
                End If
                
   Next
    Debug.Print "NO ACTIVE ERP CONNECTIONS"
    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

SAPReady:


    
objSess.FindById("wnd[0]").Maximize
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[0]/tbar[1]/btn[17]").Press
objSess.FindById("wnd[1]/usr/txtENAME-LOW").Text = ""
objSess.FindById("wnd[1]/tbar[0]/btn[8]").Press


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
        Next
objSess.FindById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").SelectedRows = r
objSess.FindById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").DoubleClickCurrentCell
objSess.FindById("wnd[0]/tbar[1]/btn[8]").Press


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").SetFocus
objSess.FindById("wnd[1]/usr/cmbG_LISTBOX").Key = "10"
objSess.FindById("wnd[1]/tbar[0]/btn[0]").Press

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

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



 
objSess.FindById("wnd[1]/tbar[0]/btn[11]").Press

 
MyExit:

Debug.Print "Attach= " & Attach

Set SAPGuiAuto = Nothing
Set objApp = Nothing
Set objSess = Nothing
Set objConn = Nothing


Exit Function


SAPOPEN:

Debug.Print "SAP IS NOT OPEN"

GoTo ResumeSAP

On Error GoTo 0

End Function