RE: SAP scripts VBS loop - Help required

[Keerthi@21]

Dears,

I'm currently trying to write a script that will run a transaction in SAP named ST03, and download the output to a specific location based on Profile name. Currently, my vba script execute each and every line item wherever required status column = "Yes" - i placed one outer for loop in main sub module (refer screenshot below).

28695

For each run (line item) it will trigger the SAP recorded scripts from beginning based on specified parameter -date, time, task type with required status = "Yes" (refer screenshot) and download files (excel screenshot).

28694

I need help to modify my existing script with another logic -> Example: If profile column ="Transaction" then execute the line items with specified input parameters -date, time and task type only if required status ="Yes". After completion, it should move (come) to User profile row and start triggering the sap recorded steps. I tried with if condition, but my outer(main) loop starts only with transaction row as next loop - please refer below code which i tried:

28696


Kindly help out! Thanks in advance.

[Paul_Hossler]

Screen shots are darn near impossible to read

Use the [#] icon to insert CODE tags an paste the code between then

Use the [Go Advanced] button and then the paperclip icon to attach a workbook with what is your Excel screen shot

[Keerthi@21]

Hi Paul,

As you mentioned , i Have attached the data along with my code for your reference.

Kindly refer it.

Please refer. Thanks for your help in advance!

[SamT]

I hate people who do not use Option Explicit. I worked my way down to Function SAP_Download_Function, and saw all those additional undeclared variables and said, "Nope, No more."

I did find one typo, ("Sesion"",) in a variable... After Setting Option Explicit and Trying to compile the code.
The only Declared variable were all Module level, but many were only used in "Sub Main Module"

These ares the changes I actually made in your code, just to give you ideas

Option Explicit '<----

Public sKernel As String
Public sTaskType As String, sDate, sFromTime, sTCode, sToTime, sOutputFilePath, sOutputFileName As String, sProfile As String
Dim sRequiredstatus As String '<----
Dim iStartRow As Long '<-----
Dim iEndRow As Long '<----
Dim i As Long '<----

Public Enum Sheet1Columns '<----
colProfile = 4
colSAPClient
colTasktype
colTasktype_technicalname
colTcode
colDate
colTime_From
colTime_To
colEmptyColumn1 '<---- to keep Enum correctly numbered
colOutputFilePath
colOutputFileName
colRequiredStatus
End Enum



Public Sub MainModule()

Application.DisplayAlerts = False
iStartRow = 8
With ThisWorkbook.Worksheets("Sheet1")

iEndRow = .Cells(Rows.Count, "E").End(xlUp).Row
For i = iStartRow To iEndRow
With .Rows(i) '<----
'RequiredStatus - User input column
On Error Resume Next
sRequiredstatus = .Cells(colRequiredStatus) 'Note: Uses Enumerated Constants. Shows actual names of values
If sRequiredstatus = "Yes" Then
sKernel = .Cells(colSAPClient)
sTaskType = .Cells(colTasktype_technicalname)
sTCode = "/nST03"
sDate = .Cells(colDate)
sFromTime = .Cells(colTime_From)
sToTime = .Cells(colTime_To)
sProfile = .Cells(colProfile)
sOutputFilePath = .Cells(colOutputFilePath)
sOutputFileName = .Cells(colOutputFileName)

maintest
End If
Err = 0 '<----
End With
Next i
End With
Application.DisplayAlerts = True '<----
End Sub

Enumerated Constants are pretty easy to create. Copy Headers, PasteSpecial Values + Transpose. Insert Prefix and copy down. Do a little Editing to make compliant. Concatenate prefixes and Names, Copy and paste into VBA

[Paul_Hossler]

Another subtilty

In ...



Public sTaskType As String, sDate, sFromTime, sTCode, sToTime, sOutputFilePath, sOutputFileName As String, sProfile As String



... sDate, sFromTime, sTCode, sToTime, sOutputFilePath are all defaulted to Type Variant since they are not explicitly Typed ( ... As String)

Some people prefer to just use Variants for everything and let the system sort it out, but my preference is to Type the heck out of everything