-6

I'm working with a 20-year-old database that our business division doesn't own and has limited access to, though it does support VBA macros for users who do a lot of repetitive tasks. I'm working on a way to automate searching a massive amount of "screens" for specific text and I have gotten most of the way there but... not quite. There are many categories (and subcategories) of screens that need to be searched and currently I only know how to search them using sendkeys that enter the address of the screens over and over, in order. I have another script that searches the text of the screens and notes the screen address of any screen that contains the specific text so that part doesn't concern me.

My current methodology looks like: 

do thing
ibmCurrentScreen.SendKeys ("a01a01")
do thing

do thing
CurrentScreen.SendKeys ("a01a02")
do thing

etc. where a01a01 goes in sequence up to z99z99 and each screen contains unique information (though most of them are empty) and there are gaps in a lot of sections. For example some only go up to a10e99 before going to b01a01.

Ideally, I'd like my code to look like:

do thing
CurrentScreen.SendKeys ("a01a01")
do thing
repeat for each screen address

and I could either add a list of the valid addresses at the end or get the VBA to refer to an excel table with all of the valid screens. Is there a way to repeat the same section of code exactly the same but only changing the sendkeys input without actually copying and pasting the whole code over and over with different addresses? I obviously don't have a programming background but a solution like this would be tremendously helpful. Thanks!

Andrew
  • 39
  • 7
  • 1
    The question is unclear, but obviously you can write a sub which uses sendkeys with a string computed from some input parameter. In any event, any solution based on `SendKeys` is inherently fragile and prone to failure, Are you *sure* that there isn't some API that you can use instead? The chance that you will have thousands of sendkeys successfully sent with no e.g. timing error is pretty small. Even a single use of `SendKeys` can be a headache. Thousands of uses is a nightmare. – John Coleman May 01 '18 at 16:25
  • Can you tell me more about using a string with sendkeys? I'm aware its janky but the database is practically unchanged since it was built in the 90s and nobody knows anything about it anymore. At present its just accepted that searching through 50000 screens by manually inputting each address and looking at each screen for something anytime its needed is the way of life. – Andrew May 01 '18 at 16:36
  • `SendKeys s` where `s` is a string variable. Anytime you find yourself copy-pasting blocks of code and just editing a couple of lines, there is a sub waiting to be born. Also, why be superstitious? Don't accept the database as an opaque oracle. Take the time to understand it. Read old manuals if need be. There is almost certainly some way to interact with it programmatically other than via `SendKeys`. – John Coleman May 01 '18 at 16:43
  • 2
    Where is this DB? Do you know what it's written in? Where is it hosted? The front end through which you're searching isn't the data itself so surely there is a datastore somewhere else you could query directly. – Brad May 01 '18 at 16:47
  • Logically and intuitively I know there must be some way, but I didn't know what questions I should be asking. I figured I would get pointed in the right direction by posting here, but to somebody like me with no programming background to speak of, "string" is Chinese to me. As far as the database itself, it's been an uphill battle finding any information about it at all. I'm hoping to use this case as more fodder for the "database revamp" project we are all hoping for. – Andrew May 01 '18 at 16:56
  • Are you accessing the database via internet? How do you log in? I'm so curious and positive there'll be a super easy reliable way to get in to it. – jamheadart May 01 '18 at 16:58
  • The database is on a network, and the front end we use to interface with it is also hosted remotely so we only have a "reflection" of it, if that makes any sense - it doesn't to me. It hasn't had official support since the early 2000s so its really a mystery to me. – Andrew May 01 '18 at 17:00
  • Is this what you're using? http://docs.attachmate.com/reflection/16-1/vba-prog-guide/webframe.html#howtos.html – Tim Williams May 01 '18 at 17:27
  • Hi Tim, our software is proprietary but that resource was helpful as there are a lot of similarities and bits of code I think I will be able to use. – Andrew May 01 '18 at 18:17

1 Answers1

0

I've got everything working, though the method is still pretty sketchy. I've pieced together a script from bits of code here and there, mostly thanks to Tim Williams' comment. The program is indeed a version of Attachmate 2014, so that resource with its various guides was pretty helpful.

In short, my solution is this:

Private Sub CommandButton1_Click()
    Dim app As Attachmate_Reflection_Objects_Framework.ApplicationObject
    Dim frame As Attachmate_Reflection_Objects.frame
    Dim terminal As Attachmate_Reflection_Objects_Emulation_IbmHosts.IbmTerminal
    Dim view As Attachmate_Reflection_Objects.view
    Dim screen As Attachmate_Reflection_Objects_Emulation_IbmHosts.IbmScreen
    Dim cellData As String, w As String, x As String, y As String, z As String
    Dim rCode As ReturnCode
    Dim row As Integer
    row = 3

    Initialization stuff
 Do
        'Get the data from Excel
        w = Worksheets("Calculator").Cells(row, 4).Value
        x = Worksheets("Calculator").Cells(row, 5).Value
        y = Worksheets("Calculator").Cells(row, 6).Value
        z = Worksheets("Calculator").Cells(row, 7).Value

        'Put the data into the appropriate fields
        rCode = screen.PutText2(w, 22, 21)
        rCode = screen.PutText2(x, 22, 47)
        rCode = screen.PutText2(y, 22, 75)
        rCode = screen.PutText2(z, 23, 20)

        'Navigate and copy from the screens
        rCode = screen.SendControlKey(ControlKeyCode_Transmit)
        rCode = screen.WaitForText1(200, "4", 22, 4, TextComparisonOption_RegularExpression)
        screen.SelectAll
        screen.Copy
        rCode = screen.WaitForHostSettle(30, 100)
        rCode = screen.SendControlKeySync(ControlKeyCode_F3)

        'Increment the counter to the next row
        row = row + 1

        'Paste into worksheet
        With Worksheets("Data")
            Range("A1").Select
            .Paste
        End With

   Loop While Worksheets("Face").Range("D7").Value = "Searching..."

End Sub

And from there, excel uses basic IF statement to look at the pasted text and determine if the search term is in it. It goes through ~40 screens per minute which is comparable or slightly slower than a human but over a long period of time its definitely faster and easily more accurate.

My next step will be to rebuild it where it scrapes text fields and compares it to the search term itself instead of all the copy pasting nonsense that is the bulk of the time per loop.

EDIT: A few Weeks later... I've made some optimizations using another very helpful vba function from the attachmate library, ".gettext(row, column, length)" and rather than copy & pasting, comparing the "gettext" (stored as a string) with the search term (also stored as a string from the excel worksheet). It hasn't had a noticeable impact on search speed, however. I've also added some logic to it that detects if it's on a blank screen (using .gettext), and skips to the next non-blank screen (the address of the next non-blank screen is shown on all blank screens so it's a simple matter of reading and interpreting that condition). All in all, it's doing the job and my colleagues (none of us are in IT or have knowledge of coding) are quite impressed. The next step will be scraping and cashing the screen info in a pseudo-database copy that I will build to mirror the live database which I can easily store in an excel sheet and use ctrl+f to search instead, and perhaps set it to update itself maybe once a week or once a month (since the database is rarely ever updated). All of this because we do not have a procedure to share database access and nobody cares enough to develop one.

Andrew
  • 39
  • 7