Grab Muliple data from Attachmate to Excel

[fluidmotion11]

I am new to the forum and did not find what I was looking for when searching.
I am using Extra enterprise 2000 here at my office and we use macros to pull information from excel input to our mainframe and the output back to excel. What I am looking for is the lines of code needed to pull several lines into multiple rows into excel. I have the following code that can pull only one peice of data off the screen. I would like to make it so when the macro enters the information into the system, it will pull off characters in string (12,4,13) into column b, (13,25,25) into column C, etc......, all the way down to (38,4,13) and (39,25,25). Also, I would like it to be able to check the next page(s) (F8 - to page) as well.

'--------------------------------------------------------------------------------
' This macro was created by the Macro Recorder.
' Date: Thursday, September 05, 2002 08:37:09
' User: jwar034
'--------------------------------------------------------------------------------

' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$

Dim Sessions As Object
Dim System As Object
Dim Sess0 As Object

'$include "returnblock.ebh"
'$include "lasthist.ebh"

Sub Main()
'--------------------------------------------------------------------------------
' Get the main system 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 = 30 ' milliseconds

OldSystemTimeout& = System.TimeoutValue
If (g_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = g_HostSettleTime
End If

' Get the necessary Session 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.KeyboardLocked = true

dim excel as object
dim selection as object
set excel = getobject("","Excel.Application")
if (excel is Nothing) then
msgbox "Couldn't find Excel.Application!"
exit sub
end if

set selection = excel.selection
if (selection is Nothing) then
msgbox "Couldn't get Selection!"
exit sub
end if

dim buf as string
dim selcol as integer
dim selrow as integer
dim i as integer
dim col as integer

selcol = selection.columns.count
selrow = selection.rows.count

Sess0.Screen.SendKeys("<Clear>/for waodmnu<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys("<EraseInput>"+trim(selection.item(1,1).value)+"ALLA<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
gCurrentBlock = "waodmnu"
for i = 1 to selrow
Sess0.Screen.SendKeys("<EraseInput>"+trim(selection.item(i,1).value)+"ALL<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
selection.item(i,selcol).value = Sess0.Screen.GetString(8,14,8)
next i

msgbox "Process complete."

endofmacro:
Sess0.KeyboardLocked = false

System.TimeoutValue = OldSystemTimeout
End Sub

 

[SkipVought]

Hi,

What application are you coding in? You seem to be doing a CreateObject for BOTH Extra and Excel. Depending on which application your macro is in, you probably only need to create ONE. The other is the APPLICATION. I almost always code in Excel VBA. Forum707.

"it will pull off characters in string (12,4,13) into column b, (13,25,25) into column C, etc......, all the way down to (38,4,13) and (39,25,25)."

set xl= getobject("","Excel.Application")
set xlSheet = xl.workbooks(1).worksheets(1)
iColXL = 2
for lRow = 12 to 39
   xlSheet.Cells(1, iColXL).value = Sess0.Screen.GetString(lRow,4,13))
   iColXL = iColXL + 1
next

"Also, I would like it to be able to check the next page(s) (F8 - to page) as well."

Check out SendKeys...

Sess0.Screen.SendKeys("<F8>")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fluidmotion11]

thanks for the reply. I will give what you said a shot.

the way that macro and others we use are set up is they run via attachmate, but grab a column of selected data from excel and input each cell it into a screen in attachmate. from there, attachmate grabs the sting and exports that string to excel next to the original data. these macros were set up prior to me being at the company and the person who created them is long gone. I am just trying to pick up the pieces and figure all of it out.

 

[SkipVought]

"...they run via attachmate"

Does not matter. The writing to Excel is the key item.

You must explicitly or implicitly reference the application, workbook, worksheet & range. In my little example, xl is the application. You must either have a workbook open or add a workbook. The referenced worksheet, set xlSheet = xl.workbooks(1).worksheets(1), is the first worksheet, or you could use a worksheet name, set xlSheet = xl.workbooks(1).worksheets("Sheet1"). The Cells object has a row and column argument. It's pretty simple.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}