Having trouble with paste in Extra Extreme.

[link99sbc]

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

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]

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]

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]

see if this helps

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"

Sendkeys "%EC{Down}"

command in Excel?

 

[link99sbc]

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]

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]

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:

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]

oops, that should be

objWorkBook.trim(Range("A2"))

 

[SkipVought]

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]

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]

thanks skip for keeping me honest.

link99sbc,
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]

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

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

 

[link99sbc]

(1) Yes
(2) Yes
(3) Yes
(4) No but I think the job will go faster
running from Extra. Sometimes I have
over 2000 on a list and it take months
to research the list. So you can see the
starting active cell is different each day.

This is one senario where I would work down ColA one
name at a time. Active Cell row 5 ColA. I would get the
name and make row 6 ColA active. The next day I may start on row 89 etc.
| ColA | ColB |ColC | ColD |ColE|ColF|
ROW2| NAME |ADDRESS|PHONE|CONTACT|DATA|DATA|
ROW3| NAME |ADDRESS|PHONE|CONTACT|DATA|DATA|
ROW4| NAME |ADDRESS|PHONE|CONTACT|DATA|DATA|
ROW5|[NAME]|ADDRESS|PHONE|CONTACT|DATA|DATA|
ROW6| NAME |ADDRESS|PHONE|CONTACT|DATA|DATA|

The other project is similar but I would be working down
ColC just getting account numbers and enter a 1 in cols A
or B. ColA for yes ColB for No. Active Cell row2 colC. Get the Acct# and make row3 ColC active for when I run the macro again - "get the next active cell". The rest of the Columns I don't need to deal with.
|ColA|ColB| ColC |ColD| ColE |ColF | ColG |ColH|ColI|
ROW2| 1 | | ACCT# |NAME|ADDRESS|PHONE|CONTACT|DATA|DATA|
ROW3| | 1 | ACCT# |NAME|ADDRESS|PHONE|CONTACT|DATA|DATA|
ROW2| | 1 |[ACCT#]|NAME|ADDRESS|PHONE|CONTACT|DATA|DATA|
ROW3| 1 | | ACCT# |NAME|ADDRESS|PHONE|CONTACT|DATA|DATA|

Hope this makes clear for you.

 

[SkipVought]

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]

hi link99sbc,

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]

Tried the code vzashin suggested.

        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]

hi

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]

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.

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]

hi link99sbc,

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