Having trouble with paste in Extra Extreme.
Having trouble with paste in Extra Extreme.
(OP)
' 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.
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.
RE: Having trouble with paste in Extra Extreme.
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
RE: Having trouble with paste in Extra Extreme.
Put what? I need it to be able to go
to the next active cell and copy when I
run the macro again.
RE: Having trouble with paste in Extra Extreme.
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.
RE: Having trouble with paste in Extra Extreme.
CODE
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
RE: Having trouble with paste in Extra Extreme.
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.
RE: Having trouble with paste in Extra Extreme.
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.
RE: Having trouble with paste in Extra Extreme.
if the data always begins in a certain row but different column A or C, it's fairly easy to check that. eg:
CODE
MyName = objWorkBook.Range("A" & i)
else
If trim(objWorkBook.Range("C2")) <> "" Then
MyName = objWorkBook.Range("C" & i)
End If
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.
RE: Having trouble with paste in Extra Extreme.
CODE
RE: Having trouble with paste in Extra Extreme.
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...![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
Just traded in my old subtlety...
talk you to death!
for a NUANCE!
RE: Having trouble with paste in Extra Extreme.
RE: Having trouble with paste in Extra Extreme.
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?
RE: Having trouble with paste in Extra Extreme.
[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...![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
Just traded in my old subtlety...
talk you to death!
for a NUANCE!
RE: Having trouble with paste in Extra Extreme.
(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.
RE: Having trouble with paste in Extra Extreme.
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...![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
Just traded in my old subtlety...
talk you to death!
for a NUANCE!
RE: Having trouble with paste in Extra Extreme.
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
RE: Having trouble with paste in Extra Extreme.
[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.
RE: Having trouble with paste in Extra Extreme.
CODE
did you step through the code to see what happens when it reaches a cell in column C that is blank?
zach
RE: Having trouble with paste in Extra Extreme.
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.
RE: Having trouble with paste in Extra Extreme.
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
RE: Having trouble with paste in Extra Extreme.
Exactly. If I don't find anything on our database I have research other avenues before I get the next cell.
RE: Having trouble with paste in Extra Extreme.
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...![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
Just traded in my old subtlety...
talk you to death!
for a NUANCE!
RE: Having trouble with paste in Extra Extreme.
RE: Having trouble with paste in Extra Extreme.
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...![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
Just traded in my old subtlety...
talk you to death!
for a NUANCE!
RE: Having trouble with paste in Extra Extreme.
RE: Having trouble with paste in Extra Extreme.
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...![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
Just traded in my old subtlety...
talk you to death!
for a NUANCE!
RE: Having trouble with paste in Extra Extreme.
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
RE: Having trouble with paste in Extra Extreme.
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
RE: Having trouble with paste in Extra Extreme.
try changing
CODE
CODE
your code is running too quick...
i tried this with extreme 8 and it took a few tries...
this works as well
CODE
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
RE: Having trouble with paste in Extra Extreme.
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.
RE: Having trouble with paste in Extra Extreme.
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 ?
RE: Having trouble with paste in Extra Extreme.
600 works ok too. Still like to know how to code it like VB. Thanks
RE: Having trouble with paste in Extra Extreme.
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