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?
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
Some of you problem may be the method you use to wait for the mainframe to respond.
Please post your code.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Using VBA to work with two Attachmate sessions at the same time
'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
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,
for a NUance!
RE: Using VBA to work with two Attachmate sessions at the same time
CODE
CODE
Alternatively, rather than row by row, you could clear the entire data area ONE TIME, Range(Rows(2), Rows(5001))
CODE
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,
Just traded in my OLD subtlety...
for a NUance!
RE: Using VBA to work with two Attachmate sessions at the same time
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
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,
Just traded in my OLD subtlety...
for a NUance!
RE: Using VBA to work with two Attachmate sessions at the same time
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
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,
Just traded in my OLD subtlety...
for a NUance!
RE: Using VBA to work with two Attachmate sessions at the same time
Did you make any progress on this problem?
Skip,
Just traded in my OLD subtlety...
for a NUance!