×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Using VBA to work with two Attachmate sessions at the same time

Using VBA to work with two Attachmate sessions at the same time

Using VBA to work with two Attachmate sessions at the same time

(OP)
Hi,

I have been working with Excel VBA and Attachmate Extra for a couple of weeks now. My issue is the process is very slow. I have around 5000 queries daily on Excel to send to Attachmate, however Attachmate responds some of the queries as long as 5 seconds. What I would like to do is open two sessions and keep sending next queries to these sessions when they become available. I know multithreading does not exist in VBA, but could it be possible with two instances of Excel maybe?

RE: Using VBA to work with two Attachmate sessions at the same time

Hi,

Some of you problem may be the method you use to wait for the mainframe to respond.

Please post your code.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Using VBA to work with two Attachmate sessions at the same time

(OP)


'This is my main sub, where I run 2 long functions for each row of excel.
'The two big functions createScenario and runScenario send a lot of commands to Attachmate Session and parses the answers back to Excel.
'What I would love to be able to do is to run 1to2500 on one Attachmate session and 2501to5000 on another

Sub RunScenarios()

Connect2Terminal
Randomize

For sc = 1 To 5000

'On Error Resume Next
createScenario (sc)

'On Error Resume Next
runScenario (sc)
Next

End Sub


'This is the function I got from some website to connect to terminal, I thought it could be relevant. Code and comments donot belong to me. Works perfectly fine for one active session.

Public Function Connect2Terminal() As Boolean

g_HostSettleTime = 50 ' milliseconds
Set System = CreateObject("EXTRA.System") ' Gets the system object
If (System Is Nothing) Then
MsgBox "err"
End
End If
Set Sessions = System.Sessions

If (Sessions Is Nothing) Then
MsgBox "err"
End
End If

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

' Get the necessary Session Object

Dim kontrol As Boolean
kontrol = False
sessiontercih = ""

If sessiontercih <> "" Then
For Each ses In System.Sessions
If ses.Name = sessiontercih Then
Set Sess0 = ses
kontrol = True
Exit For
End If
Next
Else
Set Sess0 = System.ActiveSession
End If

If kontrol = False Then Set Sess0 = System.ActiveSession

If (Sess0 Is Nothing) Then
MsgBox "err"
End
End If

If Not Sess0.Visible Then Sess0.Visible = True

Connect2Terminal = True

Exit Function

Connect2Terminal_Error:
Connect2Terminal = False

End Function

RE: Using VBA to work with two Attachmate sessions at the same time

Well you do have a lot of code. And at a glance, it seems well written.

You workbook structure leaves something to be desired. The only sheet that could be used as a proper table is Workshop. Data & Output are missing Headings. Data is not contiguous. FAQ68-5184: What are LIST & TABLE PRINCIPLES for Spreadsheet Users

1) The main thing that I can suggest is to do some things that can make any VBA code run faster. FAQ707-4105: How Can I Make My Code Run Faster?.

You’re doing the right thing by doing all your SendKeys in one procedure, using a good Wait loop and by running your Extra code in Excel VBA.

You do realize that the commands that your program sends to the mainframe are asynchronously processed, which is why your program must use OIA.XStatus to determine when the mainframe is ready.

2) There’s no reason why you couldn’t run as many sessions as you have PCs. You may need to set up the workbook containing your data as a database that you could read/write to via ADODB objects.

I’d try the first suggestion before trying to do the latter.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Using VBA to work with two Attachmate sessions at the same time

a further suggestion.

CODE

''''''''''''''''''''''''''Emptying cells
Sheets("Workshop").Range(domainColumn & sc + 1).Value = ""
Sheets("Workshop").Range(subdomainColumn & sc + 1).Value = ""
Sheets("Workshop").Range(paxTypeColumn & sc + 1).Value = ""
Sheets("Workshop").Range(journeyTypeColumn & sc + 1).Value = ""
Sheets("Workshop").Range(brandColumn & sc + 1).Value = ""
Sheets("Workshop").Range(segmentsColumn & sc + 1).Value = ""
Sheets("Workshop").Range(nSegmentsColumn & sc + 1).Value = ""
Sheets("Workshop").Range(mSegmentsColumn & sc + 1).Value = ""
Sheets("Workshop").Range(accCodeColumn & sc + 1).Value = ""
Sheets("Workshop").Range("N" & sc + 1).Value = ""
Sheets("Workshop").Range("O" & sc + 1).Value = ""
Sheets("Workshop").Range("P" & sc + 1).Value = "" 
If you defined a Named Range, for instance if you had Columns D:L and N:P Named Clear1, then the above code could be replaced by...

CODE

Intersect([Clear1], Rows(sc + 1)).ClearContents 

Alternatively, rather than row by row, you could clear the entire data area ONE TIME, Range(Rows(2), Rows(5001))

CODE

Intersect([Clear1], Range(Rows(2), Rows(5001))).ClearContents 'assuming 5000 rows 

You also seem to have an awful lot of DATA embedded in your code. This makes maintenance difficult. Data is much better maintained in tables.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Using VBA to work with two Attachmate sessions at the same time

(OP)
Skip, thank you very much for your very kind words and suggestions.

The code is not very good at the moment and far from final version. Since it has a deadline to be functionally complete in 3 working days now, I rarely had real time to tidy up the code.

So, coming back to the point of this thread; you have said:

"There’s no reason why you couldn’t run as many sessions as you have PCs. You may need to set up the workbook containing your data as a database that you could read/write to via ADODB objects. "

1. Does this mean I can run one instance of this code per PC?
2. I'm hearing ADODB for the first time. Does that mean other number of PCs running the separately code and updating the scenario table on main PC? If it is, sounds very good. I'll be looking into that.

RE: Using VBA to work with two Attachmate sessions at the same time

Wow! Three days! “I'm hearing ADODB for the first time.” You have quite a learning curve!

1) Yes. Each PC must have the required code to create and run the scenarios.

2) ADODB to be able to access the workbook/db to inquire and update. Since multiple workbooks must open/read/close or open/update/close via ADODB, your code must be prepare to use another wait loop tactic if one PC has the workbook/db open, and another is ready to open, it must loop until the workbook/db closes. I’d recommend posting this specific question in forum707: VBA Visual Basic for Applications (Microsoft). Other members may have more experience with ADODB Objects and may have other suggestions in general, that are better than mine. Keep in mind that your specific question really has nothing to do directly with Attachmate. It has more to do with updating and reading your sceanarios.

I’m going to be in and out today, so I’ll probably be of little help today. Good luck!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Using VBA to work with two Attachmate sessions at the same time

(OP)
Well, tbh I knew nothing about VBA or this terminal two weeks ago. When I saw the code to connect to terminal and send stuff, I thought I could do this and here we are! ^^

1. I want to rewrite my question. Can one PC run only one instance of the code?
2. Although it sound perfect for this project, I think I will have to pass it for this time. I only have three days and I'm not even fully done with functional coding. Instead I will just divide scenarios between 10 computers then will ask their users to send me outputs via email. Will definitely work on this for next sprint though!

RE: Using VBA to work with two Attachmate sessions at the same time

“Can one PC run only one instance of the code?”

Each PC needs its own copy.

You’ll need a PC with workbook containing the code. Maybe that workbook is just a copy of “the workbook” that you’re using now. And you multiply that by 9. Each one does their unique subset of the 5000, since each workbook contains the data to do scenarios. Each one sends to the mainframe and waits OIA.XStatus until its done. That would be the brute force approch.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Using VBA to work with two Attachmate sessions at the same time

@d3sdinova,

Did you make any progress on this problem?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close