Can I export data from protected worksheets?

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 might not be able to export using UpSlide.

It is indeed 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 though the user interface (Review\Protect sheet, see picture below), the export will fail.

Good news is: Excel provides the option to let programs modify worksheets while preventing manual modifications. Sadly, this option cannot be set manually and a bit of VBA code must be used.

Simply run this 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

Note: In the piece 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. 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request


Please sign in to leave a comment.
Powered by Zendesk