Help!!! SAP report extraction through pdf(Print Option) using Macro

[Former Member]

Hi,

Please someone could help me in extracting one of SAP report using Print option(spooling) using Macro. I have recorded a macro for print, giving the destination as "LOCL" and I get a popup "Save PDF File As" dialog box which does not run in Macro. The Macro runs only upto LOCL printer selection. I want a Macro to run even when the prompt for "Save PDF File As" dialog box with the directory and file name to be picked from excel and saves automatically in the directory choosen... Please someone could help me with the code for it?

TIA

Merci

[holger_khn · Accepted solution]

Hello.

MS COM DIALOG are not supported by SAP SCRIPTING so far. In a new SP for SAPBASIS this will be implemented.

As a Workaround there is one possibility to use sendkey. The other possibility is to have an second Excel instance open and let an macrso catch the SaveAs box initiated by SAP by a USER32.DLL API function.

So all together you have three Options:

1. Get the latest SAPBASIS SP and then you can control SaveAs Dialog via SAP SCRIPTING

2. Use sendkey

3. Use USER32 function in a second EXCEL Instance

Depence on your decision we can may post the best Option for you as solution.

Hope this helps,

Holger

[Former Member · Accepted solution]

Hi Holger,

Thank you very much for your explanation. I am not so familiar on script. Could you please post the code for 2rd & 3rd option. I will try both of them and choose the one that suite best for me. Please note as I would need the directory(desktop) to be chosen automatically and the file name to be chosed from according to my choice in excel. All these to be included in the macro? please help me with best possible things that can be done.

Tia,

Mercy

[holger_khn · Accepted solution]

Hello.

Code used by me for Point '3'. I do not have any code for Point '2' right now as this sendkey is not used by me.

Option Explicit
Private Declare Function FindWindow Lib "User32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private 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
Private 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
Declare Function MakePath Lib "imagehlp.dll" Alias "MakeSureDirectoryPathExists" _
    (ByVal lpPath As String) As Long
Const WM_SETTEXT As Long = &HC
Const BM_CLICK = &HF5
Const GW_CHILD = 5
Const GW_HWNDNEXT = 2
Const SW_SHOWNORMAL = 1
Const SW_SHOWMINIMIZED = 2
Const FileSaveAsPath = "C:\tmp\"
Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type
Dim hWnd As Long
Dim Childhwnd As Long
Dim pos As RECT
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
Private Function ActivateWindow(xhWnd As String) 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
Private Function DeActivateWindow(xhWnd As String) 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_SaveFileNamePath(Message As String, hWnd As Long)
    Call SendMessage(hWnd, WM_SETTEXT, False, ByVal Message)
End Sub
Public Sub Auto_SaveAs_SAP()
Dim strFilename As String
On Error GoTo err_handler
   hWnd = FindWindow("#32770", "Save As")
    If hWnd = 0 Then
       MsgBox "'Save As'-dialog not found"
       Exit Sub
    End If
    Childhwnd = FindWindowEx(hWnd, ByVal 0&, "ComboBoxEx32", "")
    If Childhwnd = 0 Then
        MsgBox "ComboBoxEx32 not found"
        Exit Sub
    End If
     Childhwnd = FindWindowEx(Childhwnd, ByVal 0&, "ComboBox", "")
     If Childhwnd = 0 Then
         MsgBox "ComboBox control not found"
         Exit Sub
     End If
     Childhwnd = FindWindowEx(Childhwnd, ByVal 0&, "Edit", "")
     If Childhwnd = 0 Then
         MsgBox "Edit control not found"
         Exit Sub
     End If
     
     strFilename = InputBox("Filename:", "FileName ('C:\tmp\' is default path)", "MyTestFile")
     strFilename = FileSaveAsPath & strFilename
     
     'If Right(FileSaveAsPath, 1) <> "\" Then FileSaveAsPath = FileSaveAsPath & "\"
     
     If MakePath(FileSaveAsPath) = 0 Then
        MsgBox FileSaveAsPath, vbInformation, "Pfad konnte nicht angelegt werden."
     End If
     
     Application.DisplayAlerts = False
        If Dir(strFilename) Then Kill strFilename
     Application.DisplayAlerts = True
     
     ActivateWindow (hWnd)
        DoEvents
            SendMess_SaveFileNamePath strFilename, Childhwnd
            
            Childhwnd = FindWindowEx(hWnd, ByVal 0&, ByVal "Button", ByVal "Open as &read-only")
            Childhwnd = GetWindow(Childhwnd, GW_HWNDNEXT)
            If Childhwnd = 0 Then
                MsgBox "Save Button in 'Save As'-dialog not found"
                Exit Sub
            End If
            
        SendMessage Childhwnd, BM_CLICK, 0, ByVal 0&
    Exit Sub
err_handler:
MsgBox Err.Description
End Sub

For your purpose you Need to develop an customized Sub which will wait in an Loop that SaveAs-dialog appear and fillin required filename.

This code is just handle an existing SaveAs Dialog triggered by SAP.

Best regards,

Holger

[Former Member · Accepted solution]

Hi Holger,

the above code seems to little confusing.. however I tried using the below code. Which waits till the save as dialog appear and immediately it saves in some path. I want it to save as with filename that I have in excel sheet... what code should I use for that? Below is the code that i have used

            timeout = Now + TimeValue("00:00:30")

    Do

        hWnd = FindWindow("#32770", "Save PDF File as")

        DoEvents

        Sleep 200

    Loop Until hWnd Or Now > timeout

        strFilename = Workbooks("COMPASS Reporting Wizard.xlsm").Worksheets("Input").Range("N1").Select

        If hWnd Then

        'Find the child Save button

        hWnd = FindWindowEx(hWnd, 0, "Button", "&Save")

        'Debug.Print "   Save button "; Hex(hWnd)

        End If

    If hWnd Then

        'Click the Save button

        'SetForegroundWindow (hWnd)

        Sleep 600  'this sleep is required and 600 miiliseconds seems to be the minimum that works

        SendMessage hWnd, BM_CLICK, 0, 0

    End If

[holger_khn · Accepted solution]

Hello.

SendMessage hWnd, BM_CLICK, 0, "C:\yourPath\YourFilename.xlsx"

Br,

Holger

[Former Member · Accepted solution]

Hi Holger,

I tried, but it doesn't work 😞

Where do I put that command to?

[holger_khn · Accepted solution]

Hello.

As I do not know if your code is working it´s hard for me to find a solution. I know my code is working. As well with filename.

I guess your Problem is that SAP sessioncode and this SaveAs code is running in same Excel Instance.

As session code is initiate SaveAS Dialog VBA code is not reach that Point. Create an new Excel instance for testing and execute both parallel. May then it will work.

Best regards

Holger

[Former Member · Accepted solution]

Hi Holger,

It works for me, but only the filename doesn't work...

I want it to recognise file name as it recognises save button.

are you getting?

[holger_khn · Accepted solution]

Hello.

I have this Sub:

Sub SendMess_SaveFileNamePath(Message As String, hWnd As Long)  
    Call SendMessage(hWnd, WM_SETTEXT, False, ByVal Message)  
End Sub 

And I call Sub in Line 129. of my above posting:

            SendMess_SaveFileNamePath strFilename, Childhwnd  
              
            Childhwnd = FindWindowEx(hWnd, ByVal 0&, ByVal "Button", ByVal "Open as &read-only")  
            Childhwnd = GetWindow(Childhwnd, GW_HWNDNEXT)  
            If Childhwnd = 0 Then  
                MsgBox "Save Button in 'Save As'-dialog not found"  
                Exit Sub  
            End If  
              
        SendMessage Childhwnd, BM_CLICK, 0, ByVal 0& 

[Former Member · Accepted solution]

I have tried it, the name shows on top of the screen...

[holger_khn · Accepted solution]

My code is a Little bit more complex because you Need to get childhwnd of 'EDIT' control to pass filename in:

Childhwnd = FindWindowEx(Childhwnd, ByVal 0&, "Edit", "") 

To get Childhwnd of 'Edit' control you Need to have hwnd of previous object.

I suggest you review my full code to get the depencies.

Best regards,

Holger

[Former Member]

Hi Holger,

I have used this code which is similar to yours, however it runs perfect with file names when I do F8, but when I just give F5 and run the code, it just clicks on save automatically with the default file name.

Is there anything wrong in the code.

Public Sub pdfsave()

    On Error GoTo Whoa

    Dim timeout As Date

    SP = Worksheets("Input").Range("N1").Value

    Ret = FindWindow(vbNullString, "Save PDF File As")

            timeout = Now + TimeValue("00:00:50")

    Do

        Ret = FindWindow(vbNullString, "Save PDF File As")

'        hwnd = FindWindow("#32770", "File Download")

        DoEvents

        Sleep 200

    Loop Until Ret Or Now > timeout

    SendKeys ("%{tab}")

    If Ret <> 0 Then

        'MsgBox "Main Window Found"

        '~~> Get the handle of the Button's "Window"

        ChildRet = FindWindowEx(Ret, ByVal 0&, "Button", vbNullString)

        If ChildRet = 0 Then

            MsgBox "Child Window Not Found"

            Exit Sub

        End If

        '~~> Get the caption of the child window

        strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0))

        GetWindowText ChildRet, strBuff, Len(strBuff)

        ButCap = strBuff

        '~~> Loop through all child windows

        Do While ChildRet <> 0

            '~~> Check if the caption has the word "Save"

            If InStr(1, ButCap, "Save") Then

                '~~> If this is the button we are looking for then exit

                OpenRet = ChildRet

                Exit Do

            End If

            '~~> Get the handle of the next child window

            ChildRet = FindWindowEx(Ret, ChildRet, "Button", vbNullString)

            '~~> Get the caption of the child window

            strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0))

            GetWindowText ChildRet, strBuff, Len(strBuff)

            ButCap = strBuff

        Loop

        '~~> Check if we found it or not

        If OpenRet = 0 Then

            MsgBox "The Handle of Save Button was not found"

            Exit Sub

        End If

        '~~> Retrieve the dimensions of the bounding rectangle of the

        '~~> specified window. The dimensions are given in screen

        '~~> coordinates that are relative to the upper-left corner of the screen.

        GetWindowRect OpenRet, pos

        '~~> Move the cursor to the specified screen coordinates.

        SetCursorPos (pos.Left - 10), (pos.Top - 10)

        '~~> Suspends the execution of the current thread for a specified interval.

        '~~> This give ample amount time for the API to position the cursor

        Sleep 200

        SetCursorPos pos.Left, pos.Top

        Sleep 200

        SetCursorPos (pos.Left + pos.Right) / 2, (pos.Top + pos.Bottom) / 2

        '~~> Set the size, position, and Z order of "File Download" Window

        SetWindowPos Ret, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOACTIVATE Or SWP_SHOWWINDOW Or SWP_NOMOVE Or SWP_NOSIZE

        Sleep 200

        '~~> Simulate mouse motion and click the button

        '~~> Simulate LEFT CLICK

        mouse_event MOUSEEVENTF_LEFTDOWN, (pos.Left + pos.Right) / 2, (pos.Top + pos.Bottom) / 2, 0, 0

        Sleep 700

        '~~> Simulate Release of LEFT CLICK

        mouse_event MOUSEEVENTF_LEFTUP, (pos.Left + pos.Right) / 2, (pos.Top + pos.Bottom) / 2, 0, 0

        Wait 5

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

        ' START OF SAVEAS ROUTINE '

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

        Ret = FindWindow(vbNullString, "Save PDF File As")

'             timeout = Now + TimeValue("00:00:50")

'    Do

'        Ret = FindWindow(vbNullString, "Save PDF File As")

''        hwnd = FindWindow("#32770", "File Download")

'        DoEvents

'        Sleep 200

'    Loop Until Ret Or Now > timeout

'

        If Ret = 0 Then

            MsgBox "Save As Window Not Found"

            Exit Sub

        End If

        '~~> UNCOMMENT this if using IE6 and COMMENT the code for "DUIViewWndClassName"

        '~~> "DirectUIHWND" and "FloatNotifySink"

        ' '~~> Get the handle of the Main ComboBox

        ' ChildRet = FindWindowEx(Ret, ByVal 0&, "ComboBoxEx32", "")

        '

        ' If ChildRet = 0 Then

        ' MsgBox "ComboBoxEx32 Window Not Found"

        ' Exit Sub

        ' End If

        ChildRet = FindWindowEx(Ret, ByVal 0&, "DUIViewWndClassName", "")

        If ChildRet = 0 Then

            MsgBox "DUIViewWndClassName Not Found"

            Exit Sub

        End If

        ChildRet = FindWindowEx(ChildRet, ByVal 0&, "DirectUIHWND", "")

        If ChildRet = 0 Then

            MsgBox "DirectUIHWND Not Found"

            Exit Sub

        End If

        ChildRet = FindWindowEx(ChildRet, ByVal 0&, "FloatNotifySink", "")

        If ChildRet = 0 Then

            MsgBox "FloatNotifySink 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

        '~~> COMMENT the below 3 lines if you do not want to specify a filename

        Wait 5

        SendMess SP, ChildRet

        Wait 5

        '~~> Get the handle of the Save Button in the Save As Dialog Box

        ChildRet = FindWindowEx(Ret, ByVal 0&, "Button", vbNullString)

        '~~> Check if we found it or not

        If ChildRet = 0 Then

            MsgBox "Save Button in Save As Window Not Found"

            Exit Sub

        End If

        '~~> Get the caption of the child window

        strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0))

        GetWindowText ChildRet, strBuff, Len(strBuff)

        ButCap = strBuff

        '~~> Loop through all child windows

        Do While ChildRet <> 0

            '~~> Check if the caption has the word "Save"

            If InStr(1, ButCap, "Save") Then

                '~~> If this is the button we are looking for then exit

                OpenRet = ChildRet

                Exit Do

            End If

            '~~> Get the handle of the next child window

            ChildRet = FindWindowEx(Ret, ChildRet, "Button", vbNullString)

            '~~> Get the caption of the child window

            strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0))

            GetWindowText ChildRet, strBuff, Len(strBuff)

            ButCap = strBuff

        Loop

        '~~> Check if we found it or not

        If OpenRet = 0 Then

            MsgBox "The Handle of Save Button in Save As Window was not found"

            Exit Sub

        End If

        '~~> Save the file

        SendMessage OpenRet, BM_CLICK, 0, ByVal 0&

        Wait 5

    Else

        MsgBox "File Download Window Not found"

    End If

    Exit Sub

Whoa:

    MsgBox Err.Description

End Sub

[holger_khn]

Is this running in same instance which control SAP session?