Excel vba удаление листов в зависимости от даты


Содержание

Excel VBA — удаление дубликатов и сохранение последних (на основе столбца даты)

Может кто-нибудь мне помочь? Я даже не знаю, как начать. Я хочу создать макрос, который удаляет дубликаты (на основе столбца A) и сохраняет строку с последней датой (столбец P). И если все дубликаты не имеют даты в столбце P, просто сохраните один и удалите другие дубликаты.

Данные на листе начинаются с строки 5 (а не строки 4, как на картинке, извините за это). Раньше я знал, что у меня возникли проблемы с удалением дубликатов с помощью макроса, когда таблица не начинается с строк 1 или 2.

Обычно таблица имеет около

Некоторые строки имеют дату в столбце P, а некоторые строки — нет. Таким образом, макрос должен выглядеть, если есть какие-либо дубликаты (столбец A), и если да, проверьте, есть ли дата в столбце P. Если количество дубликатов с датами больше, макрос должен удалить все дубликаты, но сохранить последние.

Код, который я использовал/отредактировал до сих пор:

Проблема: в ней хранится первая строка, а не последняя дата.

TL; DR: проверьте наличие дубликатов в A, затем проверьте даты в P, затем удалите все дубликаты, но сохраните последние. И если нет даты, удалите все дубликаты и сохраните их.

Как очистить лист Excel на VBA

Иногда есть необходимость перед какими-то действиями сначала очистить лист Excel от всех данных, которые на нем есть, чтобы заполнить его новыми данными. Я предлагаю 3 варианта как можно очистить лист.

Способ 1

Очистка всех ячеек на листе

Либо, если нужно очистить только данные на листе Excel, не затрагивая формат ячеек, можно сделать так:

Способ 2

Чтобы очистить все данные и при этом определение последней строки SpecialCells(xlCellTypeLastCell) работало правильно, можно воспользоваться таким вариантом:

Способ 3

Если лист содержит очень преочень много данных, тогда чтобы очистить лист Excel самым быстрым способом будет удалить его и создать заново.

Предложенных 3 вариантов удаления всех данных на листе Excel я думаю будет достаточно для решения повседневных задач на VBA.

Событие удаления / добавления листов — VBA

Есть ли какое-либо событие, которое вызвало удаление и добавление листа? у меня есть попытка

но это событие работает только когда я добавляю лист. В моем случае мне нужно что-то, что работает как в Add & Delete.

Любая помощь оценит.

Вот еще один способ

Логика :

  1. Перед удалением листа подсчитайте количество листов в книге.
  2. Когда лист активирован, проверьте количество листов в рабочей книге

Код :

Вот вам событие удаления листа.

Поместите этот код в стандартный модуль кода

Поместите это в ThisWorkbook

Не существует ни одного события, которое бы захватывало добавление ( Workbook.NewSheet ) или удаление ( Workbook.SheetBeforeDelete ). Но вы можете обнаружить оба события по отдельности и запустить один и тот же фрагмент кода для обоих событий.

Чтобы иметь возможность запускать код после удаления листа, вы можете использовать таймер, чтобы разрешить удаление. Более того, вы можете настроить цикл ожидания, который подсчитывает количество листов в рабочей книге и который завершается только тогда, когда количество листов является ожидаемым числом после завершения удаления. Затем вы выполняете свой намеченный код.

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

VBA Удаление форматирования на конце листа

Я пишу код, который загружается в данные из файла и помещает его в таблицу Excel. У меня установлен код, так что все ячейки в таблице имеют тонкие черные полосы вокруг них. Тем не менее, после окончания таблицы по всем ячейкам в строках ниже таблицы все еще есть границы. есть ли способ сделать так, чтобы после завершения таблицы все это форматирование было отменено, а ячейки больше не имеют границ? Размер таблицы будет отличаться в зависимости от файла, открытого в excel.

Событие листа. BeforeDelete (Excel) Worksheet.BeforeDelete event (Excel)

Возникает перед удалением листа. Occurs before the worksheet is deleted.

Синтаксис Syntax

Expression. BeforeDelete expression.BeforeDelete

Expression (выражение ) Переменная, представляющая объект листа . expression A variable that represents a Worksheet object.

Возвращаемое значение Return value

Поддержка и обратная связь Support and feedback

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь. Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Excel vba удаление листов в зависимости от даты

На сегодняшний день надстройка Excel содержит более 80 пользовательских функций, и более 25 макросов. Мы стараемся учитывать ваши пожелания и выпускаем обновления с новым функционалом и улучшениями. Все обновления проходят автоматически в пару кликов, быстро и удобно.

После установки надстройки на ленту Excel добавится новая вкладка — VBA-Excel. Вид данной вкладки зависит от вашей версии программы. В Excel 2013 она выглядит следующим образом:

Надстройка VBA-Excel

Сравнить диапазоны

Быстро сопоставьте данные двух таблиц. Найдите ячейки из первой таблице, которые присутствуют во второй. Найдите совпадающие данные. Объедините две таблицы исключив дубликаты.

Стрелки зависимостей

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

Вставить строки

Аналогично поможет вставить большое количество строк

Вставить столбцы

Быстро вставить большое количество столбцов

Поменять местами строки

Меняет два выделенных диапазона строк местами.

Поменять местами столбцы

Аналогично меняет столбцы местами.

Удалить пустые строки или столбцы

Удаляет все строки или столбцы на листе, которые не содержат данных.

Скрыть пустые строки или столбцы

Макрос скрывает пустые строки или столбцы без данных

Вставить значения

Заменить формулы их значениями в указанном диапазоне, на одном или нескольких листах, во всей книге

Разорвать связи

Разрывает связи с другими книгами и заменяет формулы, ссылающиеся на другие книги, на значения.

Объединить сохранив текст

В отличие от стандартной функции сохраняет значения всех ячеек при объединении.

Уникальные

Подсветка уникальных значений и/или вывод их в отдельный диапазон.

Закрепить ячейки

Закрепление строки/столбца или ячейки в формулах всего выделенного диапазона.

Текст формулы в комментарий

Макрос для быстрой записи текста формулы в комментарии к ячейке

Обновить все связи

Принудительное обновление всех связей в книге для пересчета формул и актуализации данных.

Собрать комментарии

Макрос собирает комментарии со всех листов на отдельный лист

Поменять местами ячейки

Позволяет поменять местами два выделенных диапазона в одно действие

Заполнить пустые ячейки

Заполняет пустые ячейки значениями верхних или левых непустых ячеек


Цветовая карта ячеек

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

Диспетчер листов

Диспетчер помогает быстро перемещаться по листам, выполнять различные операции с ними (удалять, скрывать, сортировать и многое другое).

Координатное выделение строки и столбца

Наглядное выделение текущей строки и столбца при работе с большими таблицами.

Стиль ссылок

Быстрое переключение между ссылками вида A1 и R1C1.

Сортировка листов

Макрос позволит отсортировать листы по возрастанию, убыванию или по цвету.

Скриншот области

Выделите диапазон ячеек и сохраните его как рисунок (например, для отправки по почте).

Илон Маск рекомендует:  Asp доступ к клиентским сертификатам с помощью asp

Создание оглавления книги

Помогает быстро создать лист Оглавление с перечнем всех листов книги и ссылками на них.

Регистр

Функция для быстрого изменения регистра букв в Excel.

Диспетчер имен

Макрос позволит удалить именованные диапазоны в книге в том числе и скрытые

Диспетчер стилей

Можно быстро удалить все пользовательские и встроенные стили в книге

Объединить листы в один

Макрос для сбора данных с одного или нескольких листов

Копирование листов n раз

Необходим для того чтобы массово размножить листы в книге

Как создать листы из диапазона

Данная функция позволяет создавать листы с нужным названием из указанного диапазона

Как вывести список листов

Вы можете вывести в указанную ячейку список всех листов книги.

Как массово переименовать листы книги

Функция позволяет переименовывать все листы книги за один раз, а не поштучно как в стандартном функционале

Как удалить пустые листы

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

Удаление пробелов в выделенном диапазоне

Функция позволяет удалить лишние пробелы в указанном диапазоне ячеек

Выбор даты из календаря

Функция позволяет удалить лишние пробелы в указанном диапазоне ячеек

Отображение ярлыков листов

Макрос для быстрого отображение или скрытия ярлычков листов

Диспетчер открытых книг

Менеджер книг призван облегчить работу с большим количеством открытых книг.

Сохранить выбранные книги

Помогает разово сохранить все открытые книги Excel

Изменить видимость листов

Удобное скрытие и отображение листов книги, а также возможность сделать лист супер скрытым.

Удалить картинки на листе

Функционал позволяет удалить все картинки с выбранного листа

Резервное копирование

Быстро создайте резервную копию файла с указанием даты и времени сохранения. При этом не нужно переименовывать текущий файл в котором работаете.

Текущая папка

Не тратьте время на поиск файла, откройте папку в которой он расположен.

Сохранить листы

Сохраните выбранные листы как отдельные книги Excel

Защита листов

Установите защиту сразу на несколько листов книги.

Импортировать листы

Скопируйте листы из закрытых книг в текущую. Можно импортировать листы как из одной, так и из нескольких книг.

Скопировать ссылку на файл

Копируйте ссылку на текущий файл Excel в буфер обмена.

Сумма прописью (СУММАПРОПИСЬЮ)

Запись любого числа прописью в различных падежах.

ПЕРЕВОД

Перевод с любого языка на любой язык с помощью сервиса Яндекс.Перевод.

СКЛОНЕНИЕ

Склонение существительных и ФИО по падежам

СЦЕПИТЬДИАПАЗОН

Объединяет непустые ячейки указанного диапазона.

ЧИСЛОИЗСТРОКИ

Извлечь из строки все найденные числа.

ТЕКСТИЗСТРОКИ

Извлечь из строки только текст игнорируя числа.

Определение пола по имени в Excel

ПОЧТАИЗСТРОКИ

Поможет извлечь почту из любой части строки.

ПОДСТРОКА

Извлекает подстроку текста, разделенного символом-разделителем (Пример: =ПОДСТРОКА(«Мама мыла раму»;» «;1) вернет «Мама»).

СЧЁТСИМВЛ

Подсчитывает количество заданных символов в строке.

ТЕКСТКАК

Возвращает ИСТИНА если указанный текст соответствует шаблону.

УБРАТЬСЛУЧЛАТИН

Функция помогает убрать случайно введенный текст латиницей.

ПРОИЗНЕСТИ

Функция позволяет проговорить введенный текст в ячейку Excel. Предупредить об изменении результата в расчете и т.п.


ТРАНСЛИТ

Выполнение транслитерации текста.

ЕЛАТИН

Проверяет наличие латиницы в тексте.

ЕКИРИЛЛ

Проверяет наличие кириллицы в тексте.

ИЗВЛЕЧЬЛАТИНИЦУ

Извлекает из текста слова на латинице.

ИЗВЛЕЧЬКИРИЛЛИЦУ

Текст, из которого необходимо извлечь слова, написанные кириллицей.

ЕПОЧТА

Проверяет корректность email адреса.

ИНВЕРСИЯ

Выполняет инверсию текста.

НАЧАЛОСТРОКИ

Выполняет поиск заданного текста и возвращает текст, стоящий перед искомым текстом.

КОНЕЦСТРОКИ

Выполняет поиск заданного текста и возвращает текст, стоящий после искомого текста.

ТЕКСТИЗURL

Выполняет импорт текста из интернет страницы в Excel.

ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО

Функции для удобной работы с фамилией, именем и отчеством помогут разделить ячейку с ФИО на части

УБРАТЬСЛУЧКИРИЛ

Функция помогает убрать случайно введенный текст на русской раскладке.

ПРЕДМЕТПРОПИСЬЮ

Функция ПРЕДМЕТПРОПИСЬЮ позволит указать прописью любой тип данных будь то штуки, дни, рубли, тоны, километры и так далее.

ЕФОРМУЛА

Возвращает ИСТИНА если ячейка содержит формулу.

ЕЖИРНЫЙ

Возвращает ИСТИНА если текст ячейки написан жирным шрифтом.

ЕКУРСИВ

Возвращает ИСТИНА если текст ячейки написан курсивом.

ЕДАТА

Возвращает ИСТИНА если ячейка содержит дату.

ЕПОДЧЕРКН

Возвращает ИСТИНА если текст в ячейке подчеркнут.

ФАЙЛСУЩ

Проверяет наличие файла по заданному пути и возвращает ИСТИНА если указанный файл существует.

ПАПКАСУЩ

Проверяет наличие директории по заданному пути и возвращает ИСТИНА если указанная папка существует.

ЛИСТСУЩ

Проверяет существование листа и возвращает ИСТИНА если указанный лист существует в активной книге и ЛОЖЬ если такого листа нет.

КНИГАОТКРЫТА

Проверяет открыта ли книга Excel и возвращает ИСТИНА указанный файл открыт и ЛОЖЬ в противном случае.

ФАЙЛРАСШИР

Определяет расширение файла по пути к нему

Вставка курса любой валюты на любую дату прямо с сайта Центробанка РФ (cbr.ru).

КУРСПЕРИОД

Вставка среднего курса валюты за любой период прямо с сайта Центробанка РФ (cbr.ru).

Формула для быстрого расчета суммы с НДС или же размера НДС.

ДЕНЬНЕДТ

Выводит день недели текстом (понедельник, вторник и т.д.)

СЕГОДНЯСТАТ

Выводит текущую дату. Функция автоматически не пересчитывается.

ДАТАИЗСТРОКИ

Функция ищет дату в строке вне зависимости от того в какой части текста она находится.

Дата прописью (ДАТАПРОПИСЬЮ)

Преобразует дату в текст в различных падежах и форматах.

Время прописью (ВРЕМЯПРОПИСЬЮ)

Записывает выбранное время прописью в различных форматах.

НОМКВАРТАЛА

Удобная формула для вычисления номера квартала по дате в Excel.

МАКСЕСЛИ

Определяет максимальное значение в указанном диапазоне при выполнении значений в этом диапазоне условию.

МИНЕСЛИ

Определяет минимальное значение в указанном диапазоне при выполнении значений в этом диапазоне условию.

СУММЗАЛИВКА

Суммирует ячейки с определенным цветом заливки.

СУММШРИФТ

Суммирует ячейки с определенным цветом шрифта.

СЧЁТЗАЛИВКА

Подсчитывает ячейки с определенным цветом заливки.

СЧЁТШРИФТ

Подсчитывает ячейки с определенным цветом шрифта.

СЧЁТУНИК

Подсчитывает количество уникальных ячеек в указанном диапазоне.

ПЕРВОЕВСТРОКЕ

Возвращает значение первой непустой ячейки в строке.

ПЕРВОЕВСТОЛБЦЕ

Возвращает значение первой непустой ячейки в столбце.

ПОСЛЕДНЕЕВСТРОКЕ

Возвращает значение последней непустой ячейки в строке.


ПОСЛЕДНЕЕВСТОЛБЦЕ

Возвращает значение последней непустой ячейки в столбце.

КОЛИЧЕСТВОСТРОК

Возвращает номер последней строки, которая содержит какие-либо данные.

КОЛИЧЕСТВОСТОЛБЦОВ

Возвращает номер последнего столбца, который содержит какие-либо данные.

УРОВЕНЬСТРОКИ

Возвращает уровень группировки строки.

УРОВЕНЬСТОЛБЦА

Возвращает уровень группировки столбца.

СЛУЧМЕЖДУСТАТ

Возвращает случайное число между заданными. Функция не пересчитывается автоматически.

СЛЯЧЕЙКА

Возвращает значение случайной ячейки из выбранного диапазона.

ГЕНЕРАЦИЯПАРОЛЯ

Генерирует случайный пароль заданной длины.

АДРЕССЫЛКИ

Возвращает адрес гиперссылки.

ТИПЯЧЕЙКИ

Возвращает тип данных, указанной ячейки.

ТЕКСТФОРМУЛЫ

Возвращает текст формулы указанной ячейки.

ТЕКСТФОРМУЛЫEN

Возвращает текст формулы указанной ячейки на английском языке.

ТЕКСТКОММЕНТА

Возвращает текст комментария указанной ячейки.

ФОРМАТЯЧЕЙКИ

Определяет формат данных в ячейке.

ИМЯЛИСТА

Возвращает название активного листа.

ИМЯКНИГИ

Возвращает название текущей книги.

ИМЯПОЛЬЗОВАТЕЛЯ

Возвращает имя текущего пользователя.

РАСПОЛОЖЕНИЕКНИГИ

Возвращает текущее расположение книги.

ДАТАСОЗДАНИЯ

Возвращает дату создания текущего файла.

ДАТАСОХРАНЕНИЯ

Возвращает дату последнего сохранения текущего файла.

ЦВЕТЗАЛИВКИ

Возвращает код цвета заливки указанной ячейки.

ЦВЕТШРИФТА

Возвращает код цвета шрифта указанной ячейки.

ЦВЕТЗАЛИВКИRGB

Возвращает шестнадцатеричный код RGB заливки указанной ячейки.

ЦВЕТШРИФТАRGB

Возвращает шестнадцатеричный код RGB шрифта указанной ячейки.

ДАТАПЕЧАТИ

Возвращает дату последней печати текущего файла.

ШРИФТИМЯ

Возвращает наименование шрифта в указанной ячейке.

ШРИФТРАЗМЕР

Возвращает размер шрифта в указанной ячейке.

ФАЙЛИМЯ

Определяет имя файла из полного пути к файлу

ФАЙЛПУТЬ

Определяет расположение папки в котором находится файл

Обратная связь

На ленте также расположено несколько команд для связи с разработчиком. Вы в любой момент можете оставить замечание по работе программы, сделать предложение по улучшению работы той или иной функции, оставить заказ на разработку макроса.

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

Обращаем Ваше внимание, что функционал, описанный в данной статье, ОТСУТСТВУЕТ в Excel. Чтобы его добавить, необходимо установить надстройку VBA-Excel. Данная программа содержит более сотни функций,которые сделают работу с Excel в разы эффективней. С ней рутинные задачи будут занимать считанные секунды. Программой уже воспользовались чел., попробуйте и Вы!

Удалить лист в Excel с помощью VBA

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

код, который я попробовал:

этот код возвращает ошибку:

Ошибка времени выполнения #424, требуется объект.

У меня, наверное, неправильное форматирование, но если есть более простой способ сделать это, было бы очень полезно.

Ведение журнала сделанных в книге изменений

Проблема очень актуальна для больших организаций, когда одним файлом пользуются несколько человек. И каждый может сделать какие-то свои изменения. И конечно всегда наступает момент когда надо узнать — а кто сделал то или иное изменение? Возможно просто для информации, а бывает и хуже, когда необходимо узнать кто внес конкретное изменение, которое делать было нельзя и по возможности восстановить хоть часть того, что было.

Отслеживание изменений при помощи встроенных средств — Общий доступ к книге
Есть относительно простой способ отслеживать изменения(если это можно так назвать): дать книге общий доступ
Excel 2007 и выше: вкладка Рецензирование (Review)Доступ к книге (Share workbook) . В появившемся окне поставить галочку разрешить изменять файл нескольким пользователям одновременно (Allow changes by more then one user at the same time) :

Далее можно настроить срок хранения лога изменений, конфликты и пр — вкладка Подробнее (Advanced) :

Регистрация изменений (Track changes)

  • Хранить журнал в течение (keep change history for) : — если необходимо вести журнал изменений(а нам необходимо!) то оставляем этот пункт включенным и устанавливаем количество дней, в течение которых необходимо сохранять историю. По умолчанию это 30 дней. Здесь имеются ввиду последние 30 дней от текущей даты. Т.е. по истечению этих 30 дней более ранние данные истории будут затерты
  • Не хранить журнал изменений (don’t keep change history) : после выбора этого пункта и подтверждения журнал будет удален(если он был создан) и история вестись не будет
Илон Маск рекомендует:  Функции msql

Обновлять изменения

  • При сохранении файла (When file is saved) — это самый оптимальный вариант. Данные об изменениях в файле будут обновляться только тогда, когда мы сами сохраним файл.
  • Каждые (Automatically every) : указывается промежуток времени в минутах, через который книга сама автоматически будет сохраняться и регистрировать изменения. Не очень удобен данный пункт если в файле одновременно работает несколько человек. При этом необходимо будет обязательно выбрать какое действие будет производится по умолчанию:
    • сохранить мои изменения и просмотреть чужие (save my changes and see others’ changes)
    • только просмотреть чужие изменения (just see other users’ changes)

    Пункты говорят сами за себя, можно отметить лишь один нюанс: принимать решение о сохранении тех или иных изменениях будет тот пользователь, который первым открыл файл.

Для противоречивых изменений (Conflicting changes between users)

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


  • запрашивать чьи изменения имеют преимущество (ask me which changes win) — самый оптимальный вариант. Первый, открывший файл пользователь определяет какие изменения надо принять, а какие отклонить
  • ранее сохраненные имеют преимущество (the changes being saving win) — не очень правильный вариант, но все зависит от ситуации. По логике при данном пункте при возникновении конфликта автоматически будут приняты лишь те изменения, которые были сделаны ранее. Может сыграть нехорошую шутку, поэтому надо быть острожным с этим пунктом
  • Включить в личное представление (Include in personal view)

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

  • параметры печати (Print settings) — Обычно, в одном файле когда мы настраиваем параметры печати, они сохраняются внутри файла и при следующем открытии их не надо уже заново настраивать. Здесь тоже самое, но хранится для каждого пользователя отдельно. Т.е. даже если в этом файле один пользователь настроил одни параметры печати, а другой – иные, то для каждого пользователя эти параметры сохраняться. В обычной книге применились бы те параметры, которые были назначены перед последним сохранением книги.
  • фильтры (Filter settings) — если один пользователь отфильтровал данные по «Юго-Восточный округ», а другой тот же столбец по «Северный округ», то при установленном данном пункте у каждого пользователя файл откроется с отфильтрованными строками именно по установленным ими параметрам — для каждого свой

    Но оба эти пункта имеют большой недостаток: в зависимости от количества пользователей и их действий они могут сильно «раздувать» файл и приводить к значительным его «тормозам». Поэтому без необходимости лучше их не использовать

    Теперь самое главное: как увидеть все сделанные изменения
    После того, как пользователи поработали с файлом и стало необходимо увидеть сделанные изменения необходимо перейти на вкладке Рецензирование (Review)Исправления (Track changes)Выделить исправления (Highlight changes)

    Здесь можно выбрать какие изменения показывать

    • по времени (When) — если хотите увидеть только какие-то конкретные изменения, то надо установить галочку на этом пункте и выбрать нужное. Доступно выбрать: Со времени последнего сохранения, Все, Еще не просмотрено, С даты. Пункты достаточно красноречивы и понятны, расписывать каждый не вижу смысла. Если хотите просмотреть все изменения — галочку с этого пункта надо снять
    • пользователем (Who) можно показать изменения, сделанные конкретным пользователем, всеми пользователями, или всеми пользователями, кроме того, кто запросил отчет об изменениях(т.е. кроме себя любимого)
    • в диапазоне (Where) можно указать конкретный диапазон на листе и отчет об изменения будет выведен только для ячеек этого диапазона.

    Выделять исправления на экране (Highlight changes on screen) : если установить эту галочку, то изменения будут созданы в виде примечаний к ячейкам, изменения в которых были сделаны. В левом верхнем углу ячейки в этом случае появится черный треугольник, а при наведении на эту ячейку появится примечание с информацией о том кто изменил, когда и на что:

    Вносить изменения на отдельный лист (List changes on a new sheet) : в этом случае будет создан новый лист с именем «Журнал», в котором будут перечислены ячейки, в которые были внесены изменения с указанием даты и времени изменения, пользователя сделавшего изменение, старое и новое значение измененной ячейки:

    Примечание: После того, как книге дан общий доступ книгу одновременно могут менять несколько пользователей. Однако я не рекомендую делать это без крайней необходимости, т.к. одновременный доступ к файлам Excel реализован очень плохо и работает это нестабильно. В какой-то момент файл может просто отказаться работать и все данные будут утеряны. Так же неизбежно будут возникать спорные ситуации, когда изменения внесли два человека одновременно и не сохранилось в результате ни одно из внесенных.

    Но самый главный недостаток: книги в общем доступе имеют ряд ограничений, среди которых такие как:

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

    Плюс невозможно не только использовать умные таблицы, но и сделать книгу общей, если в ней есть хоть одна умная таблица. Если будет попытка сделать общий доступ к книге с умной таблицей Excel покажет предупреждение, что этого делать нельзя и проинструктирует как преобразовать такую таблицу в диапазон для возможности использовать общий доступ.
    Так же хочу отметить, что есть распространенное заблуждение о невозможности использования макросов в книгах с общим доступом. Это не так, коды Visual Basic for Applications разрешается применять и в большинстве случаев они будут работать корректно и как задумывались, если они только не пытаются произвести действия, перечисленные как запрещенные для книг с общим доступом. Плюс невозможно просматривать и изменять коды в книгах с общим доступом.

    Отслеживание изменений и ведение журнала при помощи кода
    Изменения можно отслеживать и при помощи кода. При этом такой метод дает не менее полное представление об изменениях в ячейках и при этом давать общий доступ книге нет необходимости, а следовательно и все ограничения, применимые для книг в общем доступе тоже остаются за бортом, что делает такой подход порой предпочтительнее. Единственное, при таком режиме файл нельзя будет редактировать одновременно нескольким пользователям. Но в большинстве случаев этого и не надо.
    Я могу предложить небольшой код, который будет отслеживать следующие параметры:

    • Имя пользователя(учетная запись пользователя на компьютере), сделавшего изменения
    • адрес ячейки, в которую были внесены изменения
    • дата и время внесения изменений
    • имя листа, в котором были сделаны изменения
    • значение ячейки до изменения(старое значение)
    • значение ячейки после изменения(новое значение).

    Итак, Вы решили реализовать данный процесс. Изначально необходимо разрешить макросы, без этого данный способ ведения журнала не сработает. Далее необходимо добавить в книгу новый лист с именем LOG и вставить приведенный код в модуль книги, изменения в которойнеобходимо отслеживать:

    Option Explicit Public sValue As String Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = «LOG» Then Exit Sub Dim sLastValue As String Dim lLastRow As Long With Sheets(«LOG») lLastRow = .Cells.SpecialCells(xlLastCell).Row + 1 If lLastRow = Rows.Count Then Exit Sub Application.ScreenUpdating = False: Application.EnableEvents = False .Cells(lLastRow, 1) = CreateObject(«wscript.network»).UserName .Cells(lLastRow, 2) = Target.Address(0, 0) .Cells(lLastRow, 3) = Format(Now, «dd.mm.yyyy HH:MM:SS») .Cells(lLastRow, 4) = Sh.Name .Cells(lLastRow, 5).NumberFormat = «@» .Cells(lLastRow, 5) = sValue If Target.Count > 1 Then Dim rCell As Range, rRng As Range On Error Resume Next Set rRng = Intersect(Target, Sh.UsedRange): On Error GoTo 0 If Not rRng Is Nothing Then For Each rCell In rRng If Not IsError(Target) Then sLastValue = sLastValue & «,» & rCell Else sLastValue = sLastValue & «,» & «Err» Next rCell sLastValue = M Then Exit Sub If Target.Count > 1 Then Dim rCell As Range, rRng As Range On Error Resume Next Set rRng = Intersect(Target, Sh.UsedRange): On Error GoTo 0 If rRng Is Nothing Then Exit Sub For Each rCell In rRng If Not IsError(rCell) Then sValue = sValue & «,» & rCell Else sValue = sValue & «,» & «Err» Next rCell sValue = M End If End Sub

    Что такое модуль книги и как туда вставить код подробно описано в этой статье. Если кратко: открываем редактор VBA(Alt+F11) -находим в списке объектов ЭтаКнига(ThisWorkbook) -двойной щелчок по ней и в окно редактора справа вставляется этот код.

    Лист «LOG» рекомендую сделать скрытым, иначе смысла в отслеживании действий мало, т.к. любой сможет перейти на этот лист и стереть историю своих изменений. Надежно скрыть лист поможет эта статья: Как сделать лист очень скрытым.

    Для того, чтобы хранить историю изменений в отдельном текстовом файле или отдельной книге Excel можно применить такой код:

    Option Explicit Public sValue As String Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = «LOG» Then Exit Sub Dim sLastValue As String Dim lLastRow As Long, wbLOG As Workbook Dim sPath as String Const sLOGName As String = «\LOG.txt» ‘»\LOG.xls» sPath = Application.DefaultFilePath Application.ScreenUpdating = False ‘============== только для записи в текстовый файл ====================== If Dir(sPath & sLOGName, vbDirectory) = «» Then Open sPath & sLOGName For Output As #1: Close #1 End If ‘============== только для записи в отдельный файл Excel ====================== ‘ If Dir(sPath & sLOGName, vbDirectory) = «» Then ‘ Set wbLOG = Workbooks.Add ‘ wbLOG.SaveAs sPath & sLOGName, xlNormal ‘ End If Set wbLOG = Workbooks.Open(sPath & sLOGName) ‘============================================================================ With wbLOG.Sheets(1) lLastRow = .Cells.SpecialCells(xlLastCell).Row + 1 If lLastRow = .Rows.Count Then Exit Sub Application.ScreenUpdating = False: Application.EnableEvents = False .Cells(lLastRow, 1) = CreateObject(«wscript.network»).UserName .Cells(lLastRow, 2) = Target.Address(0, 0) .Cells(lLastRow, 3) = Format(Now, «dd.mm.yyyy HH:MM:SS») .Cells(lLastRow, 4) = Sh.Name .Cells(lLastRow, 5).NumberFormat = «@» .Cells(lLastRow, 5) = sValue If Target.Count > 1 Then Dim rCell As Range, rRng As Range On Error Resume Next Set rRng = Intersect(Target, Sh.UsedRange): On Error GoTo 0 If Not rRng Is Nothing Then For Each rCell In rRng If Not IsError(Target) Then sLastValue = sLastValue & «,» & rCell Else sLastValue = sLastValue & «,» & «Err» Next rCell sLastValue = M Then Exit Sub If Target.Count > 1 Then Dim rCell As Range, rRng As Range On Error Resume Next Set rRng = Intersect(Target, Sh.UsedRange): On Error GoTo 0 If rRng Is Nothing Then Exit Sub For Each rCell In rRng If Not IsError(rCell) Then sValue = sValue & «,» & rCell Else sValue = sValue & «,» & «Err» Next rCell sValue = M End If End Sub

    Файл хранится в папке «Мои документы» пользователя. Имя файла — LOG.txt задается посредством константы Const sLOGName As String = «\LOG.txt»

    Чтобы вести изменения в отдельной книге Excel надо будет всего лишь закомментировать строки под «только для записи в текстовый файл» и раскомментировать строки под «только для записи в отдельный файл Excel» и поменять значение для константы Const sLOGName As String = «\LOG.xls»
    Не следует оставлять оба этих блока — они противоречат друг другу и если оставить оба, то будет создан текстовый файл, но изменения все равно будут заноситься в отдельную книгу Excel.
    Если хотите, чтобы файл с историей изменений хранился в папке, отличной от Мои документы, то необходимо
    Application.DefaultFilePath заменить на нужный путь, к примеру такой: sPath = «C:\Users\The_Prist\Рабочий стол»

    При изменении данного параметра необходимо учитывать, что не у всех пользователей может быть доступ к конкретной папке.

    Tips_Macro_LOG.xls (50,0 KiB, 6 297 скачиваний)

    Статья помогла? Поделись ссылкой с друзьями!

    Несколько советов по работе с VBA в Excel

    Некоторое время назад меня попросили «помочь с Экселем», а потом и работа подвернулась такая, так что за последние пару месяцев я узнал много полезного, чем и хочу поделиться в догонку к недавней статье.

    Предполагается, что вы знаете основы Visual Basic. Я не буду рассказывать, как создавать формы или модули, здесь только примеры кода.

    Visual Basic

    Опции

    Во-первых, в VB массивы могут начинаться с индекса 1, что для многих странно, поэтому в начале модулей можно прописывать:

    Так же рекомендуется прописать:

    В этом случае интерпретатор потребует заблаговременного объявления всех переменных. Переменные объявлять нужно потому, что:
    — VB запомнит их нАпиСание и не будет исправлять во всём коде на последний введенный вариант;
    — иногда возникают ошибки с передачей переменных byRef, если они не объявлены (то есть надо или объявить переменную, или приписать в функции/процедуре перед ней byVal).

    Ещё одним важным оператором является ON ERROR. Привожу варианты:

    Возможности языка

    Хотя VB довольно прост, полезно почитать документацию по его синтаксису. Я, например, с удивлением узнал, что можно прописывать сложные усолвия в SELECT’ах (аналог switch):

    Ускорение работы макросов

    Часто макросы требуют долгого времени выполнения, которое можно значительно сократить. В начале и в конце каждой ресурсоёмкой функции вызвать Prepare и Ended.

    По порядку:
    1. Отключить перерисовку объектов на экране, чтобы ничего не мигало.
    2. Выключить расчет. Внимание, если макрос прерваляс посреди работы, то расчет так и останется в ручном режиме!
    3. Не обрабатывать события.
    4. Отображение границ страниц, тоже почему-то помогает.
    5. В статусной строке выводятся различные данные, что замедляет работу, отключаем.
    6. Это если нужно. Выключает сообщения Экселя. Например, мы делаем Workbook.Close, Эксель хочет спросить сохранить ли изменения. При выключении этого параметра все ответы будут даны автоматически (изменения не сохранятся).

    Важно понимать, что VBA выполняет все действия так же, как и пользователь. Поэтому для того, чтобы установить параметры страницы, он каждый раз открывает и закрывает окно параметров. У меня выставлялись параметры для 10 листов, это реально не быстро. Поэтому делаем так:

    Далее, часто нужно просмотреть различные диапазоны ячеек и что-то с ними сделать. Тут важно не использовать циклы for с перебором индексов, они медленные. Можно использовать встроенные функции Экселя, но удобнее всего такой вариант:
    Данный код просматривает указанный диапазон, выбирает в нем «специальные ячейки», в данном случае все, в которых есть формулы (т.е. начинаются со знака равно). Для каждой ячейки смотрится, если она не закрашена, то её надо защитить (см. далее) и покрасить. Такой код работает очень быстро.

    Для любых переменных, которым вы собираетесь присвоить книгу, лист, диапазон (ячейку) нужно предварительно объявить как Variant.

    Естественно, что если вам нужны однотипные значения в ячейках, нужно использовать автозаполнение, всё равно как «растягивание» ячеек пользователем.

    Второй диапазон должен включать первый, а второй необязательный параметр указывает тип автозаполнения.

    Загрузка книги и события

    При открытии книги каждый раз срабатывает процедура.
    В данном случае настройки печати (поля, ориентация) сбрасываются на дефолтные. Можно и другую инициализацию выполнять. Важно, что если макросы отключены, то и не выполнится ничего. Если в Экселе вылезла вверху панелька с предупреждением о макросах и пользователь нажал «Включить», то именно в этот момент выполнится процедура Workbook_open().

    Список доступных событий можно посмотреть вверху редактора VB. Например, я делал на событие Change проверку, где лежит ячейка, в которой было изменения, и если это нужный диапазон, то делалась запись в лог со старым и новым значением.

    Защита

    Во-первых сразу отмечу, что MS Office не исполняет макросы на компьютерах, где он не нашел антивируса, если книга зашифрована. Сталкивался на компьютерах, где антивирус был, но видимо Windows XP об этом не знала.

    Ещё антивирус может странным образом мешать работе, вызывать ошибки, не совсем объяснимые. Показал айтишникам, сказали ок, что-то сделали, не знаю.

    Итак, нам надо защитить книгу, чтобы ввод был разрешен только в нужные ячейки (формулы и заголовки поменять нельзя). Во-первых, нужно сделать соответствующие ячейки «не защищенными». Для этого делаем одно из:
    — выделяем диапазон, формат ячеек, снять галочку «Блокировать ячейку»;
    — выводим кнопку «Блокировать ячейку» в быстрый доступ и нажимаем её, очень удобно смотреть на неё чтобы понять, защищена ячейка или же нет;
    — а это пригодится, чтобы проверить третий вариант — написать макрос, который снимает защиту с нужных ячеек сам.

    Далее нужно защитить лист. На вкладке Рецензирование есть такая кнопка. Окошко просит ввести пароль и установить исключения (что можно будет делать пользователю). К сожалению, список исключений маловат. Самое обидное, что нельзя разрешить сворачивать/разворачивать группы столбцов/строк. Поэтому действуем так, на загрузку книги прописываем:
    Знак подчеркивания продолжает логическую строку на следующей физической строке. Итак, здесь мы:
    1. Сняли защиту.
    2. Включили группировку.
    3. Поставили защиту, при этом:
    — защита только от юзера, макросы продолжают иметь полный доступ (!), крайне важно;
    — разрешили сортировку, фильтрацию и форматирование строк/столбцов (высота/ширина);
    — DrawingObject в данном случае снимает защиту с примечаний к ячейкам, может и ещё с чего.

    Тут мы сталкиваемся с парой сюрпризов. Во-первых, не все макросы будут работать даже так. Известный баг, ничего не сделаешь. Нельзя вставить строку, например. Приходится снимать и тут же ставить защиту. Если «злоумышленник» в этот момент нажмет ctrl+break, то защита слетит.

    Во-вторых, скажем никаким способом нельзя удалять строки (AllowDeletingRows), в которых есть защищенные ячейки, хоть одна. Подробнее вот тут.

    Решением (костылем) является добавление кнопки или сочетания клавиш для удаления. Заодно можно проверить, чтобы пользователь не удалил чего не надо. В Workbook_open добавляем:

    Теперь процедура будет вызываться при нажатии shift+delete.
    Знаю, код некрасивый, простите. Здесь я пытался проверить, что выделена строка, то есть строк там 1, а ячеек не меньше тысячи. Чтобы удалить не то, придется выделить тысячу ячеек начиная не с первого столбца. Далее проверяется имя листа и номера строк. Вместо 50 был расчет последенй строки (ведь их число меняется, если мы их удаляем и добавляем).

    Заключение

    VBA — весьма глючная вещь, которая позволяет сворачивать горы в MS Office. Многие предприятяи используют модели на Excel годами, и если они сделаны хорошо, то всё работает.

    Для изучения VBA подходит он сам, во-первых там хорошая справка. Например, чтобы узнать все варианты что можно разрешить в методе Protect, нажимаем F1, Protect, ввод. И вуаля.

    Во-вторых, можно проделать требуемые действия вручную, записав макрос, а потом просмотрев его код. Код будет ужасен (например, при изменении параметров страницы, макрос запишет значения всех параметров и полей, а не только измененного вами), но ответы найдутся. Хотя, например, .AutoFit, который записывается при изменении высоты ячейки по содержимому (двойной клик на границе слева), на самом деле не работает.

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

    Excel vba удаление листов в зависимости от даты

    Версия от 10.05.2020 г.

    Операционная система: Windows

    Microsoft Excel 2007/2010/2013/2020/2020/365

    Надстройка позволяет быстро удалять все листы рабочей книги Excel кроме активного, удалять листы с заданным шагом (каждый второй, каждый третий, каждый n-ый), удалять листы по маске, используя специальные символы совпадения, например листы, содержащие в своем имени заданный пользователем текст.

    Для работы с надстройкой необходимо:

    1. Скачать файл, который станет доступен для скачивания сразу после оплаты (порядок оплаты);

    3. Нажать кнопку вызова диалогового окна на панели инструментов или на вкладке «Надстройки» (в зависимости от версии Excel);

    4. В диалоговом окне надстройки выбрать необходимую опцию, при необходимости указать в полях диалогового окна шаг либо маску для имен листов и запустить программу.

    *В случае, если в надстройку будут внесены какие-либо изменения или дополнения, то обновленную версию надстройки Вы получите бесплатно.

  • Понравилась статья? Поделиться с друзьями:
    Кодинг, CSS и SQL