Microsoft Forms and Live Spreadsheets

I used to use Google Forms and as such when Microsoft introduced Forms I was very excited about being able to use the Office 365 platform for my form based needs however I quickly found one big limitation.

In google I could create a form which for example gathered budget requests.  I could then make this available to all staff.    I could then create a spreadsheet into which the data went but into which I then started to add my own comments regarding each request indicating whether I felt they should be accepted, declined or needed further discussion.  This spreadsheet was then shared with other budget decision makers for their comments.    As such the budget decision makers were able to make notes on a live spreadsheet which instantly updated with new requests as and when they were submitted.    It sped the whole process up significantly.     The issue was the in Microsoft Forms when you accessed the data in a spreadsheet format this was via download facilities meaning the resulting data spreadsheet did not update as new requests came in, and therefore couldn’t be used to keep running notes.

Thankfully, thanks to twitter and my PLN, I have now found that Microsoft have built the exact functionality I need, it is simply that the process is a little different.

Now I need to create a spreadsheet which can be stored in either OneDrive, Teams or Sharepoint.    The spreadsheet just needs to be blank.     Once the spreadsheet has been created you then need to open it up in Excel Online, the web version of Excel rather than the desktop application.    Easiest method to do this is to visit Office.com in a web browser and open the file from the appropriate OneDrive, Teams or SharePoint app.

Once in Excel Online simply go to Insert > Form and select the New Form option.

You should now be presented with the familiar MS Forms interface where you can build the form you want, adding whatever questions you need.     Once you have your form completed try the Preview option, enter and submit some sample data.    Now revisit your online spreadsheet and the data you have entered should now be visible.   You have now created a live spreadsheet which updates as data is submitted via the form.   You can also easily add your own additional comments to the spreadsheet as needed.

For me Microsoft Forms has now become a much better resource and I am looking forward to sharing the above info with colleagues over the coming week so that they too may start making greater use of Forms.

 

Stream Transcripts (Updated)

It 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
Rows(1).EntireRow.Delete
Next

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

‘ delete the five rows preceeding text
For intcount = 1 To 5
Rows(introw).EntireRow.Delete
Next

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

Loop

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.