Can I export data from protected worksheets?

Avatar
Written by Julien | Updated

Short answer

You can, but only if the applied protection mode lets programs modify the worksheet.

Long answer

While you can copy/paste tables from protected worksheets, you may not be able to export using UpSlide.

It is necessary for UpSlide to store information about the export in the worksheet to be able to retrieve the source later on.

If you simply protect the sheet through the user interface as shown below (Review > Protect sheet), the export will fail.

The good news is that Excel provides the option to let programs modify worksheets while preventing manual modifications. However, this option cannot be set manually and some VBA code must be used.

Simply run the following macro on the sheet you need to protect and UpSlide will be able to export/update tables and charts.

Public Sub ExportCompatibleProtection()

Dim sh As Worksheet
Set sh = Application.ActiveSheet
sh.Protect Password:="MyPassword", DrawingObjects:=False, UserInterfaceOnly:=True

End Sub 

Important
In the section of code above, we added the "DrawingObjects" option. This corresponds to the "Edit Objects" in the protect sheet form. This is necessary to export and update charts, as they could not be selected otherwise.