cancel
Showing results for 
Search instead for 
Did you mean: 

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

Accepted Solutions (1)

Accepted Solutions (1)

holger_khn
Contributor

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

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

Márcio.

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

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


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!

former_member763929
Participant
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.

Regard

Baig

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

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