MS Excel, what are macros capable of or thanks to Bill from the teachers?

The idea to use MS Excel in training originated long ago. On forums with similar topics on VBA programming, you meet the mention that Macros are all powerful. Is it so?

A warning
This article will be useless, not interesting, repugnant and incomprehensible to more than 99.9999999% of the inhabitants of the planet earth including Bill himself, as well as the majority of teachers in any educational institution.

Formulation of the problem

As is known, the Bologna process is marching along Russian alma mater by leaps and bounds. And many have already switched to the point-rating system (BRS) of knowledge assessment ( http://study.engr.pfu.edu.ru/ ). And this additionally imposes additional labor service on teachers: keep records of performance and constantly publish data on the Internet.

Moreover, the former was present even before the BRS, but the latter requires the constant scoring and the meticulous introduction of a large array of data for each slave subject. And in the discipline there may be several streams, 60 people easily. Ie every month an average of 100 entries are put on the Internet by each employee. It takes this "pleasure" for everyone in different ways. For example, I have about an hour and another half to check. And this is not only in institutions. In schools, the tendency to know the assessments of their children online is also visible. Of course, this is not paid. Let's try to automate this process.
')

Excel comes to the rescue

Bill, although they don’t like you, but from teachers who use the hacked version of Windows and Office, you are THANK YOU so much!

As can be seen from the picture, each student can find his place in the cell in the table of ranks. At the same time, even without macros, the earned points are calculated by the machine gun.

It remains to write a piece of code that publishes all this mess on the site. Briefly talk about how to achieve our goal.
To publish, you must first parse the html code of the destination page and determine in which field this or that student is located.

Get html
Function JabberGET(ByVal Urlka As String) As String
Dim objHTTP As Object
Set objHTTP = CreateObject("MSXML2.XMLHTTP")
objHTTP.Open "GET", Urlka, False
objHTTP.setRequestHeader "Accept", "text/html"
objHTTP.setRequestHeader "Accept-Language", "ru-ru,ru"
objHTTP.setRequestHeader "Accept-Encoding", "gzip,deflate"
objHTTP.setRequestHeader "Accept-Charset", "windows-1251"
objHTTP.setRequestHeader "Keep-Alive", "115"
objHTTP.setRequestHeader "Connection", "keep-alive"
objHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
objHTTP.Send Empty
Debug.Print objHTTP.responseText
JabberGET = objHTTP.responseText
End Function


Parsing is done with regular expressions:
Dim RegEx As RegExp, RegMatch As MatchCollection
Dim myMatch As Match
Set RegEx = New RegExp
'String to parse
With RegEx
.MultiLine = True
.IgnoreCase = True
.Global = True
End With
RegEx.Pattern = "discipline_id..value=.\d{3,4}.|\s>\s.*<..>"
Set RegMatch = RegEx.Execute(MyStr)
If RegMatch.Count > 0 Then
For Each myMatch In RegMatch

Next
End If


Then we write the code tearing off Internet Explorer and following the “Next” buttons to the form we need:
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
Application.DisplayAlerts = False
url = "http://study.engr.pfu.edu.ru/index.php"
IE.Navigate "http://study.engr.pfu.edu.ru/tutor/logout.php"
While IE.Busy: DoEvents: Wend 'wait until IE is done loading page.

IE.Navigate url
Do Until IE.ReadyState = READYSTATE_COMPLETE: Loop

While IE.Busy: DoEvents: Wend 'wait until IE is done loading page.
IE.Visible = True
'ie is now done loading the page
IE.Document.all("valid_tutor_login").Value = getpass(0)
IE.Document.all("valid_tutor_password").Value = getpass(1)

Call ckickBtn("", IE)
While IE.Busy: DoEvents: Wend 'wait until IE is done loading page.
SubjCod = CoursGroupNumber(2, GroupNum)

For Each btn In IE.Document.all.tags("Input")
X = btn.Value
If btn.Value = SubjCod Or btn.Value = CourseNum Then
Call btn.Click
End If
Next btn
Call ckickBtn(">>", IE)

We click on the buttons with the following function:
Function ckickBtn(btm As String, IE As InternetExplorer)
For Each btn In IE.Document.all.tags("Input")
X = btn.Value
If btn.Value = btm Then
Call btn.Click
End If
Next btn
While IE.Busy: DoEvents: Wend 'wait until IE is done loading page.
Do Until IE.ReadyState = READYSTATE_COMPLETE: Loop 'Loop unitl ie page is fully loaded
End Function




The form is open and the data is entered on the site by the machine in accordance with the fields obtained at the parsing stage. Simple and tasteful.

Total

We list the opportunities that were achieved using macros:
• Automatic scoring of students
• Automatic publication of points on the website of the Faculty of Engineering http://study.engr.pfu.edu.ru/
• Create new worksheets with updated student lists for the next semester
• Maintain templates for new lists in accordance with the subject and schedule
• Quick filtering of students by several criteria, including by points.
• Ability to ask a question to its creator (correspondence with support)
• The program is always aware of the latest changes on the website of the Faculty of Engineering and itself updates the lists of students and subjects
• Statistics of the average score for all teachers of engineering faculty, taking into account certification and without
• Statistics of the average score for all engineering courses with and without certification

I note only that all of the above is available to any Excel user without installing any additional modules. The program does not start from * .xls, but as a * .exe file, which allows working with macros disabled on the machine, plus a backup is automatically created.

And finally, the flowchart


PS
How to quickly enter points on the website from Excel?


How to quickly create a list of students for the next semester?