Showing results for 
Search instead for 
Did you mean: 

Support on VB script for report


I have recorded and created a VB script to pull a daily report in VIM analytics, there are around 6 different reports which i need to pull on a daily basis.

Once the script run's it will take me till export to excel and give me the path to save, i need to give the name of the file and save, then it will run automatically for next report again till export to excel. and so on...

Can i get some loop coding, where the script should save all the excel files automatically in a specific folder, instead of asking file names every time.

So, once i run the script it should end after saving all the file.

Attach is the script details

Thanks in advance


Accepted Solutions (1)

Accepted Solutions (1)



When you use Excel VBA as source of your scripting you can use below code in an module. If you use a new folder on your local PC it can happen that you need to allow access for this folder first time.

Of course you need to call Sub 'Auto_SaveAs_SAP' in each case where your script cause 'Save As' dialog. And of course you need to change the filename handling to your requirements. Seems like this need to be moreflexible to name each report correct. So filename should not set as constant in your code.

'--------------------------------------------Start VBA Module-------------------------------------------

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

Const WM_SETTEXT As Long = &HC
Const BM_CLICK = &HF5
Const GW_CHILD = 5

    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


Public Type POINTAPI
    X As Long
    Y As Long
End Type

    Length As Long
    flags As Long
    showCmd As Long
    ptMinPosition As POINTAPI
    ptMaxPosition As POINTAPI
    rcNormalPosition As RECT
End Type

'==> Use this if you want to specify your own name in the 'Save As'-Dialog
Const FileSaveAsName = "C:\tmp\Done\MyFile6.xls"

Private 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)
                Call SetForegroundWindow(xhWnd)
                Result = BringWindowToTop(xhWnd)
            End If
            If Result Then ActivateWindow = True
        End If
    End With
  End Function

Private 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

Private 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
    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
     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
    MsgBox Err.Description
End Sub

'--------------------------------------------End VBA Module---------------------------------------------

The good point is, that we do not use sendkey-functionality. Which is sometimes hard to get right timing. This script will use API-functions and therefore we have no problems with any other activities during script runtime.

Of course you should have a little bit more knowledge about VBA to get it running.

Br, Holger

0 Kudos

Hello, Holger Kohn!

That's exactly what I'm needing, however my script is all in AutoIt, it would be possible you help me convert it?

Or someone in the community who could help me?

Thank you


Former Member
0 Kudos

Hi Holger Kohn,

I'm trying to export reports from SAP through VBA and facing the same issue.
I have tried to use your solution by pasting your above code in my 2nd module and I'm calling it from my main module after the save as window pop-up. Is this correct ? as my code on main module stop executing at SaveAs window.

Can you help me in this?

My code is as below -

session.findById("wnd[0]/usr/cntlCC_ALV/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
session.findById("wnd[0]/usr/cntlCC_ALV/shellcont/shell").selectContextMenuItem "&XXL"

'Declaring the file path

FilePath1 = FilePath & wks.Range("B" & i).Value & ".xlsx"

'Here the SaveAs window pop-up and the VBA code stop executing untill I manually click on save/cancel button


Call Auto_SaveAs_SAP(FilePath1)

0 Kudos

Hi Holger Kohn,

I had been using this solution successfully for years. A few months back, our company upgraded to SAP 7.70 and now this script throws errors as it "can't find ComboboxEx32". I've been searching for months to find the answer on my own but haven't found a solution. I am now, however, transitioning positions and I do not want the person who inherits this report to inherit an error. Any help you can provide on how to correctly identify the child window for the file name would be greatly appreciated!

0 Kudos

Thanks for coming to SAP Community for answers. Please post your question as a new question here:

Since you're new in asking questions here, check out our tutorial about asking and answering questions (if you haven't already), as it provides tips for preparing questions more effectively, that draw responses from our members.

Please note, that your post here won't be answered.

Answers (3)

Answers (3)

0 Kudos

Thanks both of you for all the support,

I have given the save as dialogue box along with my recorded VBS script, it is working as of now.



Active Participant
0 Kudos

Hi baig,

only thing you can do is "autotype" the Path and filename into the "Save-File" Dialog---

Here is the way to do 😉  thanks ScriptMan

look here:

Former Member
0 Kudos

This works fine for SM50, as the script is pure VB you can put loops or call routines, etc.

See the microsoft scripting reference for more information.

If Not IsObject(application) Then

   Set SapGuiAuto  = GetObject("SAPGUI")

   Set application = SapGuiAuto.GetScriptingEngine

End If

If Not IsObject(connection) Then

   Set connection = application.Children(0)

End If

If Not IsObject(session) Then

   Set session    = connection.Children(0)

End If

If IsObject(WScript) Then

   WScript.ConnectObject session,     "on"

   WScript.ConnectObject application, "on"

End If

sub export_SM50_Workprocesses(path,filename)


session.findById("wnd[0]/tbar[0]/okcd").text = "/nsm50"

session.findById("wnd[0]").sendVKey 0





session.findById("wnd[1]/usr/ctxtDY_FILENAME").text =filename


session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 7

session.findById("wnd[1]/usr/ctxtDY_PATH").text = path



end sub

call export_SM50_Workprocesses("C:\whatever_folder\","filename0001.xls")

call export_SM50_Workprocesses("C:\whatever_folder\","filename0002.xls")

call export_SM50_Workprocesses("C:\whatever_folder\","filename0003.xls")

' or ....

for i = 0 to 5

call export_SM50_Workprocesses("C:\whatever_folder\","filename000"& i &".xls")