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?