Excel to Extra Loop Problem 2

[jamesbt]

Hi all, I'm having a problem with the below code,

It does what I want it to do so far, however once its processed the first row in excel it ends, I cant get it to loop to the 2nd row in excel.

Also when I read the screen in extra my test variable writes back to excel its placing it on f row 15,

I think its something to do with the the set screen area, ie the way im searching the screen for the information to return the cursor position.

Basically im trying to read coloumb A then place that in my extra session, I then need to read coloumb B in excel then search my extra screen for the txt in the rearrange variable.

I then need to move the cursor to the left hand side of it, it will always be 16 spaces to the left hand side of the first letter of the txt in coloumb b.

I then need to read something from the screen and write it back to the same line coloumb f.

Then move onto the next line in excel.

Once other thing, I would like to have headings on row 1 in excel so everything should start from row 2 in excel.

Help truly appreciated.



' This section of code contains the recorded events

Set Sess1 = Sessions.Item(SessName1$)

Set MyScreen = Sess1.screen

With Excel.Worksheets("sheet1")


Row = 1

Do



Sess1.screen.SendKeys ("<Home>8<enter>")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)



Rearrange = .Cells(Row, "a").Value '**This will pull information from Excel
Sess1.screen.WaitHostQuiet (g_HostSettleTime)

MyScreen.putstring Rearrange, 15, 31 '**This is the cirucit to be re arranged

Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<Enter>")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<Pf22>")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)


Removelink = .Cells(Row, "b").Value '** The link I need to remove

MsgBox Removelink


Set MyArea = Sess1.screen.Search(Removelink)
Sess1.screen.moveto MyArea.Bottom, MyArea.Left - 16 ' this finds the remove link variable
Row = Sess1.screen.Row ' places cursor on row and col
col = Sess1.screen.col
MsgBox "row: " & Row & " /col: " & col



Sess1.screen.SendKeys ("J") ' this will allow me to delete the loosing link



Test = MyScreen.GetString(4, 40, 16) '**This will pull information from Extra, use your own coordinates

' Sess1.screen.WaitHostQuiet (g_HostSettleTime)

.Cells(Row, "f").Value = Test 'this doesnt put it on the same line in excel its putting it on line 15?



Row = Row + 1 '**This will progress the code to the next line, but it doesnt it just ends.

Sess1.screen.SendKeys ("<pf3><pf3>")

Loop Until .Cells(Row, "A").Value = "" '** This will loop code until there is a blank value in Column A in Excel

End With

End Sub

Many thanks,

James

 

[SkipVought]

hi,

Avoid using Row as a variable name as it is a reserve word.

Many programmers use a prefix notation on variables to signify the data type. ALSO, a good programmer declare ALL variables explicitly. I would use...

  Dim lRow as Long, iCol as Integer  'for Excel
  Dim iRW as integer, iCL as integer 'for Extra

for starts, being that integer datatype is only good to positive 32767 and excel more available rows than that.

Use GetString and PutString Extra methods to get and put text on the screen, rather that selecting a location on the screen. Use a separate set of row and column variables for the screen.

Your loop look OK, except for these two statements...

            Row = Sess1.screen.Row ' places cursor on row and col
            col = Sess1.screen.col

I would rather...

            iRW = Sess1.screen.Row ' places cursor on row and col
            iCL = Sess1.screen.col

and, of course, change the following references as required.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jamesbt]

Skip,

That is fantastic! I've to write a bit more code to do what I need to do, I'm getting closer but I'm learning so much, thank you for your assistance.

Thank you & to all people who post here, there is so much to learn! It's fun!

 

[SkipVought]

James,

You are the kind of person I enjoy corresponding with! Reminds me of the day I discovered Tek-Tips, too. You can learn a LOT here. I, certainly, have!

If your Extra coding ALWAYS involved Excel, which mine certainly does, I would recommend coding in Excel, as the Excel object model and VB code set if much richer, AND the VB Editor is more versatile.

You simply code the same for Extra Objects, but do not need to Create or Get the Excel application object et al. Just set a reference, in the Excel VB Editor Tools > References, to the Attachmate Extra! Object Library.

You might also check out forum707 and forum68 if you do much with Excel.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jamesbt]

Skip,

I've noticed in alot of your posts you advise the use of excel and now I'm writing everything in it, its alot more versatile, I am new to VB however I use Extra on an almost daily basis, I could kick myself for not looking into this before, better later than never!

I'll read through the information you suggest reading, again, thank you for your help.

Kind regards,

James

 

[vzachin]

james,
i wanted to add the following, in that your original code you used the variable ROW for both Excel & Extra....no wonder it's confused

 

[jamesbt]

Thanks Skip, still learning but its making sense now. Just completed what I need to do for today!

Getting closer.. Have a good weekend.

 

[jamesbt]

Dim lRow As Long, iCol As Integer 'for Excel
Dim iRW As Integer, iCL As Integer 'for Extra

Set Sess1 = Sessions.Item(SessName1$)

Set MyScreen = Sess1.screen

With Excel.Worksheets("sheet1")

Row = 2

Do

Sess1.screen.SendKeys ("<Home>8<enter>")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)

Rearrange = .Cells(Row, "A").Value '**The bearer we will re arrange
Sess1.screen.WaitHostQuiet (g_HostSettleTime)

MyScreen.putstring Rearrange, 15, 31 '**This is the circuit to be re arranged

Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<Enter>")

Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<Pf22>")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)

Sess1.screen.SendKeys ("<Home>u<Enter>")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("ir1<Tab><Enter>")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<Tab>")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<EraseEOF>")

NewAsn = .Cells(Row, "E").Value '** The new ASN on INS Job
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.putstring NewAsn

Sess1.screen.WaitHostQuiet (g_HostSettleTime)

Sess1.screen.SendKeys ("<enter>") ' when I hit enter I can get an error on screen,
Sess1.screen.WaitHostQuiet (g_HostSettleTime)


I want to read the Extra screen for the error,

NEW ASN/NP NUMBER INVALID - PLEASE AMEND

the screen position for the error is 22,02,40


If the error exists, I want to put a comment on my excel sheet col g stating

.Cells(Row, "G").Value = "No IR1 built" 'this puts no on G

I then want to Sess1.screen.SendKeys ("<pf3><pf3>")

At this point I want to ignore the rest of the code below and move onto the next line in excel

If the error does not exist then I want to move onto the next line of code below.




Sess1.screen.SendKeys ("<tab>y")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)


newdate = DateAdd("d", 365, Date) ' adds however many days I need to delay a job
MsgBox newdate
newdate = Format(newdate, "ddmmyy") 'for COZ eg "dd/mm/yy"
MsgBox newdate
Sess1.screen.SendKeys newdate
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<enter>")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<Pf22>")

Removelink = .Cells(Row, "B").Value '** The designation I need to remove
Sess1.screen.WaitHostQuiet (g_HostSettleTime)



Set MyArea = Sess1.screen.Search(Removelink)
Sess1.screen.MoveTo MyArea.Bottom, MyArea.Left - 16 ' this finds the remove link variable on screen
iRW = Sess1.screen.Row ' places cursor on row and col
iCL = Sess1.screen.col

' MsgBox "row: " & iRW & " /col: " & iCL
Sess1.screen.WaitHostQuiet (g_HostSettleTime)



Sess1.screen.SendKeys ("d") ' this will delete the loosing link
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<enter>")


Gaintrib = .Cells(Row, "D").Value '**This gets gaining bearer trib number

Sess1.screen.WaitHostQuiet (g_HostSettleTime)

Sess1.screen.putstring Gaintrib ' This puts gaining trib into extra

Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<tab>")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)

Gainingbearer = .Cells(Row, "C").Value '** Gets gaining bearer from col c in excel


Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.putstring Gainingbearer
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<tab>")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)

Sess1.screen.MoveTo iRW, iCL ' point of insertation of gaining bearer
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("I")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<enter>")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<home>")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("u")
Sess1.screen.SendKeys ("<enter>")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)

Sess1.screen.SendKeys ("<pf6>") ' Puts note on IR1
Sess1.screen.WaitHostQuiet (g_HostSettleTime)

Sess1.screen.MoveTo 9, 2
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("my notes ")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)

Sess1.screen.MoveTo 10, 2
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("my notes")

Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.MoveTo 11, 2
Sess1.screen.SendKeys ("my notes")


Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<enter><pf5>")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<pf11>")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<tab><tab>5")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<enter>")
Sess1.screen.WaitHostQuiet (g_HostSettleTime)
Sess1.screen.SendKeys ("<pf3><pf3>")


Row = Row + 1 '**This will progress the code to the next line from row 2 onwards as before do loop row = 2

Loop Until .Cells(Row, "A").Value = "" '** This will loop code until there is a blank value in Column A in Excel

End With

End Sub

 

[jamesbt]

Sorry I should have said can anyone at all help!

 

[SkipVought]

Burying your question in the middle of a slew of code, is not a good way to get help! At least use TGML Tags for emphasis! If you do not know what TGML Tags are, SEARCH on this page and follow the link.

You annotated, "when I hit enter I can get an error on screen." Not EVERY time, yes?

I have contol logic for every screen message. In fact, establishing the control logic of your procedure is probably one of the FIRST things I would design.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jamesbt]

I appreciate your comment Skip,

If I am in a do loop and I get to a point where I know I will get one of two messages.

NEW ASN/NP NUMBER INVALID - PLEASE AMEND

The screen position for the message is 22,02,40

or

REARRANGE SUCCESSFULLY CREATED

The screen position for the message is 22,02,30

If the message is NEW ASN/NP NUMBER INVALID - PLEASE AMEND

How do I only run the following code

.Cells(Row, "G").Value = "No IR1 built" 'this puts no on G

I then want to

Sess1.screen.SendKeys ("<pf3><pf3>") and move onto my next line of data in row A of data in excel

Or

2. If the message is REARRANGE SUCCESSFULLY CREATED

I would need to bypass the above code and run through the rest of my code which in turn would move me to the next line of data in row A of excel.

Kind regards,

James

 

[SkipVought]

    Dim sMSG As String, lRow As Long
    
    sMSG = Trim(Sess1.screen.GetString(22, 2, 80))
    
    Select Case sMSG
        Case "NEW ASN/NP NUMBER INVALID - PLEASE AMEND"
            xl.Cells(lRow, "G").Value = "No IR1 built"  'this puts no on G
            lRow = lRow + 1
            Sess1.screen.SendKeys ("<pf3><pf3>")
        Case "REARRANGE SUCCESSFULLY CREATED"
            'no op
    End Select

THIS is do not understand: "move me to the next line of data in row A of excel"

I there a DIFFERENT row counter for Excel column A & column G???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jamesbt]

There is not a different row counter for Excel coloumn A & G.

What I mean is if I get the error message

NEW ASN/NP NUMBER INVALID - PLEASE AMEND

I want to make a comment on column G saying "No IR1 built"

Then I want to move to the next row of data in Excel.

If I get the message

REARRANGE SUCCESSFULLY CREATED

Then I wish the code to continue on until the end, then move onto the next row in Excel.

 

[jamesbt]

Sorry instrad of saying Then I want to move to the next row of data in Excel.

example if I start at line 2 and I do not get the error message, I get REARRANGE SUCCESSFULLY CREATED, my code will run until the end then go onto line 3.

What im trying to say is, if for example I start at line 2 and I get the error message NEW ASN/NP NUMBER INVALID - PLEASE AMEND

I need to go back to the top but start at line 3, so I can work through the list I have in coloumn A.

This would apply for each line of data, so if row 5 I get error, goto top and start at line 6 and so on.

 

[SkipVought]

then just change where the excel row counter is incrimented.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jamesbt]

But I cant get it to go back to the begining of do.

 

[vzachin]

james,

all you need to do is to add the else statement, something like this:

if Sess1.Screen.Getstring (22,2,40) = "NEW ASN/NP NUMBER INVALID - PLEASE AMEND" then
	.Cells(Row, "G").Value = "No IR1 built" 
	Sess1.screen.SendKeys ("<pf3><pf3>")		
[blue]else[/blue]
	'the rest of your code goes here
	Sess1.screen.SendKeys ("<tab>y")    
	Sess1.screen.WaitHostQuiet (g_HostSettleTime)



end if

also, i'm a little leary about your last statement
"Loop Until .Cells(Row, "A").Value = "" "

what if you have a null value with spaces?

 

[jamesbt]

Zack, Skip thank you so so much, the problem I had was the "end if" statment was not in the correct place, it was directly after the variable check or case check. I have moved it to the end of my code before I increment it +1 and now it works in a continuous loop.
Zack I understand you being a little leary of last statment loop until A value is = ""

However, I will be having a list of Cirucits provided to me that will be full. So it should run until it finishes.

It would be nice no to know how to jump a space if there was one though.

This forum is really a great place to be, lots of information flying around, I have to read more and more and more! Trial and error, its the only way to learn.

I cant thank you both enough!

Kind regards,

James