Automated progress report of students in modules

[stelpstra]

Attached Excel workbook is able to download data from Canvas (using Get API calls) to create a progress report of all students in a course with modules to which requirements are added (see How do I add requirements to a module?).

(no data means not yet downloaded while download is in progress)

Compatibility

• MS Office 2010 on MS Windows (higher versions work most likely, but are not tested)
• MS Office 2011 on Mac OS X (version 2016 for Mac barely supports VBA and is therefore not supported)

Enable editing and content

In case Excel displays the following warnings, enable editing and content for the Workbook to work properly:

Settings

First fill in the settings:

• the Canvas url of your institution (e.g. https://yourinstitution.instructure.com)
• an API access token (check How do I obtain an API access token for an account?)
  
• select the desired content for column A and column B
• click Save

Run progress report

To run a progress report:

• find the course ID of the course from which the report needs to be generated in the address bar
  
• fill in the Course ID in the worksheet at cell B2 (e.g. 210)
  
• click the button Download data from Canvas to create the progress report

Depending on the amount of students and module items this may take a some time (about half a second per student).

In the top-left corner the download progress is displayed in percentages.

To cancel the download progress click the button Download data from Canvas again (only while download progress is less than 100%, otherwise downloading would start again).

Versions history:

• 1.11 (July 29, 2020)
  • Updated the processing of API pagination (Instructure changed the method regarding the enrolments API) to accommodate for both the old and the new method.
  • Changed some Excel formulas to prevent #DIV/0! from being displayed
• 1.13 (November 9, 2022)
  • Added a row "Requirement" below the module item names
  • If a module item has no requirement, the completion information per student stays empty and a comment is added for explanation
    (note: When no requirements are configured for a module, Canvas assumes progress as Complete, whether students interacted with the content or not at all)
• 1.14 (July 16, 2024)
  • Updated code related to pagination to accommodate for the change as indicated at https://community.canvaslms.com/t5/The-Product-Blog/Forthcoming-Update-to-Canvas-API-Response-Header...
• 1.15 (August 11, 2025)
  • Bugfix: When assignments are present in a module, when downloading progress by a student who is not assigned to that assignment, then the text "unassigned" will be displayed in the relevant cell and the error message "An error occurred - Invalid procedure call or argument" will not be displayed anymore nor stop the script

Performance

In case you experience extremely slow performance (a minute per row or so) to the point that Excel is freezing, then make sure the Windows Defender Antimalware Client version is at least 4.18.2102.4. You can check the version on your device following this guide How to Find the Microsoft Defender Version Installed in Windows 10.

Remarks

• Disclaimer: use this workbook at your own risk (I cannot be held responsible for any undesired consequences). This Excel workbook only reads from Canvas, never writes to it and should not have any negative effect on Canvas in terms of integrity or performance.
• First try it on your test environment https://yourinstitution.test.instructure.com (slow performance is expected and may cause time-out errors) before using it on production
• Check samples from reports with actual data in Canvas or in case your student(s) get accounted for their activities in modules as Canvas is the most reliable source
• Suggestion: save the workbook as a template (xltm file) after entering the settings so you can always start with a fresh and clean workbook so you will be prompted to save it. Keep in mind that if you don't save the file in a macro-enabled workbook format (xlsm), running the report again will not work any more as it requires the macro functionality to run.
• I created this workbook largely in my own spare time (for fun, to learn, to share and because I can), so huge amounts of support can't be expected
• Leave your comments in case you are using it, if it makes your work easier or more fun

Download

Download the latest version of this file from this Google Drive folder.

Do not open in Google Docs nor in MS Office online, because that will not work, just first save the file and then open it in the Microsoft Excel application.

Additional tools

• Automated report of assignments in courses 
• Automated report of group sets, groups and members in a course 
• Create course groups from sections using Excel 
• Download data to Excel using VBA and the API (workbook with code attached) 

[CarolBuehrens]

This worked wonderfully! Thank you SO MUCH for fixing this! You are AWESOME!

[eglover1]

I've been trying to run this report using the most recent update you've provided. I'm using Microsoft Excel for Mac Version 16.88 (24081116). The report stops loading out at 4% with the error message "Application-defined or object-defined error". I can see the assignments and the name of one student in the class, but no info about assignment completion loads. The other students are listed as "Student 2", Student 3", etc. Any suggestions?

[stelpstra]

Hi @eglover1, from https://learn.microsoft.com/en-us/officeupdates/update-history-office-for-mac I see that your version is the latest Excel version for Mac. Microsoft has ended support for al Excel for Mac versions after version "2016" (meaning not the year, but the software version). See information at "Compatibility" in the blogpost. Your only option to use this report is to install version "2016" on your Mac, or the latest Excel version on a Windows computer / laptop. It won't work on the Excel version you have installed unfortunately.

[CarolBuehrens]

Hi Jaap! Is it possible to hire you? We would like section name and email added to the progress report. Thank you in advance, and for all you do for this community! 

[stelpstra]

Hi @CarolBuehrens, thanks for your question. I work at a university and not independent unfortunately. So I have no options to be hired. Additionally, support for VBA in Office products is becoming within educational institutions, therefore this tool is not a sustainable solutions any more, which is also one of the reasons I don't develop new functionalities in it any more.

The workaround that may work for you is to get the section data and email addresses separately, import that into a new tab in the Excel file and use vlookup functionality in Excel using Index Match functions (see https://exceljet.net/formulas/index-and-match-exact-match) to supplement the module progress data. I hope that helps.

[MatthewCollins]

I think Im experiencing the same issue as eglover1 above, where it stops on 4% with the first students name and id, progress data from perhaps one item, for that one student then fails.

But I am on Windows on the latest version of excel. (Office 365, but the local client, not attempting to use online)

The error message I get as it fails is

An error occurred
Invalid procedure call or argument

It worked perfectly on the same Canvas course a few months ago, but attempted to run this evening for resit data and it fell over.

There seem to have been quite a few updates recently in July and 1 a couple of days ago in August according to the release notes section of office 365 https://learn.microsoft.com/en-gb/officeupdates/current-channel 

[stelpstra]

Hi @MatthewCollins, thanks for pointing it out. I found the cause of the problem, fixed it and added version 1.15

When assignments are present in a module, when downloading progress by a student who is not assigned to that assignment, then the text "unassigned" will be displayed in the relevant cell and the error message "An error occurred - Invalid procedure call or argument" will not be displayed anymore nor stop the script.

It should solve your issue, if that is not the case, then please sent me a PM with the details of your course dynamics so I research further together with you.

[dbrace]

@stelpstra, this is a great resource. Thanks for making it available to everyone. I had not heard of it until I enabled notifications to the "Higher Ed Users" group and saw your reply from earlier today.

I do have a question. I thought the lastest versions of Microsoft Excel for Mac (including what comes with the downloaded and installed version of Microsoft Office 365) do support VBA. Without getting too technical, what is the reason this is not compatible with the latest versions of Microsoft Excel for Mac?

Just to share my brief experience with it on a Mac:

1. I was able to open the file and enable macros
2. I was able to click on "Settings" button
3. I was able to complete the form (although, I did have to manually type in my URL and API token [that was not fun] because copy and paste did not seem to work)
4. I was able to click on "Save" to close "Settings"
5. I was able to type in a course number
6. I was able to click on the "Download Data from Canvas" button
7. the process started and stopped, giving me an error (see the screenshot at the bottom)
8. it was able to display some course and student information (see the redacted screenshots at the bottom)

If it is not compatible (or worth the time and resources to research and make it compatible), I completely understand and I am just curious.

Thanks again or your work on this!

-Doug

P.S. I do have access to Windows computers so this resource is still an option for me.

 

 

[stelpstra]

Hi Douglas (@dbrace), nice that you like the resource 🙂

Years ago when I was creating and updating this Excel workbook I did a lot of research to make sure I used functions that were not Windows specific so that it would be Mac compatible. But information on VBA compatibility on Mac regarding specific functions are not present in abundance, so back then I even built virtual environments running Mac OS versions running different versions of Excel to test it with. In the early stages of M365 for Mac it seemed most VBA functions were left out and many Mac users complained about that. MS may have added lots of functions again, but I stopped developing in VBA anyway, as it is not a future proof solution. I only fix bugs once people find them. I'm offering and supporting my Excel Workbooks for free without any ROI (other than appreciation and helpful suggestions for improvements) for many years already, but I don't want to spend tons of time anymore into figuring out all the changes and obstacles that MS created as there were so many. I'd rather rebuild it using python or another future proof language, maybe someday I will.

Regarding your case that the script breaks, I don't have any options anymore to debug where it breaks or what is causing it, it could very well be that a specific function has not yet been added to the Mac version of VBA. Resorting to Windows computers is your best bet then I guess.

[dbrace]

Thank you, @stelpstra!

-Doug