Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

limited and every time diffirent number of rows transfered to SAP from Excel file

Hi Gurus,

Please help!

this script works , but processes just the limited part of my excel rows, for example I have 200 rows in Excel, but macro processes sometimes 100, sometimes 140, sometimes 138 rows and stops. Is there something with container or shell  size (? not even sure what it is)

Is there any place where I could extend max rows to be process?

Script.txt.zip (840 B)
Tags:
Former Member
replied

Hi Elena,

As Holger has proposed, I would now integrate the VBS commands directly in VBA.

for example:

Sub ClickGo()

SAP_CheckSessions (0)

Call Initialise

End Sub

'step2 Check SAP sessions

Function SAP_CheckSessions(RequiredSessions As Integer)

    On Error Resume Next

    Dim sap As Object

    Dim sapGui As Object

    Dim sapCon As Object

    Dim sapSession As Object

    Dim i As Integer

    Dim j As Integer

    Set sap = GetObject("sapgui")

    Set sapGui = sap.GetScriptingEngine

    For i = 0 To sapGui.Connections.Count - 1

        Set sapCon = sapGui.Children(CLng(i))

    Next i

    If sapGui.Connections.Count < 1 Then

        MsgBox ("Open & Login To SAP To Continue! (No Transactions Need To Be Open)")

        End

    End If

    Set sapSession = sapCon.Children(0)

    While (sapCon.Sessions.Count < RequiredSessions)

        sapSession.findbyid("wnd[0]/tbar[0]/okcd").Text = "/osession_manager"

        sapSession.findbyid("wnd[0]/tbar[0]/btn[0]").press

    Wend

    Set sapSession = Nothing

    Set sapCon = Nothing

    Set sapGui = Nothing

    Set sap = Nothing

    On Error GoTo 0

End Function

Sub Initialise()

''''STEP 3-- create C:/Elena folder and AddVendorEmail.vbs file; If file already exist -kill and recreate again

    'On Error Resume Next

    'If Len(Dir("C:/Elena", vbDirectory)) <> 0 Then

    '    MkDir "C:/Elena"

    'End If

    'Dim fso

     ''Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    'Set fso = CreateObject("Scripting.FileSystemObject")

     'If Len(Dir("C:/Elena", vbDirectory)) <> 0 Then

      '  fso.deletefolder "C:/Elena", True

     'End If

     'If Len(Dir("C:/Elena", vbDirectory)) = 0 Then

     '   MkDir "C:/Elena"

     'End If

     'On Error Resume Next

     ActiveWorkbook.Sheets("Input").Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

     ActiveWorkbook.Sheets("Input").Columns(3).Resize(, 23).Select

     On Error GoTo 0

     '    Columns(2).Resize(, numColumnsToHide).Select

    Selection.EntireColumn.Hidden = True

     ActiveWorkbook.SaveAs Filename:="C:\Elena\Add_Email_Tool.xlsm", FileFormat _

        :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

       '''Sleep 1000

  '''Application.Wait DateAdd("m", 10, Now)

    '=================================================

'''now I'm writing our vbs file to C:/Elena folder

  'Call vbs1

   Set SapGuiAuto  = GetObject("SAPGUI")

   Set SAPapplication = SapGuiAuto.GetScriptingEngine

   Set connection = SAPapplication.Children(0)

   Set session    = connection.Children(0)

  

For i = 3 To ActiveCell.SpecialCells(11).Row

For j = 1 To ActiveCell.SpecialCells(11).Column

If j = 1 Then VENDOR = Cells(i, j).Value

If j = 2 Then Email = Cells(i, j).Value

Next j

Session.findById("wnd[0]").maximize

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

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

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

session.findById("wnd[0]/usr/txtZUT_EMAIL-ZGROUP").text = VENDOR

session.findById("wnd[0]/usr/txtZUT_EMAIL-ZEMAIL").text = Email

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

'On Error Resume Next

Next i

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

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

Session.findById("wnd[1]").Close

Session.findById("wnd[0]/shellcont").Close

Mytext1="Done! All "

Mytext2=CStr(ActiveCell.SpecialCells(11).Row - 2)

Mytext3= " Excel rows from your Input were processed, but only UNIQUE Records were added to Zut_Email table"

Mytext= Mytext1 & Mytext2 & mytext3

MsgBox Mytext

''''''''''''''''''''''

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

'session.findById("wnd[1]/usr/btnSPOP-OPTION1").press

''''''''''''''''''''''''''''''''''''''''''''''''

Session.findById("wnd[1]").Close

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

session.findById("wnd[1]/usr/btnSPOP-OPTION1").press

'Set xclwbk = Nothing

'Set xclsht = Nothing

'xclapp.Quit

'Set xclapp = Nothing

   On Error GoTo 0

   'Call Run_Myscript

End Sub

'STEP 4

Sub Run_Myscript()

    Set objShell = CreateObject("WScript.Shell")

    result = objShell.Run("wscript C:/Elena/AddVendorEmail.vbs """)

  

End Sub

Regards,

ScriptMan

1 View this answer in context
Not what you were looking for? View more on this topic or Ask a question