How do I select different Excel cells using a variable column loop?

[jeaneshe]

I keep typing this and then it disappears after a minute. So I better go fast.
Original code works on one account.
Requests it to loop through many accounts.
The column is the variable counter.

I cannot, with this code, copy and paste back and forth between Reflection and Excel.


Thank you for any assistance! BtW we are running it from within Excel macros.

 

[SkipVought]

Hi,

Please COPY your code from the .ebm file and PASTE here.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jeaneshe]

Thank you so much!

Sorry I thought it needed to be attached. It is clear to me it was originally created in the macro editor then put in Excel. I will copy for you here but let me know if it is not what you wanted. along with the For Next I added,Set curcell = Worksheets("Payment").Cells(15, Counter)and curcell.Copy



Sub Payment()
Dim Sessions, System As Object, Sess0 As Object


Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession

'Set the default wait timeout value
g_HostSettleTime = 500 ' milliseconds

OldSystemTimeout& = System.TimeoutValue
If (g_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = g_HostSettleTime
End If


Dim Counter As Long

'---------------------------------------------------------------------------

'Navigate to ###

'Sess0.Screen.MoveTo 22, 2
' Sess0.Screen.SendKeys ("<Tab>")
' System.TimeoutValue = g_HostSettleTime
' Sess0.Screen.SendKeys ("####<Pf12>")
' System.TimeoutValue = g_HostSettleTime

'---------------------------------------------------------------------------

'Enter Batch Information
For Counter = 2 To 16000 Step 1 'by column

'Payment Amount
Set curcell = Worksheets("Payment").Cells(3, Counter)
curcell.Copy
Sess0.Screen.MoveTo 3, 23
Sess0.Screen.WaitForCursor 3, 23
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'In-Date
Set curcell = Worksheets("Payment").Cells(3, Counter)
Selection.Copy
Sess0.Screen.MoveTo 3, 28
Sess0.Screen.WaitForCursor 3, 28
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'Payment Type
Set curcell = Worksheets("Payment").Cells(4, Counter)
Selection.Copy
Sess0.Screen.MoveTo 5, 20
Sess0.Screen.WaitForCursor 5, 20
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'Data Entry Code
Set curcell = Worksheets("Payment").Cells(5, Counter)
'Range("B5").Select
Selection.Copy
Sess0.Screen.MoveTo 6, 20
Sess0.Screen.WaitForCursor 6, 20
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

Sess0.Screen.SendKeys ("<Enter>")

'---------------------------------------------------------------------------

'Enter Payment Information

'ID TYPE
Set curcell = Worksheets("Payment").Cells(6, Counter)
'Range("B6").Select
Selection.Copy
Sess0.Screen.MoveTo 7, 20
Sess0.Screen.WaitForCursor 7, 20
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'ENTITY ID
Set curcell = Worksheets("Payment").Cells(7, Counter)
'Range("B7").Select
Selection.Copy
Sess0.Screen.MoveTo 7, 25
Sess0.Screen.WaitForCursor 7, 25
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'ACCOUNT TYPE
Set curcell = Worksheets("Payment").Cells(8, Counter)
'Range("B8").Select
Selection.Copy
Sess0.Screen.MoveTo 9, 20
Sess0.Screen.WaitForCursor 9, 20
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'SOURCE OF INFO
Set curcell = Worksheets("Payment").Cells(9, Counter)
'Range("B9").Select
Selection.Copy
Sess0.Screen.MoveTo 11, 20
Sess0.Screen.WaitForCursor 11, 20
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

Sess0.Screen.SendKeys ("<Pf20>")

'PERIOD BEGIN
Set curcell = Worksheets("Payment").Cells(10, Counter)
'Range("B10").Select
Selection.Copy
Sess0.Screen.MoveTo 10, 20
Sess0.Screen.WaitForCursor 10, 20
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'PERIOD END
Set curcell = Worksheets("Payment").Cells(11, Counter)
'Range("B11").Select
Selection.Copy
Sess0.Screen.MoveTo 10, 57
Sess0.Screen.WaitForCursor 10, 57
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'AMOUNT
Set curcell = Worksheets("Payment").Cells(12, Counter)
'Range("B12").Select
Selection.Copy
Sess0.Screen.MoveTo 13, 57
Sess0.Screen.WaitForCursor 13, 57
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'ENTER TRANSACTION
Sess0.Screen.SendKeys ("<Enter>")
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.SendKeys ("<Pf22>")
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'COPY NOTICE NUMBER
' Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
' Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
NoticeNumber = Sess0.Screen.Getstring(8, 19, 13)

Set curcell = Worksheets("Payment").Cells(13, Counter)
curcell = NoticeNumber
'ActiveSheet.Range("B13") = NoticeNumber
If IsNumeric(curcell) Then
'nothing
Else
MsgBox ("There was an issue copying the notice number. Process ended.")
Exit Sub
End If

'------------------------------------------------------------------------------------------------
'DEPOSIT PAYMENT

'Navigate to ####
Sess0.Screen.MoveTo 24, 18
Sess0.Screen.WaitForCursor 24, 18
Sess0.Screen.SendKeys ("####<Pf12>")
System.TimeoutValue = g_HostSettleTime

'DEPOSIT ACTION CODE
Set curcell = Worksheets("Payment").Cells(14, Counter)
'Range("B14").Select
Selection.Copy
Sess0.Screen.MoveTo 3, 24
Sess0.Screen.WaitForCursor 3, 24
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'BATCH ID
Set curcell = Worksheets("Payment").Cells(15, Counter)
Range("B15").Select
Selection.Copy
Sess0.Screen.MoveTo 4, 24
Sess0.Screen.WaitForCursor 4, 24
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'FORM TYPE
Set curcell = Worksheets("Payment").Cells(17, Counter)
'Range("B17").Select
Selection.Copy
Sess0.Screen.MoveTo 6, 24
Sess0.Screen.WaitForCursor 6, 24
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'FORM YEAR
Set curcell = Worksheets("Payment").Cells(18, Counter)
'Range("B18").Select
Selection.Copy
Sess0.Screen.MoveTo 7, 24
Sess0.Screen.WaitForCursor 7, 24
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'TRANS TYPE
Set curcell = Worksheets("Payment").Cells(19, Counter)
'Range("B19").Select
Selection.Copy
Sess0.Screen.MoveTo 8, 24
Sess0.Screen.WaitForCursor 8, 24
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'NUMBER OF ITEMS
Set curcell = Worksheets("Payment").Cells(20, Counter)
'Range("B20").Select
Selection.Copy
Sess0.Screen.MoveTo 9, 24
Sess0.Screen.WaitForCursor 9, 24
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'DEPOSIT AMOUNT
Set curcell = Worksheets("Payment").Cells(21, Counter)
'Range("B21").Select
Selection.Copy
Sess0.Screen.MoveTo 10, 24
Sess0.Screen.WaitForCursor 10, 24
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.Paste

Sess0.Screen.SendKeys ("<Enter>")
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'IN-DATE
Set curcell = Worksheets("Payment").Cells(22, Counter)
'Range("B22").Select
Selection.Copy
Sess0.Screen.MoveTo 7, 5
Sess0.Screen.WaitForCursor 7, 5
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'NOTICE
Range("B13").Select
Selection.Copy
Sess0.Screen.MoveTo 7, 28
Sess0.Screen.WaitForCursor 7, 28
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

'REMITTANCE AMT
Set curcell = Worksheets("Payment").Cells(23, Counter)
Range("B23").Select
Selection.Copy
Sess0.Screen.MoveTo 7, 46
Sess0.Screen.WaitForCursor 7, 46
Sess0.Screen.SendKeys ("<EraseEOF>")
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

Sess0.Screen.SendKeys ("<Pf3><Pf3>")
If Abs(curcell.Value) < 0.01 Then curcell.Value = 0
Next Counter
MsgBox "Macro ran Counter accounts"

End Sub

 

[SkipVought]

If your code is in Excel, generally speaking you can do something like this...

''NOTICE
Sess0.Screen.PutString Range("B13").Value, 7, 28
...

However, you have literals for the Excel source range and the screen coordinates. If you're coding a loop, these references would naturally be variables.

[tt]For Counter = 2 To 16000 Step 1 'by column[/tt]
Do you mean to say that you have 16,000 columns of data?

Perhaps an upload of your workbook would be appropriate.

Note that simply erasing text from your screen or putting text on the screen requires absolutely no delay since this part of the process is not asynchronous. HOWEVER, when you SEND a processing command to the mainframe to take the screen contents and crunch it within the remote mainframe computer, this process is asynchronous, which requires your program to WAIT until the mainframe responds that it is ready for your program to proceed. That WAIT TIME is unknown!!! Your program must LOOP, waiting for the mainframe to respond.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jeaneshe]

Yes. I agree with re the wait times. I have code for that. I'm coming in after that was completed.

Not really 16000 columns. I'm sure I'll reduce that.

How can this work?

'Counter will represent the column
Dim counter as Integer
counter = 2

For counter 2 to 300 step 1
Sess0.Screen.PutString Range("BCounter").Value, 7, 28
do things
Next counter

Can you help with the cell to include the variable? Thank you

 

[jeaneshe]

Also, I'm fully aware of the wait host issues. I'll put a sub to ensure the macro goes where it is supposed to.

 

[SkipVought]

Again I must answer in general, not knowing the structure of fields/data on the screen and how that maps to the data structure in your Excel sheet.

Back in the day, I scraped data from my terminal emulator based on lists in Excel, putting the scraped data into other sheets.

So I built a table that described the data on each screen. That table has the following columns:
[tt]
Screen Name
Field Name
Screen Row
Screen Col
End Row (to accommodate list fields)
Field Length
Field Type (Num or Char)
[/tt]

There's still sooo much we don't know about your screen and sheet. If we assume that a row on the sheet corresponds with a screen's worth of data, we still don't know where to put each Excel column/screen field data on the screen. You need a map, similar to the one I described. OR that data must be embedded in your code(YUK!!!) in a complex If...Else...End If or Select Case...End Select, but embedded data in code is a really poor coding practice.

So how many fields? 300 WOW! That's a lot of fields on one screen!

What we have here is a failure to communicate!

Dim counter as Integer, lRow As Long, rw As Integer, cl As Integer

lRow = 2

For counter 2 to Range(Cells(1, 2), Cells(1, 2).End(xlToRight)).Column
   'you need something to assign (rw, cl) for screen coordinates
   Sess0.Screen.PutString Cells(lRow, counter).Value, rw, cl
   'do things
Next counter
'Next Excel row for next screen of data...
lRow = lRow + 1
'...and an outer loop to manage the screen

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jeaneshe]

I think I'm going to write in Extra Basic since I know how to make it work from there. I really need to learn VBA though. The simplest things throw me. Thank you for help.

 

[SkipVought]

In the 15 years I did screen scraping for my users, I never coded in Attachmate VB: NEVER! By choice! I'd rather drive a Cadillac than a Yugo!

Their editor is a joke. It's like driving blindfolded! You will not only need to instantiate the Attachmate application object but you will also need to do the same for the Excel application object. You loose features like the Watch Window, Immediate Window, object Browser.

I consider that it will place you into a disadvantaged handicapped position for developing your applications.

The code you originally posted is way bloated and overly complex. I'm guessing the Excel screen is poorly designed, perusing your code, but that's a guess when I see B6 to B23 and screen rows,columns all over the place and just fail to get that picture. That must be your mapping. Substitute Cells(rownum, counter) where the counter substitutes for B and in the first instance the rownum would be 6 for B6.

Just my $0.02.

Don't give up! And post back any time.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jeaneshe]

Well I was asked to make the existing code loop. I see what you do and I know it has major bloat, but since it isn't technically wrong, I just want to do the loop. I realize she has waithosts where she doesn't need them and also she doesn't have any type of validate when moving screen to screen. I will make sure that is done.

The only issue I have is feeding the column as a variable. Excel likes ranges and selections and I'm just not up to speed with that.

I am so glad you are out there today! I was hoping you might be.Thanks

 

[SkipVought]

Hope you can get it sorted out.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein