Jaap Stelpstra

Download data to Excel using VBA and the API (workbook with code attached)

Blog Post created by Jaap Stelpstra on May 16, 2016

Introduction

There are several ways to get data from Canvas in the form of downloading csv files. In most cases you want to apply some analysis of that data, which may require importing it into excel, sorting and filtering data and create formulas and graphs to create some sort of a dashboard. This is all great, but updating it periodically may require quite some manual work each time. Additionally this may be too difficult and time consuming for teachers and ta’s.

 

Instead of working with csv files, downloading data directly from Canvas into Excel with a click of a button can be a huge advantage. Let’s say students need to complete a course with modules as a prerequisite for another course, your teachers may want to periodically check their progress in these modules.  In this case a VBA enabled Excel workbook template like Automated progress report of students in modules comes in handy. And because the Canvas API is so extensive, many types of similar reports can be created using VBA in Excel. You can even build a template and send it to your teachers and ta’s, so the only thing they have to do is to click a button to update the data (without compromising the security of the API key).

 

Attached macro enabled Excel workbook contains the VBA code to download data using the Canvas API. You can set your Canvas url and API key in a secure way and it enables you to download a list of students in a course. The purpose of this workbook is to offer you the ability to extend the code to download other course and student related data as documented in http://api.instructure.com as you see fit.

 

CanvasApiReportBase-main2.png

CanvasApiReportBase-comments.pngData of the students are populated in the left two columns and the remaining available data is added in the cell comments.

 

It is up to you to add code to populate the areas where the headers and the data need to go.

 

CanvasApiReportBase-settings.pngTo set your Canvas url and the API key, click on the Settings button and fill in the required fields.

 

This data is stored in the hidden Settings sheet, which can only be made visible using VBA code (see https://support.microsoft.com/en-us/kb/142530). This is why protecting access to the code is critical.

 

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)

 

For it all to work, make sure you enable editing as well as macros:

VbaEnableEditing.png

VbaEnableMacroContent.png

Protect your API token by protecting the code

When using this workbook it is imperative that you change the password to the code immediately after downloading it, as any of your users will be able to find this document containing the default password I put on the code.

 

Because the digital signing used on Windows versions of Office is not compatible with the Mac versions of Office, you won't be able to unlock the Visual Basic Editor on a Mac, because the VBA password is set in the Windows version of Excel. Should you want to develop on a Mac, then remove the password in Excel on Windows, save it, open it in your Mac and set a new password the code, so your API token stored in the Settings sheet is protected.

To access the Visual Basic Editor please check:

VBAProjectPassword.png

 

When trying to open the content from the project explorer, enter the given password. If you did not change it yet, enter “REPLACEME”.

 

In Windows, change this password via the menu Tools – VBAProject Properties, click OK and save the workbook.

 

VBAmenuVBAProjectProperties.png  VBAProjectProperties.png

 

The code

The module CanvasApiReportBase contains the main code for downloading data from Canvas and populating the sheet with content. The form provides input for the required settings.

 

CanvasApiReportBase-VBAProject.png

CanvasApiReportBase content:

 

Sub or function

Purpose

CleanUpSheet

To clean up data the sheet so it looks nice and tidy before you send it to your colleague. Meant to run manually from available macros via the Developer tab.

ClearSettings

To delete the Canvas url and the token and reset the options for the desired content of the first and second column in the sheet. Meant to run manually from available macros via the Developer tab.

EditSettings

To show the settings form, only available if no data is being downloaded

LoadSettings

To load all settings in variables before downloading is possible. Each time the Download data is clicked this function is executed.

ValidateSettings

To validate the entered settings, if it returns false it will indicate that downloading data from Canvas will fail.

GetReportAllEnrolments

The main sub to download data from Canvas.

GetCourseName

Performs API call to get the course name associated by the course id. It is also the first call performed, if it fails it indicates a connection error

GetUserSelf

Get the name of the user associated with the token used in settings

GetAllActiveStudentEnrolments

Get all students in a course and populate it in a collection

RequestAbortDownload

Prompts to stop processing any further. Will run when the Download data from Canvas button is clicked while running a report.

UpdateDownloadProgress

Updates the percentage of the download progress in cell A3

ResponseStatusErrorMsg

Displays a message in case of any connection errors

DebugPrintDictColl

Recursive function to iterate through all items from the Response.Data object. For debugging purposes.

 

The code depends on the library VBA-WEB created by Tim Hall which offers the API and JSON support in Excel for Windows and Mac. These are the Web* and *Authenticator (Class) Modules in the project. Documentation and downloads can be found at http://vba-tools.github.io/VBA-Web/docs/

 

Tips

  • Prevent Excel from automatically changing numbers such as SIS-ID’s and other numbers which don’t need calculating by e.g. formatting cells as text (instead of numeric or general) using NumberFormat = "@"

  • Format cells before populating data and do that from VBA instead of manually in Excel (see http://www.excelhowto.com/macros/formatting-a-range-of-cells-in-excel-vba/)

  • Preserve Mac compatibility as much as possible. Mac OS X does not support Active-X, but there are plenty of alternative solutions (see http://www.rondebruin.nl/mac.htm)

  • Use the Live API to easily test the API responses before writing your code. Check yourinstitution.instructure.com/doc/api/live

  • Please consider sharing your creations. Like you, I am also a Canvas user who also learns a lot from other members in the Canvas Community. Your contributions are much appreciated!

Outcomes