Solved

Copy from Attachmate and Paste to Excel

Posted on 2013-05-30
24
1,875 Views
Last Modified: 2013-06-19
hello, I'm im trying to create a copy paste feature from attachmate to excel. I have gotten so far as to open up the excel file but I'm unable to get it to paste the data. here is what I have so far.

'--------------------------------------------------------------------------------

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

                Sess0.Screen.Sendkeys("run mojzc.payhist<Enter>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
                Sess0.Screen.Sendkeys("'")
	Sess0.Screen.Paste
                Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
                Sess0.Screen.Sendkeys("<Right><Right><Right><Right><Right><Right><Right><Right><Right><Right>'<ENTER>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
 
' This section of code contains the recorded events 
        Dim SRow as integer
        Dim SCol as integer
        Dim ERow as integer
        Dim ECol as integer
        Dim WArea as object
        

'----------------------------------
'Change the StartRow, StartCol, EndRow and EndCol values as required to identify the area to be copied from the screen.
'
        StartRow = 3
        StartCol = 15
        EndRow = 20
        EndCol = 79
'------------------------ End Screen Area Definition

	Set WArea = Sess0.Screen.Select(StartRow,StartCol,EndRow,EndCol,,2) 
        
	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'               Sess0.Screen.SelectAll
                Sess0.Screen.Copy	
                Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	
 '----------------------------------
'Change the StartRow, StartCol, EndRow and EndCol values as required to identify the area to be copied from the screen.
'
        StartRow = 4
        StartCol = 15
        EndRow = 20
        EndCol = 79
'------------------------ End Screen Area Definiti
 
        Set WArea = Sess0.Screen.Select(StartRow,StartCol,EndRow,EndCol,,2) 

	Sess0.Screen.Sendkeys("<Pf8>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'               Sess0.Screen.SelectAll	
                WArea.Select
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.CopyAppend	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

	Sess0.Screen.Sendkeys("<Pf8>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'               Sess0.Screen.SelectAll	
                WArea.Select
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.CopyAppend	
 	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

	Sess0.Screen.Sendkeys("<Pf8>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'               Sess0.Screen.SelectAll	
                WArea.Select
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.CopyAppend	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

	Sess0.Screen.Sendkeys("<Pf8>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'               Sess0.Screen.SelectAll	
                WArea.Select
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.CopyAppend	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
 
 	Sess0.Screen.Sendkeys("<Pf8>")	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'               Sess0.Screen.SelectAll	
                WArea.Select
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.CopyAppend	
	Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
	Sess0.Screen.Sendkeys("<Pf3>")


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

	System.TimeoutValue = OldSystemTimeout

Open in new window

0
Comment
Question by:boehme13
  • 12
  • 9
  • 2
24 Comments
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39217093
This looks very similar to this question:

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_23743972.html#a22525224

Looks like after the putstring you may need something like this:

           Sess0.Screen.SendKeys "<ENTER>"
           Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Open in new window

0
 

Author Comment

by:boehme13
ID: 39218743
it doesnt paste the info from attachmate to excel. There is no error. It opens excel fine, just doest paste the clipboard info.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39218773
If you have put the data into the clipboard, use the Excel range object's .Paste method to place the data into the worksheet.
0
 

Author Comment

by:boehme13
ID: 39218843
here is what I wrote

       
        Dim xlApp As Object, xlSheet As Object
        Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        xlApp.Visible = True
        xlApp.Workbooks.Open FileName:="H:\PMDaily\Fetch.xlsx"
        Set xlSheet = xlApp.activesheet
        Set MyRange = xlApp.activesheet.Paste
                 
       
 excel opens and paste's the data, but I get an getting error no such property or method
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39219117
the paste method of the worksheet object does not return a Range... it will return boolean as to the success or failure of the paste.

I would change this line :
Set MyRange = xlApp.activesheet.Paste

Open in new window

To this line:
xlApp.activesheet.Paste

Open in new window

Unless you are wanting to check the return code of the paste... in which case you could assign it to a boolean like this:
Sub test()
Dim xlApp As Object, xlSheet As Object
Dim MyRange As Range
Dim rc As Boolean


        Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        xlApp.Visible = True
        xlApp.Workbooks.Add
        
        Set xlSheet = xlApp.activesheet
        rc = xlApp.activesheet.Paste

End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 39219493
rather than the blanket worksheet, you might specify a starting cell for the paste operation to begin.
0
 

Author Comment

by:boehme13
ID: 39219687
How would I specifcy areas to paste..

'Declare the Excel Object
       
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'       Sess0.Screen.SelectAll
        Sess0.Screen.Copy      
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

        Dim xlApp As Object, xlSheet As Object
        Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        xlApp.Visible = True
        xlApp.Workbooks.Open FileName:="H:\PMDaily\Fetch.xlsx"
        Set xlSheet= xlApp.activesheet
        xlApp.activesheet.Paste
      
       Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'       Sess0.Screen.SelectAll
        Sess0.Screen.Copy      
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
xlApp.activesheet.Paste(a1:a18)

       Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'       Sess0.Screen.SelectAll
        Sess0.Screen.Copy      
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

xlApp.activesheet.Paste(a19:a36)

       Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'       Sess0.Screen.SelectAll
        Sess0.Screen.Copy      
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

xlApp.activesheet.Paste(a37:a54)
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39219768
To specify an area to paste... you use the range object.

like this: (corrected)

xlApp.activesheet.Range("A37").PasteSpecial Paste:=xlPasteAll

Open in new window

0
 

Author Comment

by:boehme13
ID: 39219813
I tried that and I get the first part to paste in cells a1:a18 however I cannot get the script to paste directly to a19 and so on and so forth.. here is what I wrote so far:

'Declare the Excel Object
       
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'       Sess0.Screen.SelectAll
        Sess0.Screen.Copy      
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

        Dim xlApp As Object, xlSheet As Object
        Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        xlApp.Visible = True
        xlApp.Workbooks.Open FileName:="H:\PMDaily\Fetch.xlsx"
        Set xlSheet= xlApp.activesheet
        xlApp.activesheet.Paste
     
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'       Sess0.Screen.SelectAll
        Sess0.Screen.Copy      
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
      xlApp.activesheet.Range("A19").Paste

       Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'       Sess0.Screen.SelectAll
        Sess0.Screen.Copy      
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

      xlApp.activesheet.Range("A37").Paste

Do I need to re write this some how

        Dim xlApp As Object, xlSheet As Object
        Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        xlApp.Visible = True
        xlApp.Workbooks.Open FileName:="H:\PMDaily\Fetch.xlsx"
        Set xlSheet= xlApp.activesheet
        xlApp.activesheet.Paste


or will the single phrase work

xlApp.activesheet.Range("A19").Paste
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39219843
Try pastespecial on the range object like this:


'Declare the Excel Object
        
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'       Sess0.Screen.SelectAll
        Sess0.Screen.Copy      
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

        Dim xlApp As Object, xlSheet As Object
        Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        xlApp.Visible = True
        xlApp.Workbooks.Open FileName:="H:\PMDaily\Fetch.xlsx"
        Set xlSheet= xlApp.activesheet
        xlApp.activesheet.Paste
      
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'       Sess0.Screen.SelectAll
        Sess0.Screen.Copy      
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
      xlApp.activesheet.Range("A19").PasteSpecial Paste:=xlPasteAll

       Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'       Sess0.Screen.SelectAll
        Sess0.Screen.Copy      
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

      xlApp.activesheet.Range("A37").PasteSpecial Paste:=xlPasteAll

Open in new window

0
 

Author Comment

by:boehme13
ID: 39219858
VERY VERY VERY CLOSE... how do I make the paste special use text or unicode.. right now it is defaulting to bitmap and leaves an image.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 19

Expert Comment

by:Ken Butters
ID: 39221011
try substituting "xlPasteValues" instead of "xlPasteAll"
0
 

Author Comment

by:boehme13
ID: 39221769
still pastes an image. here is what I wrote.

               
        Dim xlApp As Object, xlSheet As Object
        Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        xlApp.Visible = True
        xlApp.Workbooks.Open FileName:="H:\PMDaily\Fetch.xlsx"
        Set xlSheet= xlApp.activesheet
        xlApp.activesheet.Paste
      

      Sess0.Screen.Sendkeys("<Pf8>")      
      Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'       Sess0.Screen.SelectAll      
        WArea.Select
      Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
      Sess0.Screen.Copy
      Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

        xlApp.activesheet.Range("A19").PasteSpecial Paste:=xlpastevalues
               
      Sess0.Screen.Sendkeys("<Pf8>")      
      Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'       Sess0.Screen.SelectAll      
        WArea.Select
      Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
      Sess0.Screen.Copy      
       Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

        xlApp.activesheet.Range("A36").PasteSpecial Paste:=xlpastevalues
       
        end sub
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39222223
Sorry.. just trying different things, makes it kind of hard since I don't have Attachmate... and this is sort of a guess...

This next attempt uses a different format for the paste special, but it is only available for pasting into a sheet, not a range.  The location for the paste is going to be wherever the active cell is...so changing the command to a two part process...

1) set the active Cell
2) Perform the paste

Here is your example with that code change... let's see if that works.
    Dim xlApp As Object, xlSheet As Object
    Set xlApp = CreateObject("excel.application")
    xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
    xlApp.Visible = True
    xlApp.Workbooks.Open FileName:="H:\PMDaily\Fetch.xlsx"
    Set xlSheet = xlApp.activesheet
    xlApp.activesheet.Paste
    
    
    Sess0.Screen.SendKeys ("<Pf8>")
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
'   Sess0.Screen.SelectAll
    WArea.Select
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    Sess0.Screen.Copy
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    
    xlApp.activesheet.Range("A19").Activate
    xlApp.activesheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
    
    Sess0.Screen.SendKeys ("<Pf8>")
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
'   Sess0.Screen.SelectAll
    WArea.Select
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    Sess0.Screen.Copy
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    
    xlApp.activesheet.Range("A36").Activate
    xlApp.activesheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
        
End Sub

Open in new window

0
 

Author Comment

by:boehme13
ID: 39222283
genius.... now is there a way to change it to csv.. Would I just add csv in text.?
0
 

Author Comment

by:boehme13
ID: 39222623
is there also a way to get it pick a specific sheet to paste too? here is an example I wrote


    xlApp.activesheet.sheet1.Range("A3").Activate
    xlApp.activesheet.sheet1.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False


    xlApp.activesheet.sheet2.Range("A3").Activate
    xlApp.activesheet.sheet2.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39222852
There are several different ways to reference a specific sheet.

if you do this:

go into excel

have 2 sheets and rename the first one from sheet1 to "test"
leave the second one named "Sheet2"

then

Open the Visual Basic Editor
Insert a module
paste the following code:
go to view menu and select Imediate window (so you can see results of debug.print)
and run this code...
Option Explicit

Sub test()
    
    Dim wb As Workbook
    Dim wks As Worksheet
    
    
    Set wb = Application.ThisWorkbook
    
    For Each wb In Application.Workbooks
        
        For Each wks In wb.Worksheets
            
            Debug.Print wks.Name
                        
        Next
    Next
    
    Debug.Print "-----------"
    
    Set wks = Application.ThisWorkbook.ActiveSheet
    Debug.Print "1)"; wks.Name
    
    Set wks = Application.ActiveSheet
    Debug.Print "2)"; wks.Name
    
    Set wks = Application.ThisWorkbook.Worksheets(1)
    Debug.Print "3)"; wks.Name
    
    Set wks = Application.ThisWorkbook.Worksheets("test")
    Debug.Print "4)"; wks.Name
    
    Set wks = ThisWorkbook.ActiveSheet
    Debug.Print "5)"; wks.Name
    
    Set wks = ThisWorkbook.Worksheets(1)
    Debug.Print "6)"; wks.Name
    
    Set wks = ThisWorkbook.Worksheets("test")
    Debug.Print "7)"; wks.Name
    
    Set wks = ActiveSheet
    Debug.Print "8)"; wks.Name
    
    Set wks = Worksheets(1)
    Debug.Print "9)"; wks.Name
    
    Set wks = Worksheets("test")
    Set wks = Sheet1
    
        

End Sub

Open in new window


Then you should see this this in the immediate window:
Note that numbers 1 through 9 all reference the same sheet in different ways.

The first two lines are a result of looping through all the sheets in the workbook.

test
Sheet2
-----------
1)test
2)test
3)test
4)test
5)test
6)test
7)test
8)test
9)test

so you could use (as some examples) :

sheet2.Range("A3").Activate

or

xlApp.activesheet.Range("A3").Activate

or

xlApp.worksheets("Sheet2").Range("A3").activate

etc.... the more "fully qualified" the path you use, the less prone to error the final result will generally be.
0
 

Author Comment

by:boehme13
ID: 39223099
Where would I use these statements? I tried using them in place of this (xlApp.activesheet.Paste)

sheet2.Range("A3").Activate
or
xlApp.activesheet.Range("A3").Activate
or
xlApp.worksheets("Sheet2").Range("A3").activate


However it would run and open excel and stop and error on one of the statements. I'm wondering do I need to adjust the Set xlSheet to something different. i tried different variations of like

    Set xlSheet = xlApp.activesheet
    xlApp.activesheet.Range("A3").Activate


    Set xlSheet = sheet2
    sheet2.Range("A3").Activate

    Set xlSheet = xlApp.worksheets("Sheet2")
    xlApp.worksheets("Sheet2").Range("A3").activate
0
 
LVL 19

Accepted Solution

by:
Ken Butters earned 500 total points
ID: 39223149
I was just trying to answer the question:
is there also a way to get it pick a specific sheet to paste too?
There are multiple ways to reference a sheet.  Regardless of how you reference the sheet, that sheet that is to receive the paste command as we have it defined, needs to be the active sheet.

This is one way to accomplish that... where you would need to substitute the name of your target sheet for "Your Worksheet Name Here"
xlApp.worksheets("Your Worksheet Name Here").Range("A36").Activate
xlApp.activesheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

Open in new window

the first line makes your sheet active... (showed you multiple ways to do that)
the second line pastes to the active sheet
0
 

Author Comment

by:boehme13
ID: 39223534
I appreciate your guidance and help.

I found a way to get the sheet2 tabe to activate,



xlApp.worksheets("Sheet2").Activate


however it won't allow for me specify the range like in the examples you were kind enough to provide.



Is there another way to write the fucntion to specify the range?
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39223735
if you can't get it to work all in one line... I'd try this:
xlApp.worksheets("Sheet2").Activate
xlApp.activeSheet.Range("A36").Activate
xlApp.activesheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

Open in new window

0
 

Author Closing Comment

by:boehme13
ID: 39237461
It was fantastic service!! Now all I need to do is figure out how to make it run with vb.!!
0
 

Author Comment

by:boehme13
ID: 39259420
Is there a way to paste the data to a spreadsheet that is already open. Right now it opens a new one every time. Just wondering if there is a way to keep reusing the existing spreadsheet
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now