Reading Acct/SSNs from a Text/Excel file in an Attachmate Loop

[joet222]

Hello.

I've been reading some posts on the forums, along with the FAQs trying to put together a solution that I can understand.

I'm trying to automate the task of opening a list containing several hundred or several thousand account numbers, pasting them one at a time into Attachmate, performing a simple SendKey Procedure, and then moving on to the next number. I don't really need to get Excel to talk to Attachmate, or vice versa. I can take the list of numbers down to a simple text file if necessary. The biggest challenge I have is keeping it simple enough A) That I can roll it out to co-workers and B) So that I can understand it. My level of expertise pretty ends with the 10 tips in the one FAQ written. If anyone has any suggestions, solutions, bits of code I can copy and paste, I'd really appreciate it.

Thanks,
Joe T.
JoeT222@aol.com

 

[Skie]

If you're just looking to enumerate down the page for every SSN in a column then just script your VBA to run through each SSN and perform the desired action.

'Blah blab blah, obtain your system, session, & screen objects
With Sheets("SSNs")
    For i = 1 to YourRows
        sSSN = .Cells(i,1).Value
        oScreen.PutString sSSN, 1, 1
    Next
End With

 

[joet222]

Thanks for your reply. Please forgive my follow-up questions, the term "technical user" is a bit kind when it comes to me...

OK, I got ya on the loop to run down the column within Excel. Each time the loop runs sSSN picks up the value of the current cell before ticking down one more cell and picking up that value. Good to go.

My confusion is more procedural (I think) -
- Should I be creating this macro in Excel or Attachmate?
- Once the value of sSSN is set in Excel - what is necessary to switch to Attachmate and run the PutString command? Is that done through the "oScreen"
- How does the switch back to Excel take place?

Thanks again for your help.

 

[IknowMe]

As your OP refers to an 'attachmate loop' here's some code to loop through the excel sheet. Just add your put string ect to inside the loop. thread1-1328147

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[Skie]

Should I be creating this macro in Excel or Attachmate?

IMO, it depends on how you want the user to interact with Excel. If they don't need to see that it's opening an Excel sheet, then it's probably better to write it in Extra. If the user has to interact with the Excel sheet, then it's better to write it in VBA.

Once the value of sSSN is set in Excel - what is necessary to switch to Attachmate and run the PutString command? Is that done through the "oScreen"

Yes, oScreen would the Extra session's screen object.

Dim oSystem As Object
Dim oSession As Object
Dim oScreen As Object
Set oSystem = CreateObject("EXTRA.System")
If oSystem Is Nothing Then
  Msgbox "Could not create the EXTRA System object."
  STOP
End If
Set oSession = oSystem.ActiveSession
If oSession Is Nothing Then
  Msgbox "Could not create the Session object."
  STOP
End If
Set oScreen = oSession.Screen

How does the switch back to Excel take place?

The coding does it. If you code this in Extra, the user wouldn't even have to know that Excel was open (it defaults being opened as hidden). The script uses the Excel object to obtain data from Excel and the script uses the Extra screen object to read and write data to your Extra screen. Neither application has to be active for the macro to pull the data from Excel and input the data in Extra.

 

[CompuDork]

You can always write code to read from a text file and enter into extra... Use the open file.txt for input as #1
and loop