Help

deboe · Sep 28, 2006

I have a document that has a row of items as such
UCBL
NCBL
TCBL
CCBL

What i'm trying to do is use my macro so that I can go through and search according to say for instance "UCBL" and once this particular one is identified I would like to have and number placed in another location (2,25). I would also need a counter in that code to place in that location (2,25). The counter would count which line i'm on and that's the number i need to put in (2,25)
If anyone understands me could you please give me some advice.

IknowMe · Sep 28, 2006

For Row = 1 to Screen.Rows()
Select Case Screen.GetString(Row,1,4)
Case"UCBL"
Screen.PutString lSomeNum Row,25
Case"NCBL"
Screen.PutString lSomeNum Row,25
Case"TCBL"
Screen.PutString lSomeNum Row,25
Case"CCBL"
Screen.PutString lSomeNum Row,25
End Select
Next Row

There's the idea but I'm not sure where your tags are located on your screen so I assumed Column 1, but you can change as needed. I also had no clue what you meant by "The counter would count which line i'm on and that's the number i need to put in (2,25)". I gather you want to put something in the row you found the tag but I'm not sure if you meant column 2 or 25, or if you wanted to put the number 25 in column 2 or whatever. If you can clear up the request maybee I can help more.

[thumbsup2]

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

deboe · Sep 28, 2006

MrMilson

Thanks for the input but here is a copy of my screen shot

REST S-BFAFX 09/28/06 15:47:44 034/001
FUNC FRWD WHO ITEM << RESTRICTED SECURITIES - ACCOUNT LIST >> SUB 001
1355-576 BFAFX 097873-40-0 BOND FUND AMER INC CL F

ITEM ACCOUNT NAME A DATE
01 0093-5217 DIVIDEND NETWORKING REIN X 10/28/05
02 0094-3225 MUTUAL FUND ERROR ACCOUNT X 11/02/05
03 0094-9502 MUTUAL FUND ERROR ACCOUNT X 01/05/06
04 CBAP *** ACCOUNT GROUP *** X 12/28/05
05 CCBL *** ACCOUNT GROUP *** X 05/16/05
06 CCBP *** ACCOUNT GROUP *** X 05/16/05
07 CDFL *** ACCOUNT GROUP *** X 02/01/06
08 CNDA *** ACCOUNT GROUP *** X 05/16/05
09 CPFA *** ACCOUNT GROUP *** X 05/31/05
10 CPFP *** ACCOUNT GROUP *** X 05/16/05
11 CSAP *** ACCOUNT GROUP *** X 01/10/06
12 NBAP *** ACCOUNT GROUP *** X 08/26/05
13 NCBL *** ACCOUNT GROUP *** X 05/16/05
14 NCBP *** ACCOUNT GROUP *** X 05/16/05
15 NDFL *** ACCOUNT GROUP *** X 02/01/06
16 NNDA *** ACCOUNT GROUP *** X 05/16/05

F1-SLIST F2-SDETL F4-BLIST F5-RLIST F6-BLSKY F7-BACK F8-FRWD

What I have to do is Identify which the product classes CCBL then I must put that item number into the section entitled Item number (2,25) to bring up that particular section.
If that product class is not on that page I must press enter and go to the next page and search 16 more product classes
in order to find the ones that I'm looking for. Do I need some type of counter in order to do that or can I just pull that number from the left side of the bar.

IknowMe · Sep 28, 2006

If your only looking for CCBL then

For Row = 1 to Screen.Rows()
If Screen.GetString(Row,6,4) = "CCBL" Then
sItemNumber = Screen.GetString(Row,3,2)
'Still not sure what your doing with the Item Number
End If
Next Row

In your screen shot above this code will set sItemNumber to "05" is that what your wanting? If so what do you do with "05" from that point? Input it on the screen somewhere? Where? Then What?

Will more than one ITEM be CCBL? If you find it on the first page do you still need to search a second page? There needs to be another loop added to turn the page if it is nessecary.

[thumbsup2]

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

deboe · Sep 29, 2006

Yes, I want to set the sItemNumber to the number located to the left of the "CCBL". When I get the "05" then I want to put that into postion (2,25) by using the Screen.PutString sItemNumber Row 2, Column 25 and then press "Enter".
With this I Will be searching for multiple items. For instance in the above, I will be searching for NCBL and CCBL. If I find it on the first page then I don't need to locate it on the second page but If I don't find it then I need to circle to the next page. So basically if I find the "CCBL" and If I don't I will still need to go to the second page but I will be searching for more than just these two items. I will need this search to start at Row 6 and search through Row 22 is that possible? MrMilson, You have really helped me out so far and I appreciate it very much.

IknowMe · Sep 29, 2006

Following code will put the Item # of whichever case it finds first on Row 2 Column 25, Is that what your after? At what point do you press enter? Does that change the screen? Should you look for all cases on all pages prior to the enter? I feel there's more than to this process than your describing here. As skip would say "remember I'm blind" meaning I need to know exactly what your doing before I can help more. Is the next page formatted the same as the first? Is there anything on the screen to let us know that 'turning the page' was sucessfull?

Code:

For Row = 6 to 22
    Select Case Screen.GetString(Row,1,4)
        Case"UCBL"
             Screen.PutString Screen.GetString(Row,3,2) 2,25
        Case"NCBL"
             Screen.PutString Screen.GetString(Row,3,2) 2,25
        Case"TCBL"
             Screen.PutString Screen.GetString(Row,3,2) 2,25
        Case"CCBL"
             Screen.PutString Screen.GetString(Row,3,2) 2,25
    End Select
Next Row

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

deboe · Sep 29, 2006

Yes, That is exactly what I'm after. After you search from row 6 to 22 and you don't find any of those cases then you should press enter which would take you to another page of 6 to 22. So in all you would have three pages of 6 to 22 to search before you can officially say that its not there. Upon pressing enter the screen does indicate that it has changed.

MrMilson,

I have to search files with the above screen shot that possess "CCBL, NCBL, TCBL, UCBL, CCBP, NCBP, TCBP, UCPB" these accts. If one is found then I must put that item number in 2,25 and input some key strokes. Then I must return to the previous screen and continue searching until all of these accts are found. If they are not found then I must add them to the list and I already know who to do that portion. The search is what was puzzling me currently.

Also, Do I need to declare and initialize any variables inside of the Case Selection prior to starting the process.

The next page is formatted exactly as the first page is. Again there is nothing to let me know that the screen has changed screens but the accts are in alphabetical order on each screen.

Is there anything else you would like to know. I think this is almost completed though. You have helped me tremendously thus far. I really appreciate it.

IknowMe · Sep 29, 2006

Yes, That is exactly what I'm after. After you search from row 6 to 22 and you don't find any of those cases then you should press enter which would take you to another page of 6 to 22. If I did find one should I jump out of the loop with a function, do something for that record and then come back? (If so what keystrokes am I doing to get there? Once I'm there?) Should I then come back where I left off and continue searching the original page? (what keystrokes bring me back?) So in all you would have three pages of 6 to 22 to search before you can officially say that its not there. Upon pressing enter the screen does indicate that it has changed. What does it say? Where at on the screen?

MrMilson,

I have to search files with the above screen shot that possess "CCBL, NCBL, TCBL, UCBL, CCBP, NCBP, TCBP, UCPB" these accts. If one is found then I must put that item number in 2,25 and input some key strokes. What keystrokes? Then I must return to the previous screen and continue searching until all of these accts are found. What keystrokes bring me back? If they are not found then I must add them to the list and I already know who to do that portion. The search is what was puzzling me currently.

Also, Do I need to declare and initialize any variables inside of the Case Selection prior to starting the process. Just the variable titled 'Row' I'd dim it as an Integer.

The next page is formatted exactly as the first page is. Again there is nothing to let me know that the screen has changed screens but the accts are in alphabetical order on each screen. Is it safe to say we could compare the last Item number from the previous page to the first Item number of the following page? e.x. we ended up at 16 on your sample page does the next page start at 17?

[thumbsup2]

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

deboe · Sep 29, 2006

For Row = 6 to 22
Select Case Screen.GetString(Row,7,4)
Case"CCBL"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Screen.PutString Closed 8,2
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
Case"CCBP"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Screen.PutString Closed 8,2
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
Case"NCBL"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Screen.PutString Closed 8,2
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
Case"NCBP"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Screen.PutString Closed 8,2
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
Case"TCBL"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Screen.PutString Closed 8,2
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
Case"TCBP"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Screen.PutString Closed 8,2
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
Case"UCBL"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Screen.PutString Closed 8,2
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
Case"UCPB"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Screen.PutString Closed 8,2
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
End Select
Next Row

THIS IS A COPY OF THE CODE THAT I MANIPULATED TO DO EXACTLY WHAT NEEDS TO BE DONE. THE STRING CLOSED IS "XB".
AFTERWARDS YOU DO NEED TO COME BACK WHERE YOU LEFT OFF AND CONTINUE SEARCHING THE ORIGINAL PAGE.
THE KEYSTROKES THAT ARE NEEDED ARE IN THE CODE PREVIOUSLY PROVIDED. THE KEYSTROKE F3 WILL BRING YOU BACK TO THE PREVIOUS PAGE YOU WERE ON.
WHEN YOU DON'T FIND ANY OF THOSE CASES ON ONE PAGE THEN THE NUMBERS AUTOMATICALLY START WITH 1 TO 16 ON THE NEXT PAGE. THE ONLY THING THAT CHANGES ARE THE ACCT TYPE (IE....CCBL, NCBL ETC.)

I NEED TO BE ABLE TO LOOP THROUGH THESE SELECTIONS UNTIL EVERYONE IS FOUND AND IF NONE OF THEM ARE FOUND THEN I NEED TO BE ABLE TO CREATE THEM. SO WITH WHAT KEY WOULD I NEED TO CREATE NEW ITEMS IF THE SELECTION DOESN'T WORK. DO I NEED AND IF ELSE STATEMENT WITH THE SELECTION CASES ON THE INSIDE.

IknowMe · Sep 29, 2006

Something like this,

Code:

Dim bFound as Boolean
For Page = 1 to 3
  For Row = 6 to 22
    Select Case Screen.GetString(Row,7,4)
      Case"CCBL"
        Screen.PutString Screen.GetString(Row,3,2) 2,25
        Sess0.Screen.Sendkeys("<Enter>")
        Sess0.Screen.Sendkeys("corr")
        Sess0.Screen.Sendkeys("D2V")
        Screen.PutString Closed 8,2
        Sess0.Screen.Sendkeys("<Enter>")
        Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")            
        Sess0.Screen.Sendkeys("<Pf3>")   
        bFound = True
      Case"CCBP"
        Screen.PutString Screen.GetString(Row,3,2) 2,25
        Sess0.Screen.Sendkeys("<Enter>")
        Sess0.Screen.Sendkeys("corr")
        Sess0.Screen.Sendkeys("D2V")
        Screen.PutString Closed 8,2
        Sess0.Screen.Sendkeys("<Enter>")
        Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")            
        Sess0.Screen.Sendkeys("<Pf3>")   
        bFound = True
      Case"NCBL"
        Screen.PutString Screen.GetString(Row,3,2) 2,25
        Sess0.Screen.Sendkeys("<Enter>")
        Sess0.Screen.Sendkeys("corr")
        Sess0.Screen.Sendkeys("D2V")
        Screen.PutString Closed 8,2
        Sess0.Screen.Sendkeys("<Enter>")
        Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")            
        Sess0.Screen.Sendkeys("<Pf3>")   
        bFound = True
      Case"NCBP"
        Screen.PutString Screen.GetString(Row,3,2) 2,25
        Sess0.Screen.Sendkeys("<Enter>")
        Sess0.Screen.Sendkeys("corr")
        Sess0.Screen.Sendkeys("D2V")
        Screen.PutString Closed 8,2
        Sess0.Screen.Sendkeys("<Enter>")
        Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")            
        Sess0.Screen.Sendkeys("<Pf3>")   
        bFound = True
      Case"TCBL"
        Screen.PutString Screen.GetString(Row,3,2) 2,25
        Sess0.Screen.Sendkeys("<Enter>")
        Sess0.Screen.Sendkeys("corr")
        Sess0.Screen.Sendkeys("D2V")
        Screen.PutString Closed 8,2
        Sess0.Screen.Sendkeys("<Enter>")
        Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")            
        Sess0.Screen.Sendkeys("<Pf3>")   
        bFound = True
      Case"TCBP"
        Screen.PutString Screen.GetString(Row,3,2) 2,25
        Sess0.Screen.Sendkeys("<Enter>")
        Sess0.Screen.Sendkeys("corr")
        Sess0.Screen.Sendkeys("D2V")
        Screen.PutString Closed 8,2
        Sess0.Screen.Sendkeys("<Enter>")
        Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")            
        Sess0.Screen.Sendkeys("<Pf3>")   
        bFound = True
      Case"UCBL"
        Screen.PutString Screen.GetString(Row,3,2) 2,25
        Sess0.Screen.Sendkeys("<Enter>")
        Sess0.Screen.Sendkeys("corr")
        Sess0.Screen.Sendkeys("D2V")
        Screen.PutString Closed 8,2
        Sess0.Screen.Sendkeys("<Enter>")
        Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")            
        Sess0.Screen.Sendkeys("<Pf3>")   
        bFound = True
      Case"UCPB"
        Screen.PutString Screen.GetString(Row,3,2) 2,25
        Sess0.Screen.Sendkeys("<Enter>")
        Sess0.Screen.Sendkeys("corr")
        Sess0.Screen.Sendkeys("D2V")
        Screen.PutString Closed 8,2
        Sess0.Screen.Sendkeys("<Enter>")
        Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")            
        Sess0.Screen.Sendkeys("<Pf3>")                     
        bFound = True
    End Select
  Next Row
  Sess0.Screen.Sendkeys("[COLOR=red]Whatever moves you to the next page[/color]")                     
Next Page

If not bFound Then
  [COLOR=red]Whatever you need to do if you didn't find anything on any of the 3 pages[/color]
End If

Post your final code and I'll give you some tips on cleaning it up.

[thumbsup2]

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

IknowMe · Sep 29, 2006

change Screen.PutString to Sess0.Screen.PutString if you haven't set a screen object.

I'm assuming your Setting a Sess0 object.

[thumbsup2]

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

deboe · Sep 29, 2006

I set Closed$ = XB

Is that good enough for the Screen.PutString Closed 8,2?

I will send you my final code, thanks

deboe · Sep 29, 2006

I will make sure you have my code on monday to look at

Thanks A Million

deboe · Oct 2, 2006

Dim Row as Integer
Dim msgtext
Dim Buffersize as Integer
Dim Columns as Integer
Dim Rows as Integer
Dim maxColumns as Integer
Dim maxRows as Integer
Dim LineBuffer as String
Dim ScreenBuffer as String

Close #1
close #2
close #3

startrow% = Inputbox ("Input Starting Row")

If startrow = 0 then
msgbox "Cancel"
exit sub
end if

rem Open "D:\tst\TEMP003.csv" for Input as #1
Open "D:\tst\myfile2.csv" for Input as #1
Open "D:\tst\TEMP004" for append as #2
Open "D:\tst\TEMP005.csv" for append as #3

do until y = startrow
Input #1, cusip

y=y+1
loop

x=y

Sess0.Screen.Sendkeys("<Home><Clear>")

do Until x = endrow
rem do while EOF(1) <> True
Input #1, cusip

Sess0.Screen.Sendkeys("<Home><Clear>")
rem sess0.Screen.WaithostQuiet(g_HostSettleTime)

do while Sess0.Screen.OIA.Xstatus<>0
doevents
Loop

Sess0.Screen.Sendkeys("REST Q-")
Sess0.Screen.PutString cusip
Sess0.Screen.Sendkeys("<enter>")

do while Sess0.Screen.OIA.Xstatus<>0
doevents
Loop

Sess0.Screen.Sendkeys("<PF3>")

rem sess0.Screen.WaithostQuiet(g_HostSettleTime)

Closed$ = XB

Dim bFound as Boolean
For Page = 1 to 3
For Row = 6 to 22
Select Case Screen.GetString(Row,7,4)
Case"CCBL"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Screen.PutString Closed 8,2
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
bFound = True
Case"CCBP"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Screen.PutString Closed 8,2
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
bFound = True
Case"NCBL"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Screen.PutString Closed 8,2
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
bFound = True
Case"NCBP"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Screen.PutString Closed 8,2
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
bFound = True
Case"TCBL"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Screen.PutString Closed 8,2
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
bFound = True
Case"TCBP"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Screen.PutString Closed 8,2
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
bFound = True
Case"UCBL"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Screen.PutString Closed 8,2
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
bFound = True
Case"UCPB"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Screen.PutString Closed 8,2
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
bFound = True
End Select
Next Row
Sess0.Screen.Sendkeys("<Enter>")
Next Page

If not bFound Then
Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.Sendkeys("<BackTab><BackTab>")
Sess0.Screen.Sendkeys("add ")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("UCPB")
Sess0.Screen.Sendkeys("<Tab>X<Tab><Tab>XB<Enter>")
Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.Sendkeys("<BackTab><BackTab>")
Sess0.Screen.Sendkeys("add ")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("UCBL")
Sess0.Screen.Sendkeys("<Tab>X<Tab><Tab>XB<Enter>")
Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.Sendkeys("<BackTab><BackTab>")
Sess0.Screen.Sendkeys("add ")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("TCBP")
Sess0.Screen.Sendkeys("<Tab>X<Tab><Tab>XB<Enter>")
Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.Sendkeys("<BackTab><BackTab>")
Sess0.Screen.Sendkeys("add ")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("TCBL")
Sess0.Screen.Sendkeys("<Tab>X<Tab><Tab>XB<Enter>")
Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.Sendkeys("<BackTab><BackTab>")
Sess0.Screen.Sendkeys("add ")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("NCBP")
Sess0.Screen.Sendkeys("<Tab>X<Tab><Tab>XB<Enter>")
Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.Sendkeys("<BackTab><BackTab>")
Sess0.Screen.Sendkeys("add ")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("NCBL")
Sess0.Screen.Sendkeys("<Tab>X<Tab><Tab>XB<Enter>")
Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.Sendkeys("<BackTab><BackTab>")
Sess0.Screen.Sendkeys("add ")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("CCBP")
Sess0.Screen.Sendkeys("<Tab>X<Tab><Tab>XB<Enter>")
Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.Sendkeys("<BackTab><BackTab>")
Sess0.Screen.Sendkeys("add ")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("CCBL")
Sess0.Screen.Sendkeys("<Tab>X<Tab><Tab>XB<Enter>")
Sess0.Screen.Sendkeys("<Pf3>")
End If

do while Sess0.Screen.OIA.Xstatus<>0
doevents
Loop

Acctstring$ = Sess0.Screen.getstring(3,2,19)
CycleString$ = Sess0.Screen.getstring(9,36,21)
MsgString$ = Sess0.Screen.getstring(24,2,50)

maxRows% = sessions.screen.rows()
maxColumns% = Sessions.screen.Cols()
ScreenBuffer$ = ""
LineBuffer$ = Space$(maxcolumn)

For row% = 1 to maxrows%
Linebuffer$ = sessions.screen.area (row%,1,row%,maxcolumns%,,xblock)
screenbuffer$ = screenbuffer$ + linebuffer$ + chr(13)+chr(10)
next

screenbuffer$ = screenbuffer$ + chr(13)+chr(10)
screenbuffer$ = screenbuffer$ + chr(13)+chr(10)

write #2, screenbuffer

WriteDate$ = Date()
writeTime$ = Time()
write #3, x, acct, Acctstring, CycleString, MsgString, Writedate, writetime, who

x=x+1
Loop

Close #1
Close #2
Close #3

End Sub

MrMilson,

The Above information is my code.

My Goals are as follows:

I want to open this file (cusip.xls) so I can read these numbers from and input them during the program.

I also want to then run them through my case scenario.

After running them through the case scenario, I want to print the comment from the bottom of the page that says "Done, Record Restriction Added" into that file right next to cusip.

Is this possible and How can I accomplish this?

I attempted to put what I thought would work above so could you please look at that information and let me know.

Thanks Again

IknowMe · Oct 2, 2006

This should help but your taking a big first step here.

This is assuming you have both your extra and excel open, that your information in Excel is in Column 1 or "A" and that Column 2 is blank so you can write to it. We can flesh this out once we get the first bit working for you.

Code:

Sub Main

'Extra Object
  Dim Sys_Obj As Object, Sess_Obj As Object, MyScreen_Obj As Object
  Set Sys_Obj = CreateObject("EXTRA.System")
  Set Sess_Obj = Sys.ActiveSession
  Set MyScreen_Obj = Sess.Screen
    
'Excel Object
  Dim Excel_Obj As Object, Excel_Sheet As Object
  Set Excel_Obj = GetObject(,"excel.application")
  Set Excel_Sheet = Excel_Obj.Workbooks("cusip.xls").Worksheets(1)         

 Dim Extra_Col as Integer
 Dim Extra_Row as Integer
 Dim Extra_Page as Integer
 Dim Excel_Row as Integer
 Dim Excel_Col as Integer

 LastExcelRow = Excel_Sheet.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1).Row
 For Excel_Row = Inputbox ("Input Starting Row") to LastExcelRow
   MyScreen_Obj.Sendkeys("<Home><Clear>")
   MyScreen_Obj.Sendkeys("REST Q-")    
   MyScreen_Obj.PutString Excel_Sheet.Cells(Excel_Row, 1).txt  
   MyScreen_Obj.Sendkeys("<enter>")  
   MsgBox "You should be on Extra Screen for column one of start row of Excel, now I'll write something to Excel column 2 on the same row"
   Excel_Sheet.Cells(Excel_Row, 2).Value = "Made it ?"
 Next Excel_Row
End Sub

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

deboe · Oct 3, 2006

Could you take a look at this code and try it. For some reason it won't compile.

The code is as follows:

'--------------------------------------------------------------------------------
' This macro was created by the Macro Recorder.
' Date: Wednesday, September 27, 2006 14:02:51
' User: deboee
'--------------------------------------------------------------------------------

' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$

Sub Main

'--------------------------------------------------------------------------------
' Get the main system object
Set System = CreateObject("EXTRA.System") ' Gets the system object
If (System is Nothing) Then
Msgbox "Could not create the EXTRA System object. Stopping macro playback."
STOP
End If
Set Sessions = System.Sessions

If (Sessions is Nothing) Then
Msgbox "Could not create the Sessions collection object. Stopping macro playback."
STOP
End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
g_HostSettleTime = 2000 ' milliseconds

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

' Get the necessary Session Object
If (Sess0 is Nothing) Then
Msgbox "Could not create the Session object. Stopping macro playback."
STOP
End If
If Not Sess0.Visible Then Sess0.Visible = TRUE
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

' This section of code contains the recorded events

System.TimeoutValue = OldSystemTimeout

'Extra Object
Dim Sys_Obj As Object, Sess_Obj As Object, MyScreen_Obj As Object
Set Sys_Obj = CreateObject("EXTRA.System")
Set Sess_Obj = Sys.ActiveSession
Set MyScreen_Obj = Sess.Screen

'Excel Object
Dim Excel_Obj As Object, Excel_Sheet As Object
Set Excel_Obj = GetObject(,"excel.application")
Set Excel_Sheet = Excel_Obj.Workbooks("cusip.xls").Worksheets(1)

Dim Extra_Col as Integer
Dim Extra_Row as Integer
Dim Extra_Page as Integer
Dim Excel_Row as Integer
Dim Excel_Col as Integer

LastExcelRow = Excel_Sheet.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1).Row
For Excel_Row = Inputbox ("Input Starting Row") to LastExcelRow
MyScreen_Obj.Sendkeys("<Home><Clear>")
MyScreen_Obj.Sendkeys("REST Q-")
MyScreen_Obj.PutString Excel_Sheet.Cells(Excel_Row, 1).txt
MyScreen_Obj.Sendkeys("<enter>")
MsgBox "You should be on Extra Screen for column one of start row of Excel, now I'll write something to Excel column 2 on the same row"
Excel_Sheet.Cells(Excel_Row, 2).Value = "Made it ?"
Next Excel_Row
End Sub

rem Sess0.Screen.WaithostQuiet(g_HostSettleTime)

Dim bFound as Boolean

For Page = 1 to 3
For Row = 6 to 22
Select Case Screen.GetString(Row,7,4)
Case"CCBL"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
bFound = True
Case"CCBP"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
bFound = True
Case"NCBL"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
bFound = True
Case"NCBP"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
bFound = True
Case"TCBL"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
bFound = True
Case"TCBP"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
bFound = True
Case"UCBL"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
bFound = True
Case"UCPB"
Screen.PutString Screen.GetString(Row,3,2) 2,25
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("corr")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("<Tab><Tab><Tab><Tab><Tab>XB")
Sess0.Screen.Sendkeys("<Pf3>")
bFound = True
End Select
Next Row
Sess0.Screen.Sendkeys("<Enter>")
Next Page

If not bFound Then
Sess0.Screen.Sendkeys("REST Q-")
Sess0.Screen.PutString cusip
Sess0.Screen.Sendkeys("<enter>")
Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.Sendkeys("<BackTab><BackTab>")
Sess0.Screen.Sendkeys("add ")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("UCPB")
Sess0.Screen.Sendkeys("<Tab>X<Tab><Tab>XB<Enter>")
Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.Sendkeys("<BackTab><BackTab>")
Sess0.Screen.Sendkeys("add ")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("UCBL")
Sess0.Screen.Sendkeys("<Tab>X<Tab><Tab>XB<Enter>")
Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.Sendkeys("<BackTab><BackTab>")
Sess0.Screen.Sendkeys("add ")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("TCBP")
Sess0.Screen.Sendkeys("<Tab>X<Tab><Tab>XB<Enter>")
Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.Sendkeys("<BackTab><BackTab>")
Sess0.Screen.Sendkeys("add ")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("TCBL")
Sess0.Screen.Sendkeys("<Tab>X<Tab><Tab>XB<Enter>")
Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.Sendkeys("<BackTab><BackTab>")
Sess0.Screen.Sendkeys("add ")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("NCBP")
Sess0.Screen.Sendkeys("<Tab>X<Tab><Tab>XB<Enter>")
Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.Sendkeys("<BackTab><BackTab>")
Sess0.Screen.Sendkeys("add ")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("NCBL")
Sess0.Screen.Sendkeys("<Tab>X<Tab><Tab>XB<Enter>")
Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.Sendkeys("<BackTab><BackTab>")
Sess0.Screen.Sendkeys("add ")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("CCBP")
Sess0.Screen.Sendkeys("<Tab>X<Tab><Tab>XB<Enter>")
Sess0.Screen.Sendkeys("<Pf3>")
Sess0.Screen.Sendkeys("<BackTab><BackTab>")
Sess0.Screen.Sendkeys("add ")
Sess0.Screen.Sendkeys("D2V")
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.Sendkeys("CCBL")
Sess0.Screen.Sendkeys("<Tab>X<Tab><Tab>XB<Enter>")
Sess0.Screen.Sendkeys("<Pf3>")
End If

do while Sess0.Screen.OIA.Xstatus<>0
doevents
Loop

MsgString$ = Sess0.Screen.getstring(24,2,50)

write #1, MsgString, x

x=x+1
Loop

Close #1
Close #2
Close #3

End Sub

IknowMe · Oct 3, 2006

Your pasting it all together wrong, did the last bit of code I provided compile and run on it's own? We need to get it reading and writing to your excell sheet first, then we'll add the rest.

[thumbsup2]

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

deboe · Feb 8, 2007

I am trying to Print a Screen but there is no way to Hard Code in to Print Screen or anything else.
Could you anyone find me a handy way to print a screen?

Thanks

DeBoe

IknowMe · Feb 8, 2007

deboe,

have you tried
SessionObj.sendkeys ("<printrange>")

[thumbsup2]

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Help

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Programmer

Technical User

Technical User

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor