LETU IT KnowledgeBase

Child pages
  • Workaround for saving a Canvas-exported CSV gradebook file as UTF-8 type
Skip to end of metadata
Go to start of metadata

This Excel macro is a workaround for saving a Canvas-exported CSV gradebook file as UTF-8 type, which avoids corrupting the file as would be otherwise done by the default Excel Save function.


Sub CSV_Super_Saver()

'

' CSV_Super_Saver Macro

' This macro is a workaround for saving a Canvas-exported CSV gradebook file as UTF-8 type,

' which avoids corrupting the file as would be otherwise done by the default Excel Save function.

'

' Keyboard Shortcut: Ctrl+Shift+X <-- one of the few shortcuts not used by MS Office

'

' by Joel Koblich 12FEB2018

' In the public domain.  Should work fine, but if not, please feel free to contact me and I'll try to help.

'

' References:

'  https://support.office.com/en-us/article/copy-your-macros-to-a-personal-macro-workbook-aa439b90-f836-4381-97f0-6e4c3f5ee566

'  https://stackoverflow.com/questions/1895616/how-to-get-the-excel-file-name-path-in-vba

'  https://support.microsoft.com/en-us/help/290938/keyboard-shortcuts-for-microsoft-word'

'  https://support.microsoft.com/en-us/help/213428/how-to-suppress-save-changes-prompt-when-you-close-a-workbook-in-excel

'

' To install:

'  1) On Excel Developer tab, click Record Macro,

'  2) Name the new macro CSV_Super_Saver,

'  3) To set the Shortcut key, select that field and press Shift + X,

'  4) For the Store macro in parameter, select Personal Macro Workbook option in dropdownlist

'       (this stores it with that installation of Excel rather than any particular workbook),

'  5) Stop Recording the macro,

'  6) Press Alt+F11 to launch the VBA Script Editor,

'  7) In the left window, expand the VBAProject(PERSONAL.XLSB) folder,

'  8) Within that folder, expand the Modules folder,

'  9) Double click the Module1 object.  If that does not contain a line "Sub CSV_Super_Saver()" then look around some more.

' 10) Copy the entire contents of this instruction set in place of all text beginning immediately AFTER the "Sub CSV_Super_Saver()" text

'      and everything up to but NOT including "End Sub".  Do not overwrite any other existing Sub items that may already be in the module!

' To use:

'  1) Export file from Canvas,

'  2) Open file in Excel,

'  3) Ctrl+Shift+X to run the macro and save the file as type UTF-8, which should make it non-corrupt next time it's opened.

 

Dim FileNameWithPath, FileOnly, PathOnly As String

 

Application.DisplayAlerts = False  ' Suppress the save confirmation popup message box

FileNameWithPath = ActiveWorkbook.FullName

FileOnly = ActiveWorkbook.Name

PathOnly = Left(FileNameWithPath, Len(FileNameWithPath) - Len(FileOnly))

    ChDir PathOnly

    ActiveWorkbook.SaveAs Filename:=FileNameWithPath, FileFormat:=xlCSVUTF8, CreateBackup:=False

Application.DisplayAlerts = True    ' turn alerts back on

 

End Sub


  • No labels