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

[d3sdinova]

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?

 

[SkipVought]

Hi,

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

Please post your code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[d3sdinova]

'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

 

[SkipVought]

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

1) The main thing that I can suggest is to do some things that can make any VBA code run faster. FAQ707-4105.

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

a further suggestion.

''''''''''''''''''''''''''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 Named Clear1, then the above code could be replaced by...

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))

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[d3sdinova]

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.

 

[SkipVought]

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. 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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[d3sdinova]

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!

 

[SkipVought]

“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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

@d3sdinova,

Did you make any progress on this problem?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]