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!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

I want to copy multiple cells from excel and paste it to attachmate extra 1

Status
Not open for further replies.

rajesha24

Technical User
Aug 13, 2012
26
US
I want to copy Col A and Col B and then paste it in extra col A @ 03, 22 and Col b @ 12,10
Col A Col B
12855121 16OC97
12855269 20OC05
12855287 19DE94
12855445 27MA08
12855449 04DE02
Pls advise
 
I want to copy Col A and Col B from excel and then paste it in extra col A @ 03, 22 and Col b @ 12,10. and loop it till the empty cell
Col A Col B
12855121 16OC97
12855269 20OC05
12855287 19DE94
12855445 27MA08
12855449 04DE02
 
hi,

If you cannot ACTUALLY copy 'n' paste into your screen, then such a method will not work via code.

So have you tried to MANUALLY do what you want your code to accomplish?

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

I can manually copy and paste but there around some 200 rows if I do manually it takes lots of time , So I thought I could it via a code
 

So you're saying that when you COPY 2 columns, then on the screen , you can paste ONE TIME PER SCREEN PAGE.

So if your screen takes 5 rows of data, you can copy 5 rows of 2 columns from Excel and paste that into the screen page, then advance to another screen page and repeat the process.

Does that summarize how you manually proceed?

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

Yes your right thats what I want to do
 
Well what gave me pause is you statement, "I want to copy Col A and Col B from excel and then paste it in extra col A @ 03, 22 [highlight]and Col b @ 12,10[/highlight]..."
[highlight]That[/highlight] sounded to me like some separate operation.

So have you generated any code at all? Please post what you have so far and where you need help.

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

I have not generated any code , below is just an example

Dim Row As Long
With xlApp.ActiveSheet
Set MyRange = .Range("B3:B14").Resize(xlApp.CountA(.Range("B3:B14")))
End With
For Row = 1 To MyRange.Rows.Count
Sess0.Screen.PutString MyRange.Rows(Row).Value, 24, 6
Sess0.Screen.SendKeys "<ENTER>"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Next Row

End Sub

In FOr statement I am able to copy only Cell (1, 1) but I want to copy also cell (2, 1) and loop it until the blank row
 
I mean I want to copy cell A1 and B1 , and then loop it till the blank row.
 


You have not told me how many rows you need to COPY for a Screen. I can ASSUME by your posted code, that you have 12 rows on your screen to fill. Is that correct?

BTW, your code indicates that you do not COPY ANYTHING!!! Are we on the same page? Your code assigns ROW BY ROW, rather than actually using the COPY Method in Excel to load the Clipboard, followed by the PASTE Method in the screen emulator to place the data from the Clipboard, into the sheet in ONE OPERATION.

What is the KEY that you use to indicate to the emulator that you want the data that you pasted ADDed to the database? Is that what Sess0.Screen.SendKeys "<ENTER>" does?

You also have not posted the code that assigns the OBJECTS that you reference???




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

I am not good in programming I have not written any code for this

I just need a code that will copy Cell A1 from excel and paste it to extra at 3, 22 and cell B1 from Excel and paste it to Extra at 12, 10.

( Cell A1 contains the part number and cell B1 contains the date for that p/n
)

the code should loop till there is a blank space
 
I asked several questions that i need answers from you before i can produce a solution, unless you just want a tip or two so you can code it yourself.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
so here is the code that I have written but it copies only column A but column B is not copied, content of column B should be pasted at 12, 10 in Extra

' 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 = 3000 ' 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)
'--------------------------------------------------------------------------------
'Declare the Excel Object
Dim xlApp As Object, xlSheet As Object, MyRange As Object
Set xlApp = CreateObject("excel.application")
xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
xlApp.Visible = False
xlApp.Workbooks.Open FileName:="C:\Efiles\Excel\extract.xlsx"
Set xlSheet = xlApp.activesheet
Set MyRange = xlApp.activesheet.Range("A:B")
Dim Row As Long
With xlApp.ActiveSheet
Set MyRange = .Range("A2:A65536").Resize(xlApp.CountA(.Range("A2:A65536")))
End With
'lRow = .Cells(1, 1).CurrentRegion.Rows.Count + 1
For Row = 1 To MyRange.Rows.Count
Sess0.Screen.PutString MyRange.Rows(Row).Value, 3, 22
Sess0.Screen.SendKeys "<ENTER>"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Next Row
msgbox "macrodone"
End Sub
 
BTW, this process has absolutely nothing to do with copying anything!!! Please refreain from using the term COPY, unless you actually employ the COPY METHOD in your process! This does NOT use the COPY METHOD.
Code:
For Row = 1 To MyRange.Rows.Count 
  Sess0.Screen.PutString xlSheet.Cells(Row, "A").Value, 3, 22 
  Sess0.Screen.PutString xlSheet.Cells(Row, "B").Value, 12, 10 
  Sess0.Screen.SendKeys "<ENTER>" 
  Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Next Row

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
the above code actually gets the data from Excel and then puts it into the Extra, thats why I say copy
 
Your process ASSIGNS values from Excel to Extra. COPY means something entirely different.

When you use the COPY METHOD in either system, the data is applied by way of a PASTE METHOD.

Words are very important, because they convey specific information.

In Extra Help
Copy Method

Applies To Objects

Area, Screen

Description

Copies the current selection to the Clipboard.

Syntax

object.Copy

Element Description
object The Area or Screen object.
Comments

To copy data from an Area or Screen object, you must first select the object with the Select method. (For the Screen object, you can also use the SelectAll method.)

Copyright 1996 - 1999, Attachmate Corporation. All rights reserved.
In Excel HELP
Copy Method
Copy method as it applies to the ChartArea object.

Copies a picture of the point or series to the Clipboard.

expression.Copy

expression Required. An expression that returns one of the above objects.
Copy method as it applies to the Range object.

Copies the Range to the specified range or to the Clipboard.

expression.Copy(Destination)

expression Required. An expression that returns one of the above objects. Destination Optional Variant. Specifies the new range to which the specified range will be copied. If this argument is omitted, Microsoft Graph copies the range to the Clipboard.
Example
This example copies the formulas in cells A1:D4 on the datasheet into cells E5:H8.

Set mySheet = myChart.Application.DataSheet
mySheet.Range("A1:D4").Copy _
Destination:= mySheet.Range("E5")



Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
I am not good in programing [sad]
now for the below code can you tell me how can I get the data from column A and Column B i,e A1B1 and loop it till the blank row, In the below code I am able to do only for column A

' 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 = 3000 ' 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)
'--------------------------------------------------------------------------------
'Declare the Excel Object
Dim xlApp As Object, xlSheet As Object, MyRange As Object
Set xlApp = CreateObject("excel.application")
xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
xlApp.Visible = False
xlApp.Workbooks.Open FileName:="C:\Efiles\Excel\extract.xlsx"
Set xlSheet = xlApp.activesheet
Set MyRange = xlApp.activesheet.Range("A:B")
Dim Row As Long
With xlApp.ActiveSheet
Set MyRange = .Range("A2:A65536").Resize(xlApp.CountA(.Range("A2:A65536")))
End With
'lRow = .Cells(1, 1).CurrentRegion.Rows.Count + 1
For Row = 1 To MyRange.Rows.Count
Sess0.Screen.PutString MyRange.Rows(Row).Value, 3, 22
Sess0.Screen.SendKeys "<ENTER>"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Next Row
msgbox "macrodone"
End Sub



 

Did you not look at and TRY the code that I previously posted, that addresses this very issue?

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

it works thank you very much, you saved lot of time for me

[2thumbsup]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top