Hello,
I'm currently trying to write a script that will run the KSB1 transaction in SAP and download the output to a specific location. The problem is when the Save As Dialog box come up. Since that dialog box is generated by Windows, the SAP GUI scripting doesn't record it. Newer version of SAP has a solution for this, but unfortunately, I'm running an old one. I can get VBA to get to the point of opening up the dialogue box but I can't get it to fill in the filename and then hit save.
I've tried many things from sap archive, but the solutions of Script Man or Holger Kohn don't seem to work for me. I'm talking about those threads :
Script recording doesn'''t record save of file
Help required to activate saved workbook from SAP while VBA script is running
Holger Kohn's solution:
This solution of Holger Kohn actually works when the dialog is already there and I then start a new VBA procedure. The problem is that when VBA opens the Save As dialog box, it will stop executing the code as long as the user doesn't click on a location to save the file or presses cancel. So I can't make VBA call the "Auto_SaveAs_SAP" macro. What he suggested to someone else is to loop from another instance of Excel. I'm not sure what he means by that because you can't run to macros at the same time, unless it's from a separate computer altogether which me or another user running the report won't have access to.
If I'm missing something here, please let me know.
Here is Script Man's solution. In VBA:
In VBS:
I've tried many different instances of this and all it does is open the VBS file without actually doing anything else.
I'm kind of desperate now... Please help!
Note: Cross-post: SAP - Deal with the "Save As" dialog box with VBA and/or VBS
I'm currently trying to write a script that will run the KSB1 transaction in SAP and download the output to a specific location. The problem is when the Save As Dialog box come up. Since that dialog box is generated by Windows, the SAP GUI scripting doesn't record it. Newer version of SAP has a solution for this, but unfortunately, I'm running an old one. I can get VBA to get to the point of opening up the dialogue box but I can't get it to fill in the filename and then hit save.
I've tried many things from sap archive, but the solutions of Script Man or Holger Kohn don't seem to work for me. I'm talking about those threads :
Script recording doesn'''t record save of file
Help required to activate saved workbook from SAP while VBA script is running
Holger Kohn's solution:
Code:
''https://archive.sap.com/discussions/thread/3361288'
'Option Explicit
'
'Public Declare Function FindWindow Lib "User32" Alias "FindWindowA" _
'(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
'
''Declares a reference to a procedure implemented in an external file.
''Introduces a Lib clause, which identifies the external file (DLL or code resource) containing an external procedure.
''ByVal Specifies that an argument is passed in such a way that the called procedure or property cannot change the value of a variable underlying the argument in the calling code.
' 'ByVal does not prevent changing the value of a field or property.
' 'ByVal does prevent changing the value of c1 itself.
'
'Public Declare Function FindWindowEx Lib "User32" Alias "FindWindowExA" _
'(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
'
'Public Declare Function SendMessage Lib "User32" Alias "SendMessageA" _
'(ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
'
'Public Declare Function GetWindow Lib "User32" (ByVal hWnd As Long, ByVal wCmd As Long) As Long
'
'Public Declare Function GetWindowPlacement Lib "User32" (ByVal hWnd As Long, lpwndpl As WINDOWPLACEMENT) As Long
'
'Public Declare Function SetWindowPlacement Lib "User32" (ByVal hWnd As Long, lpwndpl As WINDOWPLACEMENT) As Long
'
'Public Declare Function SetForegroundWindow Lib "User32" (ByVal hWnd As Long) As Long
'
'Public Declare Function BringWindowToTop Lib "User32" (ByVal hWnd As Long) As Long
'
'Public Declare Function GetForegroundWindow Lib "User32" () As Long
'
'Const WM_SETTEXT As Long = &HC 'Sets the text of a window.
'Const BM_CLICK = &HF5
'Const GW_CHILD = 5
'Const GW_HWNDNEXT = 2
'
'Type RECT 'Type used at module level to define a user-defined data type containing one or more elements. Public by default.
' Left As Long
' Top As Long
' Right As Long
' Bottom As Long
'End Type
'
'Dim Ret As Long, OpenRet As Long, FlDwndHwnd As Long
'Dim ChildRet As Long
'Dim pos As RECT
'
'Const SW_SHOWNORMAL = 1
'Const SW_SHOWMINIMIZED = 2
'
'Public Type POINTAPI
' X As Long
' Y As Long
'End Type
'
'Public Type WINDOWPLACEMENT
' Length As Long
' flags As Long
' showCmd As Long
' ptMinPosition As POINTAPI
' ptMaxPosition As POINTAPI
' rcNormalPosition As RECT
'End Type
'
'Public Function ActivateWindow(xhWnd&) As Boolean
' Dim Result&, WndPlcmt As WINDOWPLACEMENT
'
' With WndPlcmt
' .Length = Len(WndPlcmt)
' Result = GetWindowPlacement(xhWnd, WndPlcmt)
' If Result Then
' If .showCmd = SW_SHOWMINIMIZED Then
' .flags = 0
' .showCmd = SW_SHOWNORMAL
' Result = SetWindowPlacement(xhWnd, WndPlcmt)
' Else
' Call SetForegroundWindow(xhWnd)
' Result = BringWindowToTop(xhWnd)
' End If
' If Result Then ActivateWindow = True
' End If
' End With
' End Function
'
'Public Function DeActivateWindow(xhWnd&) As Boolean
' Dim Result&, WndPlcmt As WINDOWPLACEMENT
'
' With WndPlcmt
' .Length = Len(WndPlcmt)
' Result = GetWindowPlacement(xhWnd, WndPlcmt)
' If Result Then
' .flags = 0
' .showCmd = SW_SHOWMINIMIZED
' Result = SetWindowPlacement(xhWnd, WndPlcmt)
' If Result Then DeActivateWindow = True
' End If
' End With
'End Function
'
'Sub SendMess(Message As String, hWnd As Long)
' Call SendMessage(hWnd, WM_SETTEXT, False, ByVal Message)
'End Sub
'
'Public Sub Auto_SaveAs_SAP()
'
'On Error GoTo err_handler
'
''******************************************************************************************************************
''* *
''* Automatic 'Save as' dialog from SAP => fillin SaveAsFileName and press 'Save' *
''* *
''******************************************************************************************************************
'
' Ret = FindWindow("#32770", "Save As")
'
' If Ret = 0 Then
' MsgBox "Save As Window Not Found"
' Exit Sub
' End If
'
' '==> Get the handle of ComboBoxEx32
' 'ComboBoxEx controls are combo box controls that provide native support for item images.
' ChildRet = FindWindowEx(Ret, ByVal 0&, "ComboBoxEx32", "")
' If ChildRet = 0 Then
' MsgBox "ComboBoxEx32 Not Found"
' Exit Sub
' End If
'
' '==> Get the handle of the Main ComboBox
' ChildRet = FindWindowEx(ChildRet, ByVal 0&, "ComboBox", "")
'
' If ChildRet = 0 Then
' MsgBox "ComboBox Window Not Found"
' Exit Sub
' End If
'
' '==> Get the handle of the Edit
' ChildRet = FindWindowEx(ChildRet, ByVal 0&, "Edit", "")
'
' If ChildRet = 0 Then
' MsgBox "Edit Window Not Found"
' Exit Sub
' End If
'
' ActivateWindow (Ret)
'
' '==> fillin FileName in 'Save As' Edit
' DoEvents
' SendMess FileSaveAsName, ChildRet
'
' '==> Get the handle of the Save Button in the Save As Dialog Box
' ChildRet = FindWindowEx(Ret, ByVal 0&, ByVal "Button", ByVal "Open as &read-only")
' ChildRet = GetWindow(ChildRet, GW_HWNDNEXT) ' This will be handle of '&Save'-Button
'
' '==> Check if we found it or not
' If ChildRet = 0 Then
' MsgBox "Save Button in Save As Window Not Found"
' Exit Sub
' End If
'
' '==> press Save-button
' SendMessage ChildRet, BM_CLICK, 0, ByVal 0&
' Exit Sub
'
'err_handler:
'MsgBox Err.Description
'End SubThis solution of Holger Kohn actually works when the dialog is already there and I then start a new VBA procedure. The problem is that when VBA opens the Save As dialog box, it will stop executing the code as long as the user doesn't click on a location to save the file or presses cancel. So I can't make VBA call the "Auto_SaveAs_SAP" macro. What he suggested to someone else is to loop from another instance of Excel. I'm not sure what he means by that because you can't run to macros at the same time, unless it's from a separate computer altogether which me or another user running the report won't have access to.
If I'm missing something here, please let me know.
Here is Script Man's solution. In VBA:
Code:
Set Wshell = CreateObject("WScript.Shell")
Wshell.Run """[Path of vbs file including extension]""" & [Path of export file including extension], 1, FalseIn VBS:
Code:
if Wscript.Arguments.count > 0 then
set fs = CreateObject("Scripting.FileSystemObject")
if fs.fileExists(WScript.arguments(0)) then
Set myfile = fs.GetFile(WScript.arguments(0))
myfile.Delete
end if
set Wshell = CreateObject("WScript.Shell")
Do
bWindowFound = Wshell.AppActivate("Save As")
WScript.Sleep 1000
Loop Until bWindowFound
Do
Wshell.sendkeys "{TAB}"
Wshell.sendkeys "{TAB}"
Wshell.sendkeys "{TAB}"
Wshell.sendkeys "{TAB}"
Wshell.appActivate "Save As"
Wshell.sendkeys WScript.arguments(0)
WScript.Sleep 400
Wshell.appActivate "Save As"
Wshell.sendkeys "%s"
WScript.Sleep 400
bWindowFound = Wshell.AppActivate("Save As")
WScript.Sleep 400
Loop Until not bWindowFound
end ifI've tried many different instances of this and all it does is open the VBS file without actually doing anything else.
I'm kind of desperate now... Please help!
Note: Cross-post: SAP - Deal with the "Save As" dialog box with VBA and/or VBS
Last edited:
