on 09-13-2018 3:17 PM
Hi, I'm having a problem.
I wrote a script for downloading documents from BA23, but some of the objects do not have an active attachment field. Because of this, the script stops.
Is it possible to make an algorithm inside the script so that it misses this error and continues to read the Excel?
Below is my code
Thanks for you help.
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
session.findById("wnd[0]").maximize
Dim objExcel, objWorkbook, objSheet, i
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\Z003S0BF\Desktop\My_scripts\VF03_mass_download\Book3.xlsx")
Set objSheet = objWorkbook.Sheets("Sheet1")
For i = 2 to objSheet.UsedRange.Rows.Count
COL1 = Trim(CStr(objSheet.Cells(i, 1).Value)) 'Column1
COL2 = Trim(CStr(objSheet.Cells(i, 2).Value)) 'Column2
COL3 = Trim(CStr(objSheet.Cells(i, 3).Value)) 'Column3
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").text = "VA23"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtVBAK-VBELN").text = COL1
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/titl/shellcont/shell").pressContextButton "%GOS_TOOLBOX"
session.findById("wnd[0]/titl/shellcont/shell").selectContextMenuItem "%GOS_VIEW_ATTA"
session.findById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell").selectedRows = "0"
session.findById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell").pressToolbarButton "%ATTA_EXPORT"
session.findById("wnd[1]/usr/ctxtDY_PATH").text = COL3
session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = COL2
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 5
session.findById("wnd[1]/tbar[0]/btn[11]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
next
msgbox "Process Complete"
Hello Svyatoslav,
in my opinion it is possible. You can read the message in the status bar and dependent from the message you can decide in your code what you want to do. Here an example:
session.findById("wnd[0]/tbar[0]/okcd").text = "/nSE16"
session.findById("wnd[0]/tbar[0]/btn[0]").press
Dim arrTable
arrTable = Array("APPLE", "TADIR", "BANANA")
For i = 0 To UBound(arrTable)
session.findById("wnd[0]/usr/ctxtDATABROWSE-TABLENAME").text = arrTable(i)
session.findById("wnd[0]").sendVKey 0
If InStr(session.findById("wnd[0]/sbar/pane[0]").Text, "nicht aktiv im Dictionary vorhanden") Then
MsgBox "Table " & arrTable(i) & " doesn't exists"
Else
session.findById("wnd[0]/tbar[0]/btn[3]").press
End If
Next
I open TAC SE16 and define an array with different table names, but some of them are fictive. I loop over the array and set the table names into the field and dependent from the text in the status bar (wnd[0]/sbar/pane[0]) I decide what to do. If the table exists I do some activities and otherwise a message box pops up.
Let us know your results.
Cheers
Stefan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello. Thanks for the help, everything works fine. There is only one small problem that does not affect the functionality. When the script finishes working after reading the last line in the Excel, instead of the message that the process is ready the script stops with an error.
Below is my code
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
session.findById("wnd[0]").maximize
Dim objExcel, objWorkbook, objSheet, i
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\Z003S0BF\Desktop\My_scripts\VF03_mass_download\Book3.xlsx")
Set objSheet = objWorkbook.Sheets("Sheet1")
For i = 2 to objSheet.UsedRange.Rows.Count
COL1 = Trim(CStr(objSheet.Cells(i, 1).Value)) 'Column1
COL2 = Trim(CStr(objSheet.Cells(i, 2).Value)) 'Column2
COL3 = Trim(CStr(objSheet.Cells(i, 3).Value)) 'Column3
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").text = "VA23"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtVBAK-VBELN").text = COL1
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/titl/shellcont/shell").pressContextButton "%GOS_TOOLBOX"
session.findById("wnd[0]/titl/shellcont/shell").selectContextMenuItem "%GOS_VIEW_ATTA"
If InStr(session.findById("wnd[0]/sbar/pane[0]").Text, "The service <'Attachment list'> is not available") Then
session.findById("wnd[0]/tbar[0]/btn[15]").press
Else
session.findById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell").selectedRows = "0"
session.findById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell").pressToolbarButton "%ATTA_EXPORT"
session.findById("wnd[1]/usr/ctxtDY_PATH").text = COL3
session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = COL2
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 5
session.findById("wnd[1]/tbar[0]/btn[11]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
End If
next
msgbox "Process Complete"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Svyatoslav,
thanks for your answer and your code.
To your question: Could it be possible that UsedRange.Rows.Count delivers a higher rows number as filled with data? Please try it out and use as alternative ActiveSheet.Range("A65536").End(xlUp).Row.
Let us know your results.
Cheers
Stefan
additionally a small check can be made
zBool = session.FindById("wnd[0]/mbar/menu[0]/menu[2]").Changeable
if zBool is false, dont try clicking on it, the script will terminate, as that isnt a valid action for that menu item
obviously replace "wnd[0]/mbar/menu[0]/menu[2]" with whatever menu item you are testing for availability. The Array starts at zero & counts up.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.