on 05-20-2013 9:17 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
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!
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.