Макросы VBA Excel

Программы, надстройки и макросы VBA, использующие в своей работе модули класса (Class Modules)

Удаление (скрытие) строк по условию

макрос удалит на листе все строки, в которых содержится искомый текст:

(пример - во вложении ConditionalRowsDeleting.xls)

Sub УдалениеСтрокПоУсловию()
    Dim ra As Range, delra As Range, ТекстДляПоиска As String
    Application.ScreenUpdating = False    ' отключаем обновление экрана

    ТекстДляПоиска = "Наименование ценности"    ' удаляем строки с таким текстом

    ' перебираем все строки в используемом диапазоне листа
    For Each ra In ActiveSheet.UsedRange.Rows

Получение списка файлов в папке и подпапках средствами VBA

Макрос VBA загрузки списка файлов из папки

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

 

Пример в файле FilenamesCollection.xls выводит список файлов на чистый лист новой книги (формируя заголовки) 

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

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

Макрос сохранения листа Excel в файл

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

Для использования этого макроса на любом листе в книге Excel создайте кнопку, и назначьте ей макрос СохранитьЛистВФайл.

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

Сохранение производится в формате XLS (формат Excel 2003)
Если пользователь отказался от ввода имени файла (нажал клавишу ESC или кнопку «Отмена» в диалоговом окне),
то сохранения листа в файл не происходит.

Вставка картинок и изображений в ячейки листа Excel

Требуется макросом поместить изображение (картинку) на лист Excel?

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

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

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

Кроме того, надстройка для вставки изображений в Excel умеет загружать картинки из интернета (по ссылкам в таблице Excel)

Бесплатно скачать надстройку вставки картинок в Excel

В этом примере демонстрируются возможные варианты применения функции вставки картинок:

Сбор данных из файлов Excel в заданной папке

Обработка данных из файлов Excel - отображение информации на индикаторе состояния

Этот макрос предназначен для сбора (загрузки) информации из файлов Excel, расположенных в одной папке.

 

Для работы этого макроса, помимо него самого, вам понадобится добавить в свой файл:

  1. функцию FilenamesCollection для получения списка файлов в папке
  2. функцию GetFolder для вывода диалогового окна выбора папки с запоминанием выбранной папки
  3. прогресс-бар для отображения процесса обработки файлов (модуль класса и форму)

 

Если при тестировании макроса у вас возникает ошибка, что не найдена та или иная функция,
— проверьте, все ли необходимые компоненты (которые перечислены выше) вы добавили в свой файл.

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

 

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

После того, как очередной файл обработан, он перемещается во вторую папку («архив»).

Надстройка: выпадающий список с поиском (комбо)

Скриншот формы ввода (выпадающий список с поиском)

Надстройка для облегчения ввода значений в ячейку Excel

Автор: nerv
Last Update: 27/03/2012

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

А, может, иметь дело с одними теми же, но не структурированными данными?

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

Как это работает:

Макрос отправки письма из Excel через Outlook

Пример макроса, отправляющего письма со вложениями из Excel через почтовый клиент Outlook:

Sub Отправить_Письмо_из_Outlook()
 
    'отправляем письмо без вложений
    res = SendEmailUsingOutlook("name@domain.ru", "Текст письма 1", "Тема письма 1")
    If res Then Debug.Print "Письмо 1 отправлено успешно" Else Debug.Print "Ошибка отправки"
 
 
    'отправляем письмо с 1 вложением
    attach$ = ThisWorkbook.FullName    ' прикрепляем текущий файл Excel
    res = SendEmailUsingOutlook("name@domain.ru", "Текст письма 2", "Тема письма 2", attach$)
    If res Then Debug.Print "Письмо 2 отправлено успешно" Else Debug.Print "Ошибка отправки"
 
 
    'отправляем письмо с несколькими вложениями
    Dim coll As New Collection    ' заносим в коллекцию список прикрепляемых файлов
    coll.Add "C:\Documents and Settings\Admin\Рабочий стол\Tyres.jpg"
    coll.Add "C:\Documents and Settings\Admin\Рабочий стол\calc.xls"
    coll.Add ThisWorkbook.FullName    ' прикрепляем текущий файл Excel

    res = SendEmailUsingOutlook("name@domain.ru", "Текст письма 3", "Тема письма 3", coll)
    If res Then Debug.Print "Письмо 3 отправлено успешно" Else Debug.Print "Ошибка отправки"
End Sub

Макрос использует функцию SendEmailUsingOutlook, которая:

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

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

ВНИМАНИЕ! Данный код гарантированно работает ТОЛЬКО в ОС WindowsXP.
В остальных версиях Windows код не проверял.
В Windows7 данный макрос работать не будет ввиду отсутствия библиотеки CDO for Windows 2000.
(потребуется ручная установка недостающей библиотеки)

Пример отправки почты макросом Excel:

Sub Main()    ' Пример использования функции Send_Mail
    txt = "Это письмо сформировано макросом" & vbNewLine & _
          "без использования внешних программ и подключения дополнительных библиотек"
    If Send_Mail("ivan_ivanov@mail.ru", "vasya_pupkin@mail.ru", "проверка отправки почты", txt) Then
        MsgBox "Письмо успешно отправлено", vbInformation
    Else
        MsgBox "Не удалось отправить письмо", vbExclamation
    End If
End Sub

Выпадающий календарь в ячейке (выбор даты на листе Excel)

Выпадающий календарь в ячейке листа Excel

Надстройка samradDatePicker (русифицированная) для облегчения ввода даты в ячейки листа Excel.

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

Поместите файл надстройки из вложения в папку автозагрузки Excel (C:\Program Files\Microsoft Office\OFFICExx\XLSTART).

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

Загрузка изображений из интернета по ссылкам в одну папку

Загрузка файлов (изображений) из интернета

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

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

таблица, в которой содержатся по меньшей мере 2 столбца - один с гиперссылками, второй - с именами файлов.

Особенности макроса:

  • создаваемым файлам присваиваются имена из выбранного столбца листа Excel
  • макрос корректно работает со ссылками, содержащими символы кириллицы
  • автоматическое добавление расширения для скачиваемых файлов (если имя файла из ячейки его не содержит)

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

Кроме того, надстройка для вставки изображений в Excel умеет загружать картинки из интернета (по ссылкам в таблице Excel)

Бесплатно скачать надстройку вставки картинок в Excel

 

Настройки макроса легко выполнить, изменив в коде значения констант:

    Const НазваниеПапкиДляФайлов$ = "Фотографии"    ' так будет называться создаваемая папка
    Const НомерСтолбцаСГиперссылками = 6    ' из этого столбца макрос берет гиперссылки для загрузки файлов
    Const НомерСтолбцаСИменамиФайлов = 4    ' из этого столбца макрос берет имена для создаваемых файлов
    Const НомерПервойСтрокиСДанными = 2    ' с какой строки листа начинаем обрабатывать данные
    Const РасширениеФайлов$ = ".jpg"    ' этот текст добавляется справа к именам создаваемых файлов

Смотрите также аналогичный (более сложный) макрос загрузки изображений