Contact US

Question

Having trouble with paste in Extra Extreme.

link99sbc (TechnicalUser)

(OP)

9 Apr 09 01:13

' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$

Sub Main()
'--------------------------------------------------------------------------------
' Get the main system object
    Dim Sessions As Object
    Dim System As 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 = 300        ' milliseconds

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

' Get the necessary Session Object
    Dim Sess0 As Object
    Set Sess0 = System.ActiveSession
    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

        Dim Sys As Object, Sess As Object, MyScreen As Object, MyArea As Object

    Set Sys = CreateObject("EXTRA.System")
    Set Sess = Sys.ActiveSession
    Set MyScreen = Sess.Screen

'Activates the Spreedsheet which is already open
        AppActivate "Microsoft Excel - SCOP stats.xls"

'Copies the active cell and moves down to the next cell which makes it the next active cell.
'I do this more than a 1000 times.  "Column with over 1000 names"
        Sendkeys "%EC{Down}"
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

'Alt tabs back to Extra Session
        Sendkeys "%{tab}"
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

'Goes to the home position, tabs to the next variable and
'types Name and goes to the next variable (Field) by default
'since only four characters are required in the first field.
        Sess0.Screen.Sendkeys("<Home><Tab>NAME")
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

'Paste the copied cell from Excel in the second variable space or Field if you prefer.
        Sendkeys "%EP"
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

'Triggers Extra Session to search a Data Base for the Name that was pasted in the second field.
        Sess0.Screen.Sendkeys("<Enter>")

    System.TimeoutValue = OldSystemTimeout
End Sub

This works fine in Extra Enterprise but will not paste in Extra Extreme 9.0 SERVICE PAC 2
using "%EP" , Sess0.Screen.paste, MyScreen paste, My Area paste.  No matter how I write
it, IT WILL NOT PASTE!  Any ideas?  It will paste using the mouse or keyboard.

vzachin (TechnicalUser) · Answer 1 · 2009-04-09 10:48:32

hi,

i can't answer your question but i'm curious why you don't use
sess0.putstring "exceldata", 1,1

where "exceldata" is the Excel Cell Data
and
1,1 are the Extra screen coordinates

i don't have extreme 9.0

zach

link99sbc (TechnicalUser) · Answer 2 · 2009-04-09 18:11:11

I don't know how to write the putstring.
Put what? I need it to be able to go
to the next active cell and copy when I
run the macro again.

link99sbc (TechnicalUser) · Answer 3 · 2009-04-30 23:16:17

when I use what you suggested it just puts the word exceldata at 3,23. I don't know how to code the object
when excel is already open and get the active cell then make the next cell active for when i run the macro again.
I found code that opens an excel file but my file is always open. The coding to do this in extreme 9 should not be any different as coding for prior versions. I just don't know how to do it from start to finish. I've come close but no cigar.

vzachin (TechnicalUser) · Answer 4 · 2009-05-01 19:01:14

see if this helps

CODE

Sub Main

Dim obj as object
Dim objWorkbook as object
Set obj=CreateObject("Excel.Application")

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

Set obj = Getobject("C:\xxxx.xls")       'where xxxx is the location/name of the file on the C Drive
set objWorkbook=obj.Worksheets("Sheet1")

For i = 2 To obj.ActiveSheet.Rows.Count  'assumption data begins with row 2
MyName = objWorkBook.Range("A" & i)      'assumption data begins in column A

If Trim(MyName) = "" Then exit sub

sess.screen.putstring MyName,3,23        'places the data from Excel in Extra
'do stuff in extra...

next i                                    'i is the next row

end sub

is this the "COPY"

CODE

Sendkeys "%EC{Down}"

command in Excel?

link99sbc (TechnicalUser) · Answer 5 · 2009-05-01 22:23:19

Thanks I'll give it a try when I go back to work monday.
I usually have over 1000 names in a column OR sometimes I'll have account numbers in a column. The columns are not always in the same place. Depends on the project. Sometimes ColA, or sometimes ColC. I can only research so many in a day so the next day I open the file and click on the cell I want to start on to make it active. I have to research each one on our data base.

%EC is the same as doing Alt+E and pressing C to copy. {down} move to the next cell down which makes is active. It's very simple. It works fine but with the new Extra Extreme it won't paste when I Alt+tab back to Extra from Excel. For all intents and purposes it should. Attachmate won't support macros so their no help. I have over 300 macros that I figured out in Extra but I don't know VB. But i figured VB may be the only sure way to make it work so I appreciate any help you can offer. I like the way you explain each line so I can understand whats happening. I think that's the only way I'm going to learn VB. I looked at every post on here and I don't have a clue most of the time. I'm just a simple user trying to make it go faster. Thanks again for trying to help.

link99sbc (TechnicalUser) · Answer 6 · 2009-05-01 23:21:32

Some days I can only research about 100 so the next day I open the file and click the cell I want to start on to make it active.

I have a crude macro in excel for another project that requires columns A and B. I enter a 1 if yes in colA and 1 if No in colB. One macro button for Yes runs "Sub Yes" and one button for No runs "Sub No"

Sub CopyName()
'
' CopyName Macro
' Macro recorded 4/1/2009
'
    Selection.Copy
    SendKeys "%{TAB}{HOME}{TAB}NAME                                                  {Home}{Tab}{Tab}"
    SendKeys "%EP{TAB}{TAB}EF+{Enter}"
    SendKeys "+{Enter}"



End Sub

Sub Yes()
ActiveCell.offset(0, -2) = 1  'moves to colA and enter 1
ActiveCell.offset(0, 2)       'back to colC
ActiveCell.Offset(1, 0).Select 'down and make cell active
Call CopyName
End Sub
Sub No()
ActiveCell.offset(0, -1) = 1  'moves to colB and enter 1
ActiveCell.offset(0, 1)       'back to colC
ActiveCell.Offset(1, 0).Select 'down and make cell active
Call CopyName
End Sub
Columns A and B total at the end.  But again I have to do this from Excel.  I want to do it from Extra if possible.

vzachin (TechnicalUser) · Answer 7 · 2009-05-02 13:15:12

Quote:
Depends on the project. Sometimes ColA, or sometimes ColC

if the data always begins in a certain row but different column A or C, it's fairly easy to check that. eg:

CODE

If trim(objWorkBook.Range("A2")) <> "" Then
MyName = objWorkBook.Range("A" & i)
else
If trim(objWorkBook.Range("C2")) <> "" Then
MyName = objWorkBook.Range("C" & i)
End If

Of course, this assumes that if there is data in "A2" then there won't be any data in "C2".

it's so much easier to write the code within Excel VBA but it's a matter of choice.

the above code works in Excel as well as Extra. Copy the code into a module and give it a try.

post back if you have more questions. there is tremendous help on this forum.

vzachin (TechnicalUser) · Answer 8 · 2009-05-02 16:58:42

oops, that should be

CODE

objWorkBook.trim(Range("A2"))

SkipVought (Programmer) · Answer 9 · 2009-05-02 17:38:40

V,

You had the Trim correct previously.

However, the Range object's parent is a Woeksheet object, not a Workbook object.

Skip,
^{Don't let the Diatribe...
talk you to death!}_{Just traded in my old subtlety...
for a NUANCE!}

link99sbc (TechnicalUser) · Answer 10 · 2009-05-02 20:38:45

I think the biggest problem is each day the active cell is going to start on a different row as I work down the list. That's why I was trying to figure out how to get the active cell no matter what row it's on or what col it's in.

vzachin (TechnicalUser) · Answer 11 · 2009-05-03 08:22:18

thanks skip for keeping me honest.

link99: AttachMate solutions Linkssbc,
question:
(1)on your excel sheet, will there be more than one column of data?
(2)when you work down your list and arrive at the "active cell", is there any other data above that cell within that column?
(3)and is the data continuous down that column?
(4)is there any reason why you can't evoke the macro from excel instead?

SkipVought (Programmer) · Answer 12 · 2009-05-03 13:06:18

[code]
With Activecell
Msgbox .row & " is the row"
Msgbox .column & " is the column"
end with
what else do you need to know?

Skip,
^{Don't let the Diatribe...
talk you to death!}_{Just traded in my old subtlety...
for a NUANCE!}

link99sbc (TechnicalUser) · Answer 13 · 2009-05-03 17:13:12

SkipVought (Programmer) · Answer 14 · 2009-05-03 19:05:24

When I program ranges on an Excel sheet, I AVIOD using the ActiveCell method. Rather I explicitly reference an range of this kind, using the Cells(row,column) method. That way you always know know where you are.

I do not understand your dilema. If, in the logic for a sheet, there are 2 columns, at any time you ought to know which column you are in for any row. If it's the last column, then when the command for the next row is issued, yout logic chooses the first column.

Skip,
^{Don't let the Diatribe...
talk you to death!}_{Just traded in my old subtlety...
for a NUANCE!}

vzachin (TechnicalUser) · Answer 15 · 2009-05-04 12:03:29

hi link99: AttachMate solutions Linkssbc,

here's a thought for you. when you find your data in the excel sheet (no matter which row or column), can you copy that into another excel sheet, say column a, row 2...and then fire the macro from extra.
that way the extra macro should always work

zach

link99sbc (TechnicalUser) · Answer 16 · 2009-05-04 12:06:56

Tried the code vzashin suggested.
[code]
        Dim obj as object
        Dim objWorkbook as object
        Set obj=CreateObject("Excel.Application")
        Set System = CreateObject("EXTRA.System")
        Set Sess = System.ActiveSession
        Set obj = Getobject("C:\My Documents\No Response 2007\nres2007.XLS")       'where xxxx is the location/name of the file on the C Drive
        set objWorkbook=obj.Worksheets("Sheet1")

        For i = 2 To obj.ActiveSheet.Rows.Count  'assumption data begins with row 2
        MyName = objWorkBook.Range("C" & i)      'assumption data begins in column C

        If Trim(MyName) = "" Then exit sub

        sess.screen.putstring MyName,4,18        'places the data from Excel in Extra
        'do stuff in extra...
        Sess0.Screen.Sendkeys ("<Enter>")
        next i                                    'i is the next row

end sub

This does a continuous loup down the column.  It doesn't stop.

vzachin (TechnicalUser) · Answer 17 · 2009-05-04 12:20:42

hi

CODE

If Trim(MyName) = "" Then exit sub

should have stopped the loop when it sees no value in column "C" and row i.

did you step through the code to see what happens when it reaches a cell in column C that is blank?

zach

link99sbc (TechnicalUser) · Answer 18 · 2009-05-04 12:47:51

The first blank cell is row 1584 ColC.  I am currently working on row 838 ColC. I need 1 cell at a time.

Here is what I'm using in Excel.  I'm trying to get the same result from Extra.
[code]
Sub clear()

    ActiveCell.Offset(0, -2) = 1
    ActiveCell.Offset(1, 0).Activate

    Dim Sessions As Object
    Dim System As Object
    Set System = CreateObject("EXTRA.System")
    Dim Sess0 As Object
    Set Sess0 = System.ActiveSession
    Set Field = System.ActiveSession.Screen.Area(4, 18, 4, 26)
    Field.Select
    Field.Delete
    Field.Value = ActiveCell
    SendKeys "%{TAB}+~"

End Sub
Sub bill()

    ActiveCell.Offset(0, -1) = 1
    ActiveCell.Offset(1, 0).Activate

    Dim Sessions As Object
    Dim System As Object
    Set System = CreateObject("EXTRA.System")
    Dim Sess0 As Object
    Set Sess0 = System.ActiveSession
    Set Field = System.ActiveSession.Screen.Area(4, 18, 4, 26)
    Field.Select
    Field.Delete
    Field.Value = ActiveCell
    SendKeys "%{TAB}+~"

End Sub

This code doesn't care what column I'm in. It's just putting the contents of the active cell in my screen area (one cell at a time) which is what I'm trying to do from Extra instead of going to Excel everytime.

vzachin (TechnicalUser) · Answer 19 · 2009-05-04 14:20:51

hi link99: AttachMate solutions Linkssbc,

guess i misunderstood the scope of your needs...

you're constantly activating the macro for each cell, right?

i'm used to grabbing data from a cell in excel, then querying my mainframe via extra with that cell value, waiting for a response from the mainframe and then perform an update of some sort; then go back to excel for the next cell and loop until there is no more data in excel

zach

link99sbc (TechnicalUser) · Answer 20 · 2009-05-04 14:33:43

"you're constantly activating the macro for each cell, right?"
Exactly. If I don't find anything on our database I have research other avenues before I get the next cell.

SkipVought (Programmer) · Answer 21 · 2009-05-04 14:47:11

Quote:
If I don't find anything on our database I have research other avenues before I get the next cell.

It seems to me that you're cutting WAYYYYY short the capabilities of Excel to be able to "automate" this research process.

Let's suppose that you had a macro in Excel, with a similar list.  You turn it on and it grabs the first item, gets the IMS screen, reads the appropriate data from the screen, writes it into an Excel sheet, depending on the data, maybe, it decides with database/table it needs to access and accesses that data and returns it to an Excel sheet, relating it to the data returned from the IMS screen, etc, etc until the list is exhausted. It might go to half a dozen or more different sources to gather the data, rather than YOU haveing to do all that work yourself.

I have data access programs that go one place to attempt to return data.  If nothing is returned then it goes another place.  No big deal.

I have data access programs that join 6 or 8 different tables in order to get the data I need.

I have nearly 100 data access functions that return ONE piece of data, given one or more parameters: greta for getting the On Hand balance for a Part, or the requirements for a Part between two dates.  And the list goes on and on.

There are all kind of ways to get data "automatically"

Skip,
^{Don't let the Diatribe...
talk you to death!}_{Just traded in my old subtlety...
for a NUANCE!}

link99sbc (TechnicalUser) · Answer 22 · 2009-05-04 15:51:08

In a perfect office this would be great but I have to research other agency databases which we have access to but for security issues require a login and password which we are not allowed to write down anywhere. I also have to research the internet so sometimes what I'm looking for is not always specific but general information. Just getting what I need from the active cell helps alot. I only use Excel as the list of Names OR Numbers to research. Once I get the name or number in my Extra Session and search our database I can determine if it requires further research. Sometimes it doesn't and I move on to the next one. So all I need is a code that will do the same in Extra as it does in excel. Just get the active cell, or get the active cell and enter a 1 in the appropriate column. Should be pretty simple for a wizard such as yourself.

SkipVought (Programmer) · Answer 23 · 2009-05-04 15:59:16

Quote:
So all I need is a code that will do the same in Extra as it does in excel

So you want to code in an application that has less flexibility and using code that has fewer capabiltites?

Is that what I understand you want to do?

And for what reason?

Skip,
^{Don't let the Diatribe...
talk you to death!}_{Just traded in my old subtlety...
for a NUANCE!}

link99sbc (TechnicalUser) · Answer 24 · 2009-05-04 16:21:28

So I don't have to go from Extra to Excel 1500 times to run the excel macro.

SkipVought (Programmer) · Answer 25 · 2009-05-04 16:29:31

Now I am confused!

If you are in Excel, then you do not have to go from ANYWHERE to ANYWHERE to run an Excel macro.

Skip,
^{Don't let the Diatribe...
talk you to death!}_{Just traded in my old subtlety...
for a NUANCE!}

vzachin (TechnicalUser) · Answer 26 · 2009-05-04 16:44:51

hi link99: AttachMate solutions Linkssbc,

i don't have Extra Extreme 9.0 SERVICE PAC 2, but did you check the Edit options in Extra?

your original statement was you couldn't get it to paste using 9.0 but using Enterprise it was ok

zach

link99sbc (TechnicalUser) · Answer 27 · 2009-05-04 17:12:00

zach
Yes I checked the Edit Options.  This is just an annomally that attachmate doesn't want to deal with. So I thought I could use a code to put the contents of the active cell in my field instead of copy & paste.
This code in Excel does exactly what I want it to do but I have to go from Extra to Excel 1500 times to run the macro. I was wanting to stay in Extra and run the macro that accomplishes the same thing without having to go to the Excel screen each time.
[code]
Sub clear()

    ActiveCell.Offset(0, -2) = 1
    ActiveCell.Offset(1, 0).Activate

    Dim Sessions As Object
    Dim System As Object
    Set System = CreateObject("EXTRA.System")
    Dim Sess0 As Object
    Set Sess0 = System.ActiveSession
    Set Field = System.ActiveSession.Screen.Area(4, 18, 4, 26)
    Field.Select
    Field.Delete
    Field.Value = ActiveCell
    SendKeys "%{TAB}+~"    'This Alt tabs back to Extra and sendkey <enter>


End Sub
Sub bill()

    ActiveCell.Offset(0, -1) = 1
    ActiveCell.Offset(1, 0).Activate

    Dim Sessions As Object
    Dim System As Object
    Set System = CreateObject("EXTRA.System")
    Dim Sess0 As Object
    Set Sess0 = System.ActiveSession
    Set Field = System.ActiveSession.Screen.Area(4, 18, 4, 26)
    Field.Select
    Field.Delete
    Field.Value = ActiveCell
    SendKeys "%{TAB}+~"   'This Alt tabs back to Extra and sendkey <enter>

End Sub

vzachin (TechnicalUser) · Answer 28 · 2009-05-04 22:03:25

link99: AttachMate solutions Linkssbc,

try changing

CODE

g_HostSettleTime = 300

to

CODE

g_HostSettleTime = 1000

your code is running too quick...

i tried this with extreme 8 and it took a few tries...

this works as well

CODE

sess0.screen.paste

what's really strange is that i cannot get it to work all the time...sometimes Sendkeys "%EC{Down}" doesn't always send the {Down}....

zach

link99sbc (TechnicalUser) · Answer 29 · 2009-05-04 22:32:20

I know the feeling. Sometimes {Enter} doesn't always work so i use +{Enter} and that seems to do the trick. I haven't had any problem with {down}. I will try what you suggest. I think I already tried that but I'll try it again.

I would still like to know how to code this in Extra so I don't have to use copy and paste. I think its a matter of getting the object correct and putting the value of the object which is the active cell at the specified location plus making the active cell offset 1 row and activate that cell. I got one to compile today but it's not putting anything in the field.

vzachin (TechnicalUser) · Answer 30 · 2009-05-05 11:33:29

link99: AttachMate solutions Linkssbc,

from what i see, with the existing coding you have in Extra with copy & paste, that does work. there's nothing wrong with it.
did you try changing the g_hostSettle time ?

link99sbc (TechnicalUser) · Answer 31 · 2009-05-05 12:53:01

OMG it works. Must be too much code for 300 milliseconds
600 works ok too. Still like to know how to code it like VB. Thanks

vzachin (TechnicalUser) · Answer 32 · 2009-05-05 13:08:18

you had the correct code all along...it was a lousy timing issue

glad it worked for you

let me know when YOU figure out how to code in VB

Contact US

Come Join Us!

Posting Guidelines

Having trouble with paste in Extra Extreme.

Having trouble with paste in Extra Extreme.

Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

CODE

CODE

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

Quote:

CODE

RE: Having trouble with paste in Extra Extreme.

CODE

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

CODE

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

Quote:

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

Quote:

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

CODE

CODE

CODE

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

RE: Having trouble with paste in Extra Extreme.

Red Flag This Post

Red Flag Submitted

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Join Tek-Tips® Today!

Join Tek-Tips^® Today!