SAP VBA Save As
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
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