18 Recs from Excel, to Attachmate enter, back to Excel next 18 etc 2

[MrHeynow]

I have varying number of records all in one column, starting at A2. There maybe 1,000 or 20,000 records. What I am trying to do is grab 18 rows from a spreadsheet, switch over to Extra! /Attachmate paste the 18 rows then enter them, then go back to Excel grab the next 18 rows switch back over to Extra! /Attachmate, paste and enter these 18 rows and repeat these same steps until all the records are updated.

Below is code from a previous thread which I modified “For Rows = 1 To 18, which does one row at a time and stop at the 18th record. So that doesn’t work.

Can anyone help here?

'Declare the Excel Object
Dim xlApp As Object, xlSheet As Object, MyRange As Object
Set xlApp = CreateObject("excel.application")
xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
xlApp.Visible = True
xlApp.Workbooks.Open FileName:="D:\Shared Team\IVR_Bulk_Update.xls"
Set xlSheet = xlApp.activesheet
Set MyRange = xlApp.activesheet.Range("A:A")

Dim Row As Long
With xlApp.ActiveSheet
Set MyRange = .Range("A2:A65536").Resize(xlApp.CountA(.Range("A2:A65536")))
End With
For Rows = 1 To 18
Sess0.Screen.PutString MyRange.Rows(Rows).Value, 5, 18
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Next Rows


End Sub

 

[MrHeynow]

Anyone have any suggestion on my last question

 

[vzachin]

mrheynow.

not sure if this is what you mean

Sub Main

    Dim Sessions As Object
    Dim System As Object
    Set System = CreateObject("EXTRA.System")  
    Set Sessions = System.Sessions
    Dim Sess0 As Object
    Set Sess0 = System.ActiveSession
'----------------------------------------
    SFile = "c:\test18rows.xls"
    Dim obj as object
    Dim objWorkbook as object
    Set obj=CreateObject("Excel.Application")
    obj.visible=TRUE
    obj.Workbooks.Open sFile
    set objWorkbook=obj.Worksheets("sheet1")
'----------------------------------------
    obj.Worksheets("sheet1").Select

    For i = 2 To obj.activesheet.Rows.Count Step 18   'step 18 increments 18 rows

    dat = obj.Range("a" & i)

    if dat = "" then
    msgbox "nomore"
    exit sub
    else
    obj.range("a" & i,"a" & i +17).copy         'this will copy 18 rows 
    sess0.screen.moveto 5,18
    sess0.screen.paste
    end if

    next i

End Sub

i don't know how much faster this would be. time it and let me know, please

zach

 

[MrHeynow]

Zach
Sorry it didnt enter the records at all and when I added the enter step, it goes right to the last set of records, then displays Nomore. So if there was 300 records it pastes the last 18 records only. What are we missing ? Thanks again for your efforts. _Rob