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

 

[SkipVought]

hi,

'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
    
    Dim Row As Long, lLastRow As Long, lRow As Long
    With xlSheet
        lLastRow = .[A2].End(xlDown).Row
    End With
    
    lRow = 2
    Do
        For Rows = 1 To 18
            Sess0.Screen.PutString xlSheet.Cells(lRow, "A").Value, Rows, 18    'load up the emulator in column 18, rows 1-18
            lRow = lRow + 1
            If lRow > lLastRow Then Exit For
        Next Rows
        Sess0.Screen.SendKeys ("<Enter>")               'then hit ENTER
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    Loop Until r.Row > lLastRow

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MrHeynow]

THANKS ! Skip

What about the location ie

Sess0.Screen.PutString MyRange.Rows(Rows).Value, 5, 18

Will it know where to place it?

 

[MrHeynow]

Also

Syntax error on

lLastRow = .[A2].End(xlDown).Row

 

[SkipVought]

Where did your see

Sess0.Screen.PutString MyRange.Rows(Rows).Value, [b][red]5[/red][/b], 18

in my code???

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MrHeynow]

in a portion of the original code

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

 

[SkipVought]

If you insist on using your code, rather than the code that I posted, I guess that you'll just have to settle for having all the values from your Excel sheet in 5,18, regardless of the value in Row from your loop.

Hope your're happy with that. I certainly would not.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MrHeynow]

Mr Vought, I am not happy with my code hence my posting and it is not my aim to frustrate or agrivate anyone.

Maybe your code is all I need, my question was how would it know to place the resulting copied rows at 05/18 or row 5 col 18 in the seesion.

Also there is a Syntax error bieng displayed on

lLastRow = .[A2].End(xlDown).Row

I appreciate any and all assistance....

 

[vzachin]

mrHeyNow,

i could not get skip's code to compile correctly due to my lack of knowledge of proper coding but try this code.
i added a Loop and a few If statements. the code will stop if there is a blank null value in the cell (not sure if you need that)

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession

'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:="c:\test.xls"
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,rw as Long
rw=5
do
With xlApp.ActiveSheet
Set MyRange = .Range("A2:A65536").Resize(xlApp.CountA(.Range("A2:A65536")))
End With
For Rows = 2 To 65536
if MyRange.Rows(Rows).Value = "" Then Exit Sub
Sess0.Screen.PutString MyRange.Rows(Rows).Value, rw, 18
if rw = 22 then
rw = 5
else
rw = rw + 1
end if
Sess0.Screen.Sendkeys("<Enter>")    
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Next Rows

loop

a little sloppy but should work for you

zach

 

[CompuDork]

I think the question that we are having is why are you trying to overwrite the data that you
are placing at row 5, column 18? You are putting a string there, with one row of data from
the Excel sheet, and the next time the loop goes thru, you are overwriting with the data in
the next row on the Excel Sheet. I believe you are missing the Range keyword in your lLastRow statement.

 

[MrHeynow]

OK,the string I want to set at 05/18 is the 18 records copied or pulled from the excel spreadsheet. Then I send an <Enter> to attachmate which will process and clear these placed records. Then go back to Excel, get the next 18 records and loop through all the records 18 at a time untill complete. I think Skip has it, but I couldnt see where the data was set at 05/18 or

Sess0.Screen.PutString MyRange.Rows(Rows).Value, 5, 18

Then when just trying to run it. there was a Syntax error bieng displayed on

lLastRow = .[A2].End(xlDown).Row

I appreciate all the reponses

 

[SkipVought]

"but I couldnt see where the data was set at 05/18"

Sess0.Screen.PutString xlSheet.Cells(lRow, "A").Value, [b]Rows, 18[/b]    'load up the emulator in column 18, rows 1-18

from my posted code on 17 Jan 09 20:27

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MrHeynow]

Skip thanks for revisting this. I know you are the man with the answer

Ok I copied and pasted yours of 1/17 I get four compliling errors on marked by ">>"

--------------------------------------------------------------------------------
'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

Dim Row As Long, lLastRow As Long, lRow As Long
With xlSheet
>>lLastRow = .[A2].End(xlDown).Row
End With

lRow = 2
Do
For Rows = 1 To 18
>>Sess0.Screen.PutString xlSheet.Cells(lRow, "A").Value, Rows, 18 'load up the emulator in column 18, rows 1-18
lRow = lRow + 1
If lRow > lLastRow Then Exit For
Next Rows
>>Sess0.Screen.SendKeys ("<Enter>") 'then hit ENTER
>>Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Loop Until r.Row > lLastRow

 

[SkipVought]

Dim Row As Long, lLastRow As Long, lRow As Long

lLastRow = xlSheet.Range("A2").End(xlDown).Row

lRow = 2
Do
  For Rows = 1 To 18
    Sess0.Screen.PutString xlSheet.Cells(lRow, "A").Value, Rows, 18 
    lRow = lRow + 1
    If lRow > lLastRow Then Exit For
  Next Rows
  Sess0.Screen.SendKeys ("<Enter>") 
  Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Loop Until r.Row > lLastRow

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MrHeynow]

Skip similair errors/little bugs when compiled. I know it can be done, I just know it.

 

[SkipVought]

what errors on what statements?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MrHeynow]

Thanks Skip,,

At the @ signs

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

Dim Row As Long, lLastRow As Long, lRow As Long

@lLastRow = xlSheet.Range("A2").End(xlDown).Row

lRow = 2
Do
For Rows = 1 To 18
@Sess0.Screen.PutString xlSheet.Cells(lRow, "A").Value, Rows, 18
lRow = lRow + 1
If lRow > lLastRow Then Exit For
Next Rows
@Sess0.Screen.SendKeys ("<Enter>")
@Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
@Loop Until r.Row > lLastRow
@

 

[MrHeynow]

Any Help in resolving errors would be appreciated

 

[vzachin]

just curious, did you try the code i posted?

 

[MrHeynow]

Yes I did , and it works pasting /posting one record at a time moving down the screen. Which it did work in the sense that it updated all the records with out erroring

Dude this was HUGE!!, that it didnt stop, it did all 20,000 records!

But I should able to paste or post 18 lines of data at a time at the location of row 5 and column 18 or 5/18. This would cut the time by 18.

I really appreciate yours and Skip's efforts.

Really this is quite cool.......it can be even cooler

Rob