Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Защита листов и PQ - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Защита листов и PQ
mikaelw Дата: Вторник, 04.07.2023, 13:07 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 179
Репутация: 1 ±
Замечаний: 20% ±

Excel 2010, 2013, 2016
Всем привет!
Макросы для меня что-то дастаточно мутное и далекое, но если не пробывать, то зачем что-то делать.

Суть: Есть книга в которой листы, некоторые листы нужно защитить от шаловлтивых ручек юзерят

нашел https://www.planetaexcel.ru/techniques/5/64/

Использовал макрос, как указано в статье:

[vba]
Код
Private Sub Workbook_Open()
Worksheets("Reestr_Документ").Protect Password:="123", UserInterfaceOnly:=True
End Sub
[/code][/vba]

В Книге есть 2 макроса:
1. Печать сразу на принтер, с настройками - он выполняется на ура;
2. А вот с этим проблема(он не хочет выполнять), он перезаполняет таблицу через PQ:

[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G1")) Is Nothing Then
    Select Case Target
        Case Else: Call Обновить
    End Select
End If
End Sub
[/code][/vba]

[vba]
Код
Sub Обновить()
'
    ActiveWorkbook.RefreshAll

End Sub
[/vba]

Заранее спасибо!

P.S. Извините за отсутствие примера файла, как сделать пример не пойму на угад, может следующим сообщением выложу.


Сообщение отредактировал mikaelw - Вторник, 04.07.2023, 13:08
 
Ответить
СообщениеВсем привет!
Макросы для меня что-то дастаточно мутное и далекое, но если не пробывать, то зачем что-то делать.

Суть: Есть книга в которой листы, некоторые листы нужно защитить от шаловлтивых ручек юзерят

нашел https://www.planetaexcel.ru/techniques/5/64/

Использовал макрос, как указано в статье:

[vba]
Код
Private Sub Workbook_Open()
Worksheets("Reestr_Документ").Protect Password:="123", UserInterfaceOnly:=True
End Sub
[/code][/vba]

В Книге есть 2 макроса:
1. Печать сразу на принтер, с настройками - он выполняется на ура;
2. А вот с этим проблема(он не хочет выполнять), он перезаполняет таблицу через PQ:

[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G1")) Is Nothing Then
    Select Case Target
        Case Else: Call Обновить
    End Select
End If
End Sub
[/code][/vba]

[vba]
Код
Sub Обновить()
'
    ActiveWorkbook.RefreshAll

End Sub
[/vba]

Заранее спасибо!

P.S. Извините за отсутствие примера файла, как сделать пример не пойму на угад, может следующим сообщением выложу.

Автор - mikaelw
Дата добавления - 04.07.2023 в 13:07
Serge_007 Дата: Вторник, 04.07.2023, 14:36 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Здравствуйте

Запросы PQ обновляются с помощью [vba]
Код
ActiveWorkbook.Connections("Запрос — Таблица1").Refresh
[/vba]


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеЗдравствуйте

Запросы PQ обновляются с помощью [vba]
Код
ActiveWorkbook.Connections("Запрос — Таблица1").Refresh
[/vba]

Автор - Serge_007
Дата добавления - 04.07.2023 в 14:36
mikaelw Дата: Вторник, 04.07.2023, 14:47 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 179
Репутация: 1 ±
Замечаний: 20% ±

Excel 2010, 2013, 2016
А чтобы все запросы обновились?


Сообщение отредактировал Serge_007 - Вторник, 04.07.2023, 15:26
 
Ответить
СообщениеА чтобы все запросы обновились?

Автор - mikaelw
Дата добавления - 04.07.2023 в 14:47
Serge_007 Дата: Вторник, 04.07.2023, 15:26 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
[vba]
Код
Sub RefreshCon()
    Dim Con As WorkbookConnection
        For Each Con In ThisWorkbook.Connections
            Connection.Refresh
        Next Con
End Sub
[/vba]


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение[vba]
Код
Sub RefreshCon()
    Dim Con As WorkbookConnection
        For Each Con In ThisWorkbook.Connections
            Connection.Refresh
        Next Con
End Sub
[/vba]

Автор - Serge_007
Дата добавления - 04.07.2023 в 15:26
mikaelw Дата: Вторник, 04.07.2023, 16:09 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 179
Репутация: 1 ±
Замечаний: 20% ±

Excel 2010, 2013, 2016
Проблему это не решает!

"ячейка или диаграмма которую вы пытаетесь изменить находится на защищенном листе...."
 
Ответить
СообщениеПроблему это не решает!

"ячейка или диаграмма которую вы пытаетесь изменить находится на защищенном листе...."

Автор - mikaelw
Дата добавления - 04.07.2023 в 16:09
Serge_007 Дата: Вторник, 04.07.2023, 16:19 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Так получается, что у Вас проблема не с обновлением запроса PQ, а со снятием пароля защиты листа

В этом макросе
[vba]
Код
Private Sub Workbook_Open()
   Worksheets("Reestr_Документ").Protect Password:="123", UserInterfaceOnly:=True
End Sub
[/vba]указаны имя листа для снятия пароля и сам пароль
Проверьте что бы пароль 123 и имя Reestr_Документ совпадали с тем, что у Вас в файле и что другие листы с запросами не запаролены

[p.s.]Вот для чего нужны файлы-примеры ;) [/p.s.]


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеТак получается, что у Вас проблема не с обновлением запроса PQ, а со снятием пароля защиты листа

В этом макросе
[vba]
Код
Private Sub Workbook_Open()
   Worksheets("Reestr_Документ").Protect Password:="123", UserInterfaceOnly:=True
End Sub
[/vba]указаны имя листа для снятия пароля и сам пароль
Проверьте что бы пароль 123 и имя Reestr_Документ совпадали с тем, что у Вас в файле и что другие листы с запросами не запаролены

[p.s.]Вот для чего нужны файлы-примеры ;) [/p.s.]

Автор - Serge_007
Дата добавления - 04.07.2023 в 16:19
mikaelw Дата: Вторник, 04.07.2023, 16:38 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 179
Репутация: 1 ±
Замечаний: 20% ±

Excel 2010, 2013, 2016
Я написал макрос так:

[vba]
Код

Sub Обновить()
'
    ActiveSheet.Unprotect Password:="123"

                 ActiveWorkbook.RefreshAll

           ActiveSheet.Protect Password:="123"

End Sub
[/vba]

В таком виде он выдает ошибку: "ячейка или диаграмма которую вы пытаетесь изменить находится на защищенном листе..."

Если делаем так: [vba]
Код

Sub Обновить()
'
    ActiveSheet.Unprotect Password:="123"

                 ActiveWorkbook.RefreshAll

  '         ActiveSheet.Protect Password:="123" - отключаем

End Sub
[/vba]

Все работает, но лист тогда не защищен!

Надо пример!
Через 2 часа выложу!


Сообщение отредактировал mikaelw - Вторник, 04.07.2023, 16:38
 
Ответить
СообщениеЯ написал макрос так:

[vba]
Код

Sub Обновить()
'
    ActiveSheet.Unprotect Password:="123"

                 ActiveWorkbook.RefreshAll

           ActiveSheet.Protect Password:="123"

End Sub
[/vba]

В таком виде он выдает ошибку: "ячейка или диаграмма которую вы пытаетесь изменить находится на защищенном листе..."

Если делаем так: [vba]
Код

Sub Обновить()
'
    ActiveSheet.Unprotect Password:="123"

                 ActiveWorkbook.RefreshAll

  '         ActiveSheet.Protect Password:="123" - отключаем

End Sub
[/vba]

Все работает, но лист тогда не защищен!

Надо пример!
Через 2 часа выложу!

Автор - mikaelw
Дата добавления - 04.07.2023 в 16:38
mikaelw Дата: Вторник, 04.07.2023, 19:48 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 179
Репутация: 1 ±
Замечаний: 20% ±

Excel 2010, 2013, 2016
ПРимер.

Жамкаем по кнопке!
или в ручную запускаем!!!

Если из кода убираешь снятие защиты, то он просто не обновляет таблицу!
К сообщению приложен файл: Test.xlsm (113.4 Kb)


Сообщение отредактировал mikaelw - Вторник, 04.07.2023, 19:51
 
Ответить
СообщениеПРимер.

Жамкаем по кнопке!
или в ручную запускаем!!!

Если из кода убираешь снятие защиты, то он просто не обновляет таблицу!

Автор - mikaelw
Дата добавления - 04.07.2023 в 19:48
Serge_007 Дата: Среда, 05.07.2023, 09:32 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
проблема не с обновлением запроса PQ
После того, как был приложен пример, стало ясно, что проблема всё же с запросом PQ:


Однако мой Excel не поддерживает данную версию:
К сообщению приложен файл: 0160430.png (5.7 Kb) · 0735460.png (14.4 Kb)


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
проблема не с обновлением запроса PQ
После того, как был приложен пример, стало ясно, что проблема всё же с запросом PQ:


Однако мой Excel не поддерживает данную версию:

Автор - Serge_007
Дата добавления - 05.07.2023 в 09:32
mikaelw Дата: Среда, 05.07.2023, 13:35 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 179
Репутация: 1 ±
Замечаний: 20% ±

Excel 2010, 2013, 2016
что проблема всё же с запросом PQ
Решил вопрос другим путем.
Нет работает, поэтому пример и приложить была проблема.
Сделал проще, при обнововлении снимаю полностью защиту с листа, а дальше при попытке изменения любой ячекий уставливаю защиты заново и все теперь работает!


Сообщение отредактировал Serge_007 - Среда, 05.07.2023, 13:37
 
Ответить
Сообщение
что проблема всё же с запросом PQ
Решил вопрос другим путем.
Нет работает, поэтому пример и приложить была проблема.
Сделал проще, при обнововлении снимаю полностью защиту с листа, а дальше при попытке изменения любой ячекий уставливаю защиты заново и все теперь работает!

Автор - mikaelw
Дата добавления - 05.07.2023 в 13:35
прохожий2019 Дата: Четверг, 06.07.2023, 16:23 | Сообщение № 11
Группа: Проверенные
Ранг: Старожил
Сообщений: 1264
Репутация: 322 ±
Замечаний: 0% ±

365 Beta Channel
Как бы нет там проблемы с PQ от слова совсем
Да и с VBA нет, почти... Просто refreshall запускает обновление запросов и на лист сразу ставится защита, а на защищённый лист результат уже не выгружается.
Поэтому надо запустить обновление, дождаться его окончания и потом обратно поставить защиту:
[vba]
Код
Sub Обновить()
    ActiveSheet.Unprotect Password:="123"
    For Each conn In ActiveWorkbook.Connections
    With conn.OLEDBConnection
        tmp = .BackgroundQuery
        .BackgroundQuery = False
        .Refresh
        .BackgroundQuery = tmp
    End With
    Next
    
    ActiveSheet.Protect Password:="123"
End Sub
[/vba]
ну и дальше можно обновлять не все, а только выгружаемые на лист и т.п. - можете посмотреть код этой надстройки - Мерка
К сообщению приложен файл: 3801731.xlsm (107.3 Kb)


Сообщение отредактировал прохожий2019 - Четверг, 06.07.2023, 16:29
 
Ответить
СообщениеКак бы нет там проблемы с PQ от слова совсем
Да и с VBA нет, почти... Просто refreshall запускает обновление запросов и на лист сразу ставится защита, а на защищённый лист результат уже не выгружается.
Поэтому надо запустить обновление, дождаться его окончания и потом обратно поставить защиту:
[vba]
Код
Sub Обновить()
    ActiveSheet.Unprotect Password:="123"
    For Each conn In ActiveWorkbook.Connections
    With conn.OLEDBConnection
        tmp = .BackgroundQuery
        .BackgroundQuery = False
        .Refresh
        .BackgroundQuery = tmp
    End With
    Next
    
    ActiveSheet.Protect Password:="123"
End Sub
[/vba]
ну и дальше можно обновлять не все, а только выгружаемые на лист и т.п. - можете посмотреть код этой надстройки - Мерка

Автор - прохожий2019
Дата добавления - 06.07.2023 в 16:23
mikaelw Дата: Пятница, 07.07.2023, 13:11 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 179
Репутация: 1 ±
Замечаний: 20% ±

Excel 2010, 2013, 2016
Цитата прохожий2019, 06.07.2023 в 16:23, в сообщении № 11 ()
Просто refreshall запускает обновление запросов и на лист сразу ставится защита, а на защищённый лист результат уже не выгружается


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

P.S. Мне нужно именно полное обновление всех связей и таблиц!
 
Ответить
Сообщение
Цитата прохожий2019, 06.07.2023 в 16:23, в сообщении № 11 ()
Просто refreshall запускает обновление запросов и на лист сразу ставится защита, а на защищённый лист результат уже не выгружается


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

P.S. Мне нужно именно полное обновление всех связей и таблиц!

Автор - mikaelw
Дата добавления - 07.07.2023 в 13:11
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!