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

[Former Member]

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

[Former Member · Accepted solution]

Thank you in advance!!!!

Cheers,

Elena

[script_man · Accepted solution]

Hi Elena,

I guess, the issue will be obtained by storing the data. In such cases, always helped i did a little break.

for example:

. . .

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

wscript.sleep 1000           'wait for 1 second

'on error resume next

'I would disable this command to see what error actually incur.

next

. . .

Regards,

ScriptMan

[Former Member · Accepted solution]

Hi ScriptMan,

Thank you for looking to this.

I added

wscript.sleep 1000

but SAP  still  processes just small fraction of my Input, now it is even less than before.

Also macro does not close Excel file with input data right away, it takes long time for Excel to close, please see a message below

Please-please help me!!!

Thanks again ScriptMan!

Elena

,

just adding 2 attachments. Thanks!

write_vbs.txt.zip

Module1.txt.zip

[script_man · Accepted solution]

Hi Elena,

I still have no solution but questions:

- Why must be always recreated the AddVendorEmail.vbs file? The could be as a constant file.

- When the script stops, appears an error message ?

- One could start AddVendorEmail.vbs from the Windows browser manually. Does the script also incomplete?

If the command (wscript.sleep 1000) did not bring anything to the proposed site, you should remove it again.

Regards,

ScriptMan

[Former Member · Accepted solution]

Dear Script Man,

--I recreate vbs file each time, because I want user be able to start from email attachment (without saving) and do not create folder and files each time.

---no, there is no any message if not all records from excel input file were processes. Just after 5-10 minutes, I got the message from Excel that file "is now available for editing..."

but if I make input for  example 50 records , all 50 records always processes correctly without stopping, then there is a message "All done..."  and there is no Excel message

--yes, if I start script manually, have the same problem , if there are more than sometimes 66, sometimes 80, sometimes 115 rows in excel file.

Thank you Script Man!

[script_man · Accepted solution]

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

[Former Member · Accepted solution]

Dear Script Man,

THANK YOU!

Your code is working. I am really appreciate your help!

Could I ask for more , if it is ok. How to add to this code data validation:

if vendor is not blank-- check if email is in valid format

thanks again and again,

Elena

Added code I don't know why I don't see the option to add file first time

script1.txt.zip

[holger_khn · Accepted solution]

Hi Elena.

How you define valid email Format?

[script_man · Accepted solution]

Hi Elena,

You could, for example, check the following:

pos_1 = instr("@" , Email)

pos_2 = instr("." , Email)

if vendor <> 0 and (pos_1 = 0 or pos_2 = 0) then   msgbox "There is email missing for vendor " & vendor

And another note: You could do without a complete second loop.

. . .

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

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

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

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

'Next

VENDOR = cells(i,1).value

Email = cells(i,2).value

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

. . .

Regards,

ScriptMan

[Former Member · Accepted solution]

Hi Holger,

xxxxxxxxxxxx.(dot)xxxxxxxxxxx@xxxxxxxxxxx.(dot)xxx

or

xxxxxxxxxxx@xxxxxxxxx.(dot)xxx

thank you!

Elena

[Former Member · Accepted solution]

Dear ScriptMan,

Thank you very much!!! You are real SCRIPTMAN!

sorry for late reply , was away

Cheers,

Elena

[holger_khn]

Hello Elena.

As you want update SAP from Excel file I would suggest you include this coding as VBA directly in Excel. In this case timing errors will not happen as OLE-Automation will wait until each code line is executed.

As well other issus can be captured and checked with debug function.

[Former Member]

Hi Holger,

Thanks for looking to this.

may be I did something wrong, but actually this coding already included in Excel, please see 2 attachments

may be you have any simple example to share? please!

Thanks again,

Elena

,

write_vbs.txt.zip

Module1.txt.zip

[holger_khn]

Hello Elena.

I would suggest not to create an vbs-file via Excel VBA. Just execute your code in Excel VBA and Loop from their over your sheet. I do not see any valueable reasons the way your code is working.

[Former Member]

THANK YOU HOLGER!

You were right from the beginning, but I just didn't know how to follow your suggestion.

*sorry, there is no way to mark 2 answers as "Correct"

Thanks!

Elena