Attachmate Macro to read data from Excel 2

[Mikemix]

Hi,
I’m looking for an elegant way to read data from cell's from within an Excel sheet using Attachmate Extra Basic.
Im using Extra 5.2.
I need to get data from cells within excel and store them in variables to apply functions on the data and insert the processed data in fields in a session.

Anyone who can provide me with some example code would be much appreciated.

Thank you,
Mikemix

 

[calculus]

I don't think it's possible to read directly from an Excel file. In real programming languages, you could use a DAO or ADO to accomplish this, but EB does not have any of that functionality that I've ever found.

What I've done in the past is save the file as a .csv or .txt file, then you can use EB to open the file and get data. If this is something that needs to be ongoing, you can write a VBA program that will do this for you and fire the macro using the shell command:
rc% = shell ("...ebrun.exe yourmacronamehere.ebm&quot

It's critical that the macro name not have spaces in it.

Hope this is helpful.

 

[steveycarr]

*** You can read direct from Excel ***
If you still require some code to get you started I'll post it for you, no probs

 

[calculus]

I'd love to see it.

 

[DvlDawg]

when you post that code, please show how to get the macro to NOT open instance after instance of Excel. I can get it to read and paste into a Worksheet, but it keeps opening one after another of excel.

 

[samato]

What if I want the opposite - I want to scrape a screen to Excel and append the file?

 

[SeMack]

You could try adding the following code to a module in VBA for excel. (Shift + F11 from excel)

'-------------------------------------------
Sub Test()
Dim Field1 As OmniFieldRecord
Dim Field2 As OmniFieldRecord
Dim Sheet1 As Worksheet
Dim MyScreen As Object

Set MyScreen = GrabExtra
If MyScreen Is Nothing Then Exit Sub

Set Sheet1 = Sheets("Sheet1&quot

Sheet1.Cells(1, 1).Value = "Howdy"

Field1.x = 14: Field1.y = 2: Field1.z = 5
Field2.x = 14: Field2.y = 2: Field2.z = 5

Field1.Value = Sheet1.Cells(1, 1).Value

Key Field1, MyScreen

Field2.Value = GetField(Field2, MyScreen)

End Sub
'-------------------------------------------
Type OmniFieldRecord
Value As String
x As Integer 'Row
y As Integer 'Col
z As Integer 'Len
End Type

Function GrabExtra() As Object
Dim Sess As Object
Dim Sys As Object

On Error GoTo One

Set Sys = CreateObject("EXTRA.System&quot
Set Sess = Sys.ActiveSession
Set GrabExtra = Sess.Screen

GoTo Two
One:
MsgBox prompt:="Extra! is not open.", Title:="Error Message"
Set GrabExtra = Nothing
Two:

On Error GoTo 0

Set Sys = Nothing
Set Sess = Nothing

End Function

Function GetField(Record As OmniFieldRecord, MyScreen As Object, Optional Offset As Integer = 0) As String
GetField = MyScreen.GetString(Record.x + Offset, Record.y, Record.z)
End Function

Function ClearKey(Record As OmniFieldRecord, MyScreen As Object, Optional Offset As Integer = 0)
MyScreen.MoveTo Record.x + Offset, Record.y
MyScreen.SendKeys (&quot;<EraseEOF>&quot
End Function

Function Key(Record As OmniFieldRecord, MyScreen As Object, Optional Offset As Integer = 0)
MyScreen.MoveTo Record.x + Offset, Record.y
MyScreen.SendKeys (Mid(Record.Value, 1, Record.z))
End Function

Function HitKey(Key As String, MyScreen As Object, Optional WaitTime = 0)
MyScreen.SendKeys (Key)
MyScreen.WaitHostQuiet (WaitTime)
End Function

 

[SeMack]

Sorry, that is (Alt + F11 from excel) not shift + F11

 

[cous261]

ExtraHeadScratcher
Please post your code I would love to see it also.

 

[DvlDawg]

I tried this code snipet, and all I can get it to do is open the session. What am I missing to get it to send a cell of data to the screen?

 

[DvlDawg]

Got it, works like a charm now. This is exactly what I needed, sometimes you cant see the forest for the trees until you take a step back and regroup.

 

[makavity]

I copied this into Alt-11 - but I get a compiling error in Excel (&quot;User-defined type not defined&quot at each function.

I'm using Exel 97. It is me?

JR

 

[makavity]

Hey! Figured it out myself!

I have seen this request in a number of posts.. hope this helps.

Got this from the Attachmate website:

To create a macro that pastes a range of cells from an Excel 97 worksheet into a host screen, use the following section of code as a template.

dim obj as object, Sess0 as object, objWorkBook as object
set obj = CreateObject( &quot;Excel.Application.8&quot; )
obj.visible = TRUE
obj.Workbooks.Open &quot;c:\msoffice\excel\library\common.XLS&quot;

set objWorkbook = obj.Worksheets(&quot;Sheet1&quot

'This copies the range of cells to the clipboard
objWorkBook.Range(&quot;A1:C3&quot.copy
'You can also select just one cell with (&quot;A1&quot
'Or you can do something like Name = ObjWorkBook.Range(&quot;B14&quot

'This pastes the clipboard to the screen
Sess0.Screen.paste

Note: Excel.Application.8 is specific to Microsoft Excel 97. For Microsoft Excel 2000, use Excel.Application.9. For other versions of Excel, open the registry and look under HKEY_CLASSES_ROOT for Excel.Application.? and find the version you have installed.

 

[JimGE]

Here is an example of creating/updating excel worksheet, range, whatever via ADO run w/Attachmate EXTRA! 7.0 Object Library & Microsoft DAO 3.6 Object Library.

Sub Main

Dim adoConn as object
Dim TableRS as object
Dim FieldRS As Object
Dim strConString as string
Dim FileName as String
Dim MyErr as String

Set adoConn = createobject(&quot;ADODB.Connection&quot
Set TableRS = CreateObject(&quot;ADODB.Recordset&quot
Set FieldRS = CreateObject(&quot;ADODB.Recordset&quot
'To be used to enumerate the worksheets in Excel

Path = &quot;Q:\Agency\&quot;
FileName = &quot;FDATTYB.XLS&quot;
DataFile = Path & FileName

StrConString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source=&quot; & _
DataFile & &quot;;&quot; & _
&quot;Extended Properties=&quot;&quot;Excel 8.0; HDR=Yes&quot;&quot;&quot;
adoConn.open strConString

set TableRS = adoConn.OpenSchema(20) 'Get Sheet List

'Enumerate these by looping through the TableRS recordset as normal

Do Until TableRS.EOF
If Left(TableRS(&quot;TABLE_NAME&quot,1) <> &quot;_&quot; Then
SheetName = TableRS(&quot;TABLE_NAME&quot

FieldRS.CursorLocation = 3
FieldRS.open &quot;Select * from [&quot; & SheetName & &quot;]&quot;, adoConn, 2,3

NbrFlds = FieldRS.Fields.Count
For Z = 1 to NbrFlds
FldNm = FieldRS.Fields(Z-1).Name
Test = FieldRS(FldNm)
Next Z
FieldRS.Close

StrSQL = &quot;Drop Table [Excel 8.0;Database=&quot; & DataFile & &quot;].[NewData]&quot;

On Error Resume Next
adoConn.Execute strSQL
Myerr = adoConn.Errors.Count
Myerr = adoConn.Errors(0).Description

If adoConn.Errors.Count = 1 Then

strSQL = &quot;SELECT * &quot; & _
&quot;INTO [Excel 8.0;Database=&quot; & DataFile & &quot;].[NewData]&quot; & _
&quot; FROM [&quot; & SheetName & &quot;]&quot;

adoConn.Execute strSQL

Else

strSQL = &quot;Insert Into [NewData$] In '&quot; & DataFile & &quot;' 'Excel 8.0;' Select * &quot; & _
&quot;FROM [&quot; & SheetName & &quot;] As A&quot;

adoConn.Execute strSQL

End If

Myerr = adoConn.Errors.Count
MyErr = adoConn.Errors(0).Description

End If
TableRS.MoveNext
Loop

TableRS.close
adoConn.close

End Sub

 

[calculus]

You may want to check out the FAQ section as well. Using ADO works, but it's actually much more difficult than just using VBA to get data from Extra.

How do I use VB(A) to manipulate attachmate (6.5+)? faq99-4069

calculus

 

[Qwazerty]

Hi
In first you must declare extra.tlb in excel (Library Type) I haven't the right word because I haven't excel on my computeur...
You must declare the path of Extra on Windows's variables of Environement (in Windows Explorer -> Controle Panel ->
System -> ... and find the buttom call 'variables of Environement' Maybe it isn't the exact word because I have Windows French... sorry
So it's ok tape this code and go...
++


' Global variable declarations
Global g_HostSettleTime%

Sub Main()
'--------------------------------------------------------------------------------
' Get the main system object
Dim Sessions As Object
Dim System As Object
Dim TmpVal as string
Set System = CreateObject(&quot;EXTRA.System&quot ' Gets the system object
If (System is Nothing) Then
Msgbox &quot;Could not create the EXTRA System object. Stopping macro playback.&quot;
STOP
End If
Set Sessions = System.Sessions

If (Sessions is Nothing) Then
Msgbox &quot;Could not create the Sessions collection object. Stopping macro playback.&quot;
STOP
End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
g_HostSettleTime = 700 ' 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 &quot;Could not create the Session object. Stopping macro playback.&quot;
STOP
End If
If Not Sess0.Visible Then Sess0.Visible = TRUE
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

' This section of code contains the recorded events

 

[HarryPeltz]

Qwaz... I have succeeded in pulling data from Extra! to an excel file.. then taking the resulting file and putting it back into Extra.

I am trying to add a line to the first macro that will set the column to a text display, instead of a general display (since the data is numerical, excel tends to display long strings of numbers in a scientific format - not good for my purposes).

I've tried recording a macro in excel, then copying & pasting the macro into my extra macro .

Problem: Extra does not recognize the command used to format the cell in a text format.

Example:
Columns("A:A").ColumnWidth = 18.86
Range("A1").Select
Selection.NumberFormat = "@"

Extra will run the first two lines. The 3rd line results in a problem.
Is there a workaround?

regards
Harry

 

[Qwazerty]

Hello
Why that you recording a macro in Excel for copying the macro into your Extra macro ? I don't understand why you do that ?
With the code who i gave you, you don't have need to use Extra Macro...The code who I gave you run on Excel.
If you had create a Macro in Extra, you can copy it in the Excel macro, so Excel drive Extra not the reverse.
I'm not verry good in english, so if you don't understand me, say it to me and I will tray to re-explaind
Or if I had not understood what you say me, re-explaind me the probleme ... sorry
++
Ps:

 

[Qwazerty]

Hi
If you juste want to use Excel like a Database, and use Extra Macro, you can create a Excel macro, named CellModif, with you code
"Example:
Columns("A:A").ColumnWidth = 18.86
Range("A1").Select
Selection.NumberFormat = "@""
and you call it on ExtraMacro, it can be done, but I don't remember how ... in Excel's help files you may find it..
Enjoy

 

[JeaShe]

Harry et al... re the cell formatting, I usually use an Excel template to set up all my formatting, then when I send data to that cell, it's already formatted. You'd have to maintain the template on individual systems rather than work off a server since Excel is somewhat unstable under those conditions.