Programming in Extra!: using array to collect data from screen and paste to Excel

My client has Attachmate Extra! Enterprise.  I'm writing a program in Extra to screen-scrape a bunch of data from a database that apparently doesn't have an API I can get at.  I've found some documentation on the Extra! object model that apparently is close enough to allow me to make progress (so far), but nothing yet on Attachmate's version of the Visual Basic language, so I'm having to find by experimentation how it differs from VBA and VBScript.  (For example, I gather that I can write Subs and Functions but only if they appear before the main routine, not after.  I think it will not support user-defined classes, and the ReDim command doesn't seem to work.  I can't define an array with more than 9999 rows.  And so on.)

When working with Excel, it can take forever to populate thousands of cells one at a time; but populating an array and then pouring the array into an Excel range is almost instant.  The usual command, once I have the data in an array, looks like this:

Range(ows.Cells(<row>, <column>), ows.cells(<row>, <column>)).Value = array

...where "ows" is the worksheet object.  But the Attachmate macro editor flags this statement as an error.  I've tried several variations, and have come to suspect that it simply will not support an array used in this way.  Maybe I'm wrong, though.  Can anyone tell me what syntax I should use?

To write the data one cell at a time into Excel would take many minutes, perhaps 30 or 60, in addition to the time it takes to scroll through perhaps 500 panels.  If I have to, I think I can use the FileSystemObject to write the data to a text file and then import it into Excel, but I suspect I can do it more directly with an array if someone will just tell me what I'm doing wrong, here.

  • Hi BobBridges,

    Based on the name of the product, this fairly old technology, 10 years old.  You might have found this KB Article that does include references for older versions of Attachmate Extra!. Possibly someone in the community can help, but if you need support from Micro Focus, please e-mail or call us 800-688-3270.

    Regards,

    Jeff

  • Thanks for the references, Jeff.  I will certainly keep these and refer to them once I get back to this project.  I think I have the immediate solution—in two different ways, actually.

    At the time, it  finally occurred to me that the time it takes for the mainframe to scroll from one panel to the next will take up most or all of the time I would save by working with arrays anyway.  In other words, if I write the data to cells individually, yeah, it'll take many minutes to fill up thousands of cells—but it would take that long to scroll through hundreds or thousands of panels anyway.  So I wrote it that way; I think the entire program runs in 5 or 10 minutes.

    But recently I was writing a VBS program for Excel and ran into a similar problem, going the other way:  I wanted to assign a range of an Excel worksheet to an array—again, to save considerable time—and wscript kept telling me it was an error, a type mismatch I think.  I experimented with many ways of allocating the array, and trying to match up the array bounds with the cell bounds in the worksheet, before I accidentally stumbled on the solution:  If I don't Dim and Redim the array at all, just assign it the Excel range as if the array variable is scalar, it works fine.  (Furthermore, the lower bound of the array is 1, instead of 0 as is usual in VBScript.)

    In the program I asked about here the problem isn't the same, because it's trying to go the other way, ie load up an array and dump it into a worksheet.  But I have hope that this'll give me some new ideas.

    And if not, as soon as I'm back on this project I'll look up your references.  In fact I will anyway, because I'm still hoping to find a reference not just on the Extra! object model but more information on the VB flavor, too.  Thanks again.

  • [SPAM][SPAM][SPAM and you're out of here!] Hi,

    I am also facing this issue recently.

    From SevenMentor, I found the solution, you can also check it once. Hope this will be helpful to you.