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!

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

Help Getting Excel Data Into Attachmate session/emulator 1

Status
Not open for further replies.
RussellWhy

RussellWhy

Technical User
Jan 17, 2012
10
US
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:


 
Sort by date Sort by votes
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

***************************************************************
 
Upvote 0 Downvote
Attached are images of what i am doing in excel and what i am using as far as attachmate is concern. Please any help will suffice as i am a newbie to VBA and codes.

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



************************************************
 
Upvote 0 Downvote

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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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.

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



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...
[tt]
xlField exROW exCOL
1
2
3
4
5
6
7
8
[/tt]
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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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
*************************
 
Upvote 0 Downvote



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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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.

<<

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.
 
Upvote 0 Downvote

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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I will do that as soon as I can. Thanks for your input and patience. Have a good day.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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")
   
'[b]Explicitly reference your sheet!!!![/b]     
        With .Worksheets("YourSheetName")
        
'[b]Your Excel range starts in B3 and continued downward to the END of data[/b]     
            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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I will try out this and see if it is successful. Thank you for the input.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

kzhkr
  • Locked
  • Question
Attachmate EXTRA! with Excel
Replies
4
Views
290
kzhkr
kzhkr
Mike.and.H
  • Locked
  • Question
Syntax problem in AttachMate macro working with MS Word
Replies
4
Views
255
Mike.and.H
Mike.and.H
2009luca
  • Locked
  • Question
show extra session in a vb6 form
Replies
1
Views
226
SkipVought
S
DougExtra21
  • Locked
  • Question
Attachmate Comparation
Replies
8
Views
281
SkipVought
S
gouldie64
  • Locked
  • Question
Access - Attachmate Location
Replies
6
Views
238
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top