Copy Cell in Excel and Paste in Attachmate 2

[vzdog]

Hello I recieved no reply to my first thread so that forced me to work it out, which I have done. I have an Attachmate session that I want to run a macro that opens an Excel file and then copy the 1st Cell (A2) and then paste it in to the Attachmate session. There could and will be multiple cells ie A1 through A2000 etc so i want it to be able to loop until done.

Here is the code I have so far:

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
Set xlApp = CreateObject("excel.application")
xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
xlApp.Visible = True
xlApp.Workbooks.Open FileName:="D:\PMDaily\Fetch.xls"
Set xlSheet = xlApp.activesheet
Set MyRange = xlApp.activesheet.Range("A2").Select

Up to this point it works great, then stops. Object Error Line54 (Set MyRange = xlApp.activesheet.Range("A2").Select)

I believe that after getting it to copy the cell I would then use:

Selection.Copy
Sess0.Screen.paste

Then loop...

I have not been able to figure out what I am missing. I sure would appreciate the help!.Thanks!

 

[SkipVought]

Use the Area property or PutString method to assign a string to the screen.

Sess0.Screen.PutString xlApp.activesheet.Range("A2").Value,row,col

or

Sess0.Screen.Area(row, ColStart, row, ColEnd).value = xlApp.activesheet.Range("A2").Value

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

 

[vzdog]

Skip, Thanks for the reply. Will have to give it a run tomorrow @ work and see if it flies.

 

[vzdog]

Thanks Skip. Will give it a run @ work tomorrow and see how it goes.

 

[hatman88]

You never initialized "MyRange" nor "xlSheet" either. Not sure if it would still work or not...

 

[vzdog]

hatman88, I am learning so if there was something I should have done differently please point it out. I will be duplicating this quite often for other applications so I would like for it to be correct.

This does work with the addition of Skip's Putstring code, now I need to loop through until the last cell is Empty on my Excel sheet.

Do While Loop was what I have tried but I believe, going back to what hatman88 pointed out, it doesnt work because I am not referencing it correctly?

 

[vzdog]

I desperately need some help on this. I have the code written in Attachmate that will open my Excel file, go to the Cell A2 and copy that cell. It will then paste it in to the attachmate session and enter. Now I need to know how to get it to loop through the range of Cells A2 through last populated Cell and stop on the empty. Once it hits the last cell it should close the Activeworkbook and exit the Attachmate session as well. And of course needs to include the On Error Resume Next. Can you look at my code and point me in the right direction?

'Declare the Excel Object
Dim xlApp As Object
Set xlApp = CreateObject("excel.application")
xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
xlApp.Visible = True
xlApp.Workbooks.Open FileName:="D:\PMDaily\Fetch.xls"
Set MyRange = xlApp.activesheet.Range("A2").Select
Sess0.Screen.PutString xlApp.activesheet.Range ("A2").Value,row,col

 

[SkipVought]

BTW,

The word "COPY" has a very specific meaning. What you are doing is NOT a copy. You are ASSIGNING a value in Excel to a place in your emulator.

Also, you do not need the SELECT method in Set MyRange = xlApp.activesheet.Range("A2").Select.

I would also recommend NOT using ActiveSheet, as the ActiveSheet TOMORROW, may not be the sheet that you expect.

   Dim wb as excel.workbook, r as excel.range
   set wb = xlApp.Workbooks.Open FileName:="D:\PMDaily\Fetch.xls"
   Set xlSheet = xlApp.wb.Worksheets("TheSheetNameThatYouWant")

   With xlSheet 
      Set MyRange = .Range(.[A2], .[A2].End(xldown))
      for each r in myrange
        Sess0.Screen.PutString r.Value,row,col
        row = row + 1  
      next
   End With

...as an example.

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

 

[vzdog]

Skip ....thanks for the follow-up. It appears I went way off on this somewhere. I changed my code to the last example you provided and tried to compile and it had so many errors I want sure what to do. So...I started over....again.

'Declare the Excel Object
        Dim xlApp As Object
        Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        xlApp.Visible = True
        xlApp.Workbooks.Open FileName:="D:\PMDaily\Fetch.xls"
        Set MyRange = xlApp.activesheet.Range("A2")
        Sess0.Screen.PutString xlApp.activesheet.Range ("A2").Value,row,col

Then tried to add this

Do While xlApp.activesheet.Range("A:A").Value <>""
Sess0.Screen.PutString xlApp.activesheet.Range ("A:A").Value,row,col

Loop
   
        
End Sub

How did that workout? Not Too Good.

So basically I can still "copy" the A2 from Excel and "paste" it into my Attachmate session, but then it stops. I even tried "playing with your code and still nothing. I got close it appeared once, all of the code would compile except for getting a Syntax error on the line

for each r in myrange

suggestions? Is there something I am not putting together correctly?

 

[SkipVought]

Sorry, I keep forgetting that Extra Basic is not as robust as VBA and has some different syntax. Try this...

   Dim wb as excel.workbook, [b]r as LONG, FirstRow as long, LastRow as long[/b]
   set wb = xlApp.Workbooks.Open FileName:="D:\PMDaily\Fetch.xls"
   Set xlSheet = xlApp.wb.Worksheets("TheSheetNameThatYouWant")

   With xlSheet [b]
      with .Range(.[A2], .[A2].End(xldown))
        FirstRow = .row
        Last Row = .rows.count + FirstRow - 1
      end with
      for r = FirstRow to LastRow[/b]
        Sess0.Screen.PutString [b].Cells(r, 1).Value[/b],row,col
        row = row + 1  
      next
   End With

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

 

[vzdog]

It didnt like that either...

 

[vzachin]

vzdog

here's one way:
this code will go down column "a" beginning with cell "a2" in excel , paste the excel value in attachmate as defined by row & column; [you need to add coding here to do your stuff in attachmate} and then continue down the excel column until it reaches a blank value.
crude method but works

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

        sFile = "place your file path here"
        
        Dim obj as object
        Dim objWorkbook as object
        Set obj=CreateObject("Excel.Application")
        obj.visible=TRUE
        obj.Workbooks.Open sFile
        set objWorkbook=obj.Worksheets("test")
'---------------------------------------------------------------------------  

obj.WorkSheets("test").Select
For i = 2 To obj.ActiveSheet.Rows.Count
MyExcelData = obj.Range("A" & i)

If MyExcelData = "" Then
Exit For
End If
Sess0.Screen.PutString MyExcelData,1,1
[blue]do your stuff in attachmate over here. iuse something like this :
Sess0.Screen.SendKeys ("<right><PF1>")
Do: Loop Until Sess0.Screen.Row = 3 And Sess0.Screen.Col = 20
[/blue]

next
End Sub

 

[vzdog]

First..Thanks to all of you for your responses. I have yet however seen or been able to modify any of these in to a working solution. So I thought i would try to clarify what i am trying to do. Please keep in mind that I am a novice at best and do not know the jargin, so a clear explination would be greatly appreciated, especially if I am supposed to make changes with the code somewhere.

Scenerio:
I am running this macro in Attachmate EXTRA! X-treme
I have an Excel spreadsheet named D:\PMDaily\Fetch.xls.
The sheet name is the default Sheet 1.
my data is all in Column 1 begining at A2.
The amount of data can very as the Excel sheet is updated daily from an Access Query.
so it could run from A2 through A 65000 etc.
Typical contents of a cell would look something like this:
FETCH-HISTORICAL-QI 00/HCGS/123456/^^^/AR^^/^^^ 09/18/2008
The macro opens the Excel sheet and "copies" the 1st Cell of data (A2) then
"pastes" it in to the Attachemate session @ 24, 6 (This line position has no bearing as it defaults everytime in my Attachmate Session).
The code below works.

' 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, Row As Long
        Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        xlApp.Visible = True
        xlApp.Workbooks.Open FileName:="D:\PMDaily\Fetch.xls"
        Set xlSheet = xlApp.activesheet
        Set MyRange = xlApp.activesheet.Range("A2")
        Sess0.Screen.PutString xlApp.activesheet.Range ("A2").Value,row,col
        Sess0.Screen.Sendkeys("<Enter>")

end sub

What I can not get to work is to go back to the same Excel spreadsheet and pick up A3 and " paste" it.
Yes - Loop. until the macro comes to any "empty cell". At that point it should stop.
I have tried several variations and none of them work no matter.

Suggestions? And please clarify thank you!

 

[SkipVought]

What values have you assigned to row, col in...

Sess0.Screen.PutString xlApp.activesheet.Range ("A2").Value,[b]row,col[/b]

Did you ever look at HELP on PutString? and do you uunderstand how to use?

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

 

[vzdog]

Skip. Thanks for the reply. That is the problem. No I do not understand. I guess the main reason is because what I have cobbled together actually does what I want it to do up to the point of getting the A3 etc and looping.

If row, col refers to the Attachmate Session that is confusing to me because it never changes (by Default of the
Attachmate session i am using ...the curser always comes back to 024,006 so it didnt make sense that i needed to do anything with it..

 

[SkipVought]

PutString does NOT control the cursor.

It Puts a String on the screen in the row, col that YOU specify. YOU have to do something to control row and col in the PutString method. Which is why I had row = row + 1 in one of my examples.

Before the loop starts, YOU must intialize row & col with in initial value and then control those values, if you want them to change within the for...next loop.


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

 

[vzachin]

vzdog,
my code does exactly what you want. what didn't work?
copy my entire code and
in my code,
change this:

sFile = "place your file path here"

to

SFile = "D:\PMDaily\Fetch.xls"

and

set objWorkbook=obj.Worksheets("test")

to

set objWorkbook=obj.Worksheets("sheet1")

and this:

Sess0.Screen.PutString MyExcelData,1,1

to
:

Sess0.Screen.PutString MyExcelData,24,6

this assumes you want the data in row 24, column 6

place this after the above statement with the row & col

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

 

[vzdog]

VZACHIN...copied the code and made changes as follows:

I ran it and it did not loop. I stepped through it as well and it is not picking up the loop function...

Skip...I am in the process of going make through to try your version.

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

        SFile = "D:\PMDaily\Fetch.xls"
        
        Dim obj as object
        Dim objWorkbook as object
        Set obj=CreateObject("Excel.Application")
        obj.visible=TRUE
        obj.Workbooks.Open sFile
        set objWorkbook=obj.Worksheets("sheet1")
'---------------------------------------------------------------------------  

obj.Worksheets("sheet1").Select
For i = 2 To obj.ActiveSheet.Rows.Count
MyExcelData = obj.Range("A" & i)

If MyExcelData = "" Then
Exit For
End If
Sess0.Screen.PutString MyExcelData,24,6
Sess0.Screen.Sendkeys("<Enter>")
Do: Loop Until Sess0.Screen.Row = 3 And Sess0.Screen.Col = 20


next
End Sub

 

[vzachin]

vzdog

i suspect the problem is here

Do: Loop Until Sess0.Screen.Row = 3 And Sess0.Screen.Col = 20

after you hit the enter key

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

is the cursor really at row 3 , col 20? if it's not there, then this code will fail.

i just stepped through the code and it does work.

this has to loop because of the following statement:

For i = 2 To obj.ActiveSheet.Rows.Count
'code over here
next

this will loop until there is a blank in column A


wish there was a way i can communicate with you offline

 

[vzdog]

All thank you for your suggestions.
I have found the problem and corrected the code as follows:

' 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 = True
        xlApp.Workbooks.Open FileName:="D:\PMDaily\Fetch.xls"
        Set xlSheet = xlApp.activesheet
        Set MyRange = xlApp.activesheet.Range("A:A")
  
 
Dim Row As Long
        With xlApp.ActiveSheet
           Set MyRange = .Range("A2:A65536").Resize(xlApp.CountA(.Range("A2:A65536")))
        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

Thank you all for your help and patience with a newbie!!!!!!!