Защитить лист Excel только для чтения, но разрешить обновление внешних данных

У меня есть книга Excel 2010. Один рабочий лист импортирует данные из внешнего подключения к данным (SQL-запрос). Я также добавил дополнительные столбцы на рабочий лист, чтобы выполнять вычисления с данными и немного их массировать. Рабочий лист формирует основу необработанных данных, используемых в других рабочих листах.

Я хотел бы защитить рабочий лист, чтобы он был доступен только для чтения (с возможностью сортировки, фильтрации, использования сводной таблицы). Я знаю, как это сделать с помощью функции защиты рабочего листа. Но когда рабочий лист защищен, я не могу использовать кнопку «Обновить» для обновления данных из источника, и я хочу, чтобы пользователи могли это делать. Я собирался настроить свойства соединения так, чтобы они автоматически обновлялись при открытии и разрешали обновление вручную.

Кто-нибудь нашел элегантный способ включения функции защиты рабочего листа и включения обновления внешних данных, не позволяя пользователям самостоятельно изменять значения ячеек?


excel excel-2010 protection worksheet
person Dominic    schedule 03.12.2013    source источник

Ответы (4)


arrow_upward
3
arrow_downward

Основываясь на предложении Панкаджа, я сделал следующее (хотя я не думаю, что это очень элегантно, и все же думаю, что должен быть лучший способ).

Я создал новый макрос для книги.

Sub RefreshData()
'
' RefreshData Macro
'
Application.ScreenUpdating = False
Sheets("sheetname").Unprotect Password:="password"
ActiveWorkbook.Connections("connection name").Refresh
Sheets("sheetname").Protect _
Password:="password", _
UserInterfaceOnly:=True, _
AllowFiltering:=True, _
AllowSorting:=True, _
AllowUsingPivotTables:=True
End Sub

Затем я открыл ThisWorkbook в проекте VBA и отредактировал процедуру открытия книги.

Private Sub Workbook_Open()
RefreshData
End Sub

Более подробную информацию о вариантах защиты можно найти здесь: http://datapigtechnologies.com/blog/index.php/worksheet-protection-best-practice/

Оно работает; лист блокируется каждый раз при открытии книги и выполняется обновление данных. Свойство UserInterfaceOnly не влияет на команду обновления данных (хотя это должно быть связано с другими событиями макроса). Вам все равно придется специально разблокировать электронную таблицу, выполнить обновление данных, а затем снова заблокировать лист.

Я добавил кнопку формы на один из других листов и связал ее с моим макросом RefreshData, чтобы данные можно было обновлять вручную, в то время как лист предположительно заблокирован.

Еще одно, что я сделал в свойствах подключения, - это убрал галочку напротив фонового обновления.

person Dominic    schedule 04.12.2013

arrow_upward
2
arrow_downward

Самый простой способ сделать это - добавить настраиваемую кнопку и написать макрос. Когда пользователь нажимает настраиваемую кнопку панели инструментов, макрос, стоящий за ним, снимает защиту с листа и обновляет внешние данные, а затем защищает лист (очевидно, для параметра screenupdate установлено значение false)

person Pankaj Jaju    schedule 03.12.2013

arrow_upward
1
arrow_downward

Я бы предложил добавить внешний запрос на другой лист, не защищенный, но который вы бы скрыли. Данные на защищенном листе будут просто относиться к незащищенному листу.

person Olivier    schedule 16.03.2015

arrow_upward
0
arrow_downward

Я добавил подключение к данным к некоторым незащищенным листам, затем спрятал листы. К сожалению, соединения не работают, даже если структура книги защищена.

Я не могу поверить, что мне может потребоваться выпустить свой код незащищенным, для этого должно быть промышленное решение.

person Chris    schedule 02.11.2016