×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Help Getting Excel Data Into Attachmate session/emulator

Help Getting Excel Data Into Attachmate session/emulator

Help Getting Excel Data Into Attachmate session/emulator

(OP)
Hi,

Anyone out there who could help me with creating a macro that opens an Excel file and then copy the data within a record that resides on row 1, 2, 3, etc where the data are in A2 through H2 (See first img as an example)and then paste it in to the Attachmate session ( I am providing an image of the attachmate emulator/session i am using) to illustrate. There could and will be multiple cells ie A1 through A200 or A2000 where the date for each record resides as a row. So basically I want it to be able to loop until all the fields within a record is pasted into the attachmate session and then it'll loop to the next record, etc, etc. I am also including what i am doing so far (got it from reading other post here on the site) to no avail. Please help however you can. Thanks.

Images links in mediafire:

http://www.mediafire.com/?1kmig1u8mouly4b

http://www.mediafire.com/?ow9j42azg2jax24

RE: Help Getting Excel Data Into Attachmate session/emulator

(OP)
Hi again,

This is what i got so far:
****************************************************************

'--------------------------------------------------------------------------------
' This macro was created by the Macro Recorder.
' Session Document: "C:\Program Files\Attachmate\E!E2K\Sessions\Nts.edp"
' Date: Tuesday, January 17, 2012 11:47:53
' User: 067
'--------------------------------------------------------------------------------

' 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)
 ----------------------------------------------------------------------------------
 '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:="\\DTCNAS-MNMI004\C_MTGCMC_Users\067\My Documents\411_macro_spreadsht.xlsx"
        Set xlSheet = xlApp.activesheet
        Set MyRange = xlApp.activesheet.Range("B3:B14")
    


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

***************************************************************

RE: Help Getting Excel Data Into Attachmate session/emulator


hi,

This loads values from your first sheet column B into the screen...

CODE


 'Declare the Excel Object
    Dim xlApp As Object, MyRange As Object
    
    Set xlApp = CreateObject("excel.application")
    
    xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
    xlApp.Visible = True
    
    With xlApp.Workbooks.Open(Filename:="\\DTCNAS-MNMI004\C_MTGCMC_Users\067\My Documents\411_macro_spreadsht.xlsx")
        With .Sheets(1)     'this refers UP to the workbook
            For Each MyRange In .Range("B3:B14")    'this refers UP to the worksheet
            
                Sess0.Screen.PutString MyRange.Value, 24, 6
                
                Sess0.Screen.SendKeys "<ENTER>"
                Sess0.Screen.moverelative 1, 1              'move cursor off rest point
                Do Until Sess0.Screen.waitforcursor(r, c)   'r,c is the row,col where the cursor comes to rest after ENTER
                    DoEvents
                Loop
            Next
        End With
        
        .Close
    End With
    xlApp.Quit
    Set xlApp = Nothing


Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Help Getting Excel Data Into Attachmate session/emulator

(OP)
Skip,

Thank you for your input, as i will certainly try this tomorrow. As far as the code:

 Sess0.Screen.PutString MyRange.Value, 24, 6

Is this valid for my use? Because i got this from another post, it did open my test excel file but no input/scraping was done. Are these referencing to something within excel? Attachmate? Also ff it helps you to understand more of my predicament, I included an image of what type of data i typically want to get into attachment with this link.

<<http://www.mediafire.com/i/?40a051k0qixjnsj>>

Again, thanks Skip for your help. Looking forward to more insight from you and the rest.
  

RE: Help Getting Excel Data Into Attachmate session/emulator




This is reading data from sheet 1, column B to just ONE row,col in the screen: 24,6.

I do not have my help file available.  Use Extra VB Help.

How do your 8 columns map to the screen?  BTW, I do ALL my VB programming in Excel VBA, 'cuz I'd rather drive a Lamborghini than a Yugo.

I would build a table in Excel on a separate sheet and use NAMED RANGES...

xlField  exROW  exCOL
1        
2
3
4
5
6
7
8

BTWm there does not seem like there is much correlation between your 8 columns of Excel data and the scads of fields on the General Review Data screen???
 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Help Getting Excel Data Into Attachmate session/emulator

(OP)
Skip,

My concern is how I should go about in mapping the data to attachmate? The way our attachmate work and how it relates to the data in the columns are as followed:
1. I would enter a lookup id (in my case, the loan number from the spreadsheet) into the loan number field of attachmate in the general review data screen.
2. Once I do that, in my company, the analyst would press Enter on the keyboard and some fields within the general review data screen would be populated whiles others are not. The fields that are not are the ones we would repeatedly copy and paste using the data from each record where there is 8 to 10 column of data for each record.
3. After that's done then there is some step to update the data using the F5 key (on the legion).


So my questions are:
How can I scrape/copy this type of data into attachmate? Because the correlation is that I will be pasting the data in the columns of the spreadsheet into the empty fiields within the general review data screen.
Are you suggesting I use named ranges instead of aligning my data for each record into Skip,

My concern is how I should go about in mapping the data to attachmate? The way our attachmate work and how it relates to the data in the columns are as followed:
1. I would enter a lookup id (in my case, the loan number from the spreadsheet) into the loan number field of attachmate in the general review data screen.
2. Once I do that, in my company, the analyst would press Enter on the keyboard and some fields within the general review data screen would be populated whiles others are not. The fields that are not are the ones we would repeatedly copy and paste using the data from each record where there is 8 to 10 column of data for each record.
3. After that's done then there is some step to update the data using the F5 key (on the legion).

So my questions are:
How can I scrape/copy this type of data into attachmate? Because the correlation is that I will be pasting the data in the columns of the spreadsheet into the empty fiields within the general review data screen.
Are you suggesting I use named ranges instead of aligning my data for each record into  

RE: Help Getting Excel Data Into Attachmate session/emulator

What version Excel?

Named Ranges or, if Excel 2007+, Structured Table Reference, makes the Excel coding simpler.

Also, it would make the entire development process much better if you would code in Excel VBA, IMNSHO.

Your application is rather complex.  You begin with an INQUIRY transaction.  Then you must test for a sucessful inquiry and have a control structure to deal with success or not.  Then you LOAD data to UPDATE and submit an UPDATE transaction.  Then you must test for that result and deal with success or not.  That seems to be the extent of your loop.

I would work on the control structure first.  Here's a snippet of some general control code that I use...

CODE

    Const COMP = "LAST PAGE DISPLAYED"
    Const MORE = "MORE DETAIL LINES - PRESS ENTER TO CONTINUE"
    Const NONE = "TRAVELER NOT FOUND ON SERIAL SYSTEM"

    With oScrn
        For Each r In wsPartList.[PART_ID]
            sPn = r.Value
            .Area(3, 17, 3, 33).Value = sPn & "               "
            Do
                .MoveRelative 1, 1, 1
                .SendKeys ("<enter>")
                Do Until (.WaitForCursor(3, 17))
                    DoEvents
                Loop
'
'  screen data stuff occurs in here
'
NextPN:
            Loop Until GetField(sIn, "MSG") = COMP Or GetField(sIn, "MSG") = NONE
        Next
    End With

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Help Getting Excel Data Into Attachmate session/emulator

(OP)
Hi,

I am using Excel 2007 and use of Structured Table Reference is doable for me. My issue is linking the two applications together so that the repetitiveness of entering the same type of data can be automated as much as it can via a macro.

This code below worked in getting my attachmate application to open an excel file, albeit it DOES NOT do a thing as far as scraping my data because the references as not tailor to my needs as I got this from another post.

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

************************************
My concern going forward is how I would even start to compose a code that will automate this between the two application. Please steer me anyway in the right direction.

Also I was able to record a macro session today of what I do inside attachment mate using the data i copy from Excel 2007 and pasted into attachmate; hopefully someone can help. Thanks.

***********************************
 ' 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 = 2000        ' 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.Paste    
    Sess0.Screen.Sendkeys("<Enter>")    
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
    Sess0.Screen.Paste    
    Sess0.Screen.Paste    
    Sess0.Screen.Sendkeys("<Tab>1<Tab><Tab><Tab><Pf5>")    
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
    Sess0.Screen.Paste    
    Sess0.Screen.Paste    
    Sess0.Screen.Paste    
    Sess0.Screen.Paste    
    Sess0.Screen.Paste    
    Sess0.Screen.Sendkeys("<Tab><Up><Down><Down><Down><Tab><Tab><Tab><Tab><Pf5>")    
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
    Sess0.Screen.Paste    
    Sess0.Screen.Paste    
    Sess0.Screen.Paste    
    Sess0.Screen.Sendkeys("<Tab><Tab><Pf5>")    
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
    Sess0.Screen.Sendkeys("nn<Pf5>")    
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

    System.TimeoutValue = OldSystemTimeout
End Sub
*************************
 

RE: Help Getting Excel Data Into Attachmate session/emulator




What are the EXACT screen MESSAGES for complete, done, invalid for both INQUIRY & UPDATE?

Is there an UPDATE (add,change,delete) field or a separate UPDATE transaction?  In other words, how does an UPDATE occur?

What are the PF Keys and other keys for controlling necessary screen function?

YOU have to specify which Excel column/Value is used for INQUIRY.

YOU have to specify where each Excel column/value will be placed on the screen, as a row,col pair.
 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Help Getting Excel Data Into Attachmate session/emulator

(OP)
Skip,

What are the EXACT screen MESSAGES for complete, done, invalid for both INQUIRY & UPDATE?

I will get the EXACT messages for you tomorrow if you want the exact ones.


Is there an UPDATE (add,change,delete) field or a separate UPDATE transaction?  In other words, how does an UPDATE occur?

An update happens when I press the PF5 key.

What are the PF Keys and other keys for controlling necessary screen function?

These keys are labeled at the bottom of the screen. I will include an image of it.

<<http://www.mediafire.com/?1rkwpnn7ewsluf1>>


YOU have to specify which Excel column/Value is used for INQUIRY.

It will be included in the image that i post with this, was converted from xls to text file.

YOU have to specify where each Excel column/value will be placed on the screen, as a row,col pair.

In the included jpeg image, all the fields that i highlighted in PURPLE are where the values of the excel date will go into. The fields not highlighted are prepopulated; therefore NO PASTING is necessary. As far as determining whether it'll be placed within a row, or column pair, I honestly do not know; this is why I am graciously asking for any help whatsoever. Thanks for the inputs so far, bare with a novice like me as i work through this.

 

RE: Help Getting Excel Data Into Attachmate session/emulator


I do not want a PICTURE.

You need to RECORD the ROW,COL for each field!  THAT is what your program will use for each column of data from Excel.

You pull up that screen and TAB to to field and the emulator shows you the ROW,COL of your cursor at the bottom of the screen.

If your emulator daes not display ROW,COL, then COUNT to determine this data.  IT MUST BE DONE!

PLEASE do that!

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Help Getting Excel Data Into Attachmate session/emulator

(OP)
I will do that as soon as I can. Thanks for your input and patience. Have a good day.

RE: Help Getting Excel Data Into Attachmate session/emulator

(OP)
Skip,

So I was able to find out that the attachmate application/emulator that I am using has a 24x80 grid/space; by that there are 24 rows by 80 columns.

My data from excel would go to the following rows/columns.
1.) 4x20
2.) 4x46
3.) 5x71
4.) 17x50
5.) 21x30

etc, etc........

So my questions are as followed:

1.) How would  even begin to reference my data in excel and have it scrape into these rows and columns and then makes it loop until blank/no record?

2. Using the following codes below, how would i tweak it so that it does the scraping for me? (Again the code below, i got it from another post. It is successful in opening an excel worksheet from Attachmate but the references to excel and attachmate rows/columns are wrong.)

Please any help/suggestion would be greatly appreciated. If more info is needed, please let me know. Thanks for your time and effort.


'--------------------------------------------------------------------------------
' This macro was created by the Macro Recorder.
' Session Document: "C:\Program Files\Attachmate\E!E2K\Sessions\Nts.edp"
' Date: Tuesday, January 17, 2012 11:47:53
' User: 067
'--------------------------------------------------------------------------------

' 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)
 ----------------------------------------------------------------------------------
 '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:="\\DTCNAS-MNMI004\C_MTGCMC_Users\067\My Documents\411_macro_spreadsht.xlsx"
        Set xlSheet = xlApp.activesheet
        Set MyRange = xlApp.activesheet.Range("B3:B14")
    


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  
 

RE: Help Getting Excel Data Into Attachmate session/emulator

CODE



Sub test()
'this is your data mapping table with NAMED RANGES in an Excel sheet...
' exROW  exCOL
' 4      20
' 4      46
' 5      71
' 17     50
' 21     30

 '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
        
    With xlApp.Workbooks.Open(Filename:="\\DTCNAS-MNMI004\C_MTGCMC_Users\067\My Documents\411_macro_spreadsht.xlsx")
   
'Explicitly reference your sheet!!!!     
        With .Worksheets("YourSheetName")
        
'Your Excel range starts in B3 and continued downward to the END of data     
            Set MyRange = .Range(.Cells(3, "B"), .Cells(3, "B").End(xlDown))

            Dim r As Object, iCol As Integer

            For Each r In MyRange
                For iCol = 1 To 5
                    Sess0.Screen.PutString .Cells(r.Row, iCol).Value, Range(exRow)(iCol), Range(exCol)(iCol)
                Next
                
                Sess0.Screen.SendKeys "<ENTER>"
                Sess0.Screen.MoveRelative 1, 1
'[b]WhatRow and WhatCol are the row,col that this screen's cursor returns to
                Do Until Sess0.Screen.waitforcursor(WhatRow, WhatCol)
                  DoEvents
                Loop
            Next
            
        End With
    End With
End Sub

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Help Getting Excel Data Into Attachmate session/emulator

(OP)
I will try out this and see if it is successful. Thank you for the input.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close