Stream Transcripts (Updated)

code-1839406_640It was recently brought to my attention that the transcript files in Steam had changed and therefore the code I previously created for extracting the text from these files no longer works (You can read my original posting and code here).     As such I had another look and updated the code so that it would work with the new format.

The issue was that the new format includes additional lines of data which I needed to strip out plus also supports double and single line groups of text.    It didn’t take too long to write a new macro which would support this new format.

You can see the new Macro code below:

Sub Macro1()

Dim introw As Integer
Dim intcount As Integer

‘Delete first 10 rows
For intcount = 1 To 5

introw = 1
Do While Cells(introw + 1, 1).Value <> “”

‘ delete the five rows preceeding text
For intcount = 1 To 5

‘ deal with blocks of 2 or 1 line of text
If Cells(introw + 3, 1).Value <> “” Then
introw = introw + 2
introw = introw + 1
End If


End Sub

If using the above take care in the way that WordPress converts the minus ( – ) character in my code to a similar looking character in the above.   As such you may get a syntax error if copying and pasting.  If so just delete and replace the minus with the correct character in your code.  If you have any other issues with the above please let me know.



Author: garyhenderson2014

Gary Henderson is currently the Director of IT in an Independent school in the UK. Prior to this he worked as the Head of Learning Technologies working with public and private schools across the Middle East. This includes leading the planning and development of IT within a number of new schools opening in the UAE. As a trained teacher with over 15 years working in education his experience includes UK state secondary schools, further education and higher education, as well as experience of various international schools teaching various curricula. This has led him to present at a number of educational conferences in the UK and Middle East.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: