Skip to Content

Support on VB script for report

Hi,

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

Baig

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on May 22, 2013 at 11:13 AM

    Hello.

    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
    Const GW_HWNDNEXT = 2

    Type RECT
    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

    '==> 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)
    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 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
    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

    '--------------------------------------------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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

      session.findById("wnd[0]/tbar[1]/btn[8]").press


      Call Auto_SaveAs_SAP(FilePath1)

  • author's profile photo Former Member
    Former Member
    Posted on May 20, 2013 at 12:28 PM

    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]").maximize

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

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

    session.findById("wnd[0]/tbar[1]/btn[9]").press

    session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").select

    session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").setFocus

    session.findById("wnd[1]/tbar[0]/btn[0]").press

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

    session.findById("wnd[1]/usr/ctxtDY_PATH").setFocus

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

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

    session.findById("wnd[1]/tbar[0]/btn[0]").press

    session.findById("wnd[0]/tbar[0]/btn[3]").press

    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")

    next

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 21, 2013 at 10:15 AM

    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:http://scn.sap.com/message/8219341#8219341

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 09, 2013 at 07:33 AM

    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.

    Regard

    Baig

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.