SAP - Deal with the "Save As" dialog box with VBA and/or VBS

YounesB3

Board Regular
Joined
Mar 28, 2012
Messages
138
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:

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 Sub

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:

Code:
Set Wshell = CreateObject("WScript.Shell")
Wshell.Run """[Path of vbs file including extension]""" & [Path of export file including extension], 1, False

In 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 if

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
 
Last edited:

YounesB3

Board Regular
Joined
Mar 28, 2012
Messages
138
All the code does is detect the SaveAs dialog as soon as it comes up and hit the Save Button.

Try commenting out the 4th If line and see what you get :

'If FindWindowEx(wParam, 0, "DUIViewWndClassName", vbNullString) Then'End If

Leave the two lines between them ie: UnhookWindowsHookEx and PostMessage.

Still doesn't work. I did it with F8 this time and the sethook seems to activate only at the "Workbooks.Open Filename:=KSB1R14" line.

Meaning it doesn't recognize the "session.findById("wnd[1]/tbar[0]/btn[0]").press" line.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

YounesB3

Board Regular
Joined
Mar 28, 2012
Messages
138
Hello, I finished my code. Everything works except for this. Any help would be appreciated.
 

drjudym

New Member
Joined
Sep 26, 2017
Messages
1
Hi All,

I am struggling same problem - only solution I found is following - it works but we know about sendkeys. After declaring this:

Code:
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As Long) As Long

I have this code:
Code:
a = Cells(i, 3).Value [COLOR=#008000]'path to save + name - to be input in file name[/COLOR]

session.findById("wnd[0]/tbar[1]/btn[44]").press [COLOR=#008000]'after this save as window comes up[/COLOR]
Application.Wait (Now + TimeValue("00:00:02"))


hWnd = FindWindow(vbNullString, "Select destination") [COLOR=#008000]'what I am saving is a PDF file using a PDFCreator and the windows name is actually "Select destination" could be "Save As" too, the problem was same, script was waiting for my action[/COLOR]
SetForegroundWindow (hWnd) [COLOR=#008000]'puts save as as active window[/COLOR]
SendKeys "%n" [COLOR=#008000]'activates file name field
[/COLOR]

Application.Wait (Now + TimeValue("00:00:01"))
SendKeys a [COLOR=#008000]'enters path + name[/COLOR]
Application.Wait (Now + TimeValue("00:00:01"))
SendKeys "~" [COLOR=#008000]'press enter[/COLOR]
Application.Wait (Now + TimeValue("00:00:01"))


a = Null
hWnd = Null

While script is running I am drinking a tea or checking facebook to not interrupt sendkeys.
 

danici

New Member
Joined
Dec 6, 2017
Messages
1
Hi!
I'm having some problems with the first code,
I receive the Compile Error: Only comments may appear after End Sub, End Function, or End Property.
Do you know what might be causing it?

Thank you
 

YounesB3

Board Regular
Joined
Mar 28, 2012
Messages
138
Hi, I would recommend trying another way to export data in excel from SAP. There sometimes hidden way in SAP to export a document that are available via code, but not within the menus depending on the transaction. The format might be ugly, but at least it works.
 

Forum statistics

Threads
1,147,498
Messages
5,741,504
Members
423,663
Latest member
kaveh87rsh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top