Макросы VBA Excel — Страница 22

Получение списка переменных окружения (функция ENVIRON в VBA)

При помощи функции Environ() можно получить значение переменной окружения Windows

Этот макрос создаст новую книгу, и выведет в неё список из 31 переменной,
с примерами вызова функции для получения каждого из параметров:

Sub ВывестиПеременныеОкружения()
    On Error Resume Next
    Dim sh As Worksheet, param$
    Application.ScreenUpdating = False: Set sh = Workbooks.Add.Worksheets(1)
    With sh.Range("a1:d1")
        .Value = Array("Номер параметра", "Параметр", "Пример вызова", "Результат (на моём компьютере)")
        For i = 1 To 31
            param$ = Split(Environ(i), "=")(0)
            .Offset(i).Value = Array(i, param$, "env$ = Environ(""" & param$ & """)", Environ(param$))
        Next
    End With
End Sub

В результате работы макроса, получается следующая таблица:

Функция получения ссылки на заданную пользователем ячейку

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

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

Потому и была написана функция GetCell, которую можно использовать следующим образом:

Sub ПримерИспользования_GetCell()
    ' вставляем значение в первую пустую ячейку столбца A
    ' (вставка производится ниже всех данных в первом столбце листа)
    GetCell("a").Value = Now
 
    ' то же самое, но с другими вариантами параметра функции (все 4 способа равнозначны)
    GetCell("a:a").Value = 111
    GetCell(Columns(1)).Value = 222
    GetCell([a:a]).Value = 333
 
    '  ============ вставка в первую незаполненную ячейку третьей строки =================
    GetCell(Destination:=3).Value = 1
    ' то же самое, но с другими вариантами параметра функции (все 4 способа равнозначны)
    GetCell("3").Value = 2
    GetCell(Rows(3)).Value = 3
    GetCell([3:3]).Value = 4
 
    '  ============ другие варианты использования =================
    GetCell().Value = "активная ячейка"    ' вставка в заданную ячейку (вызов без параметра)
    GetCell("NewSheet").Value = "на новый лист в ячейку A1"    ' создаётся новый лист
    GetCell("NewWorkbook").Value = "в новую книгу в ячейку A1"    ' создаётся новая книга Excel
End Sub

Функция для получения всех графических объектов (картинок) в заданном диапазоне ячеек

Функция ShapesInRange предназначена для получения объекта типа ShapeRange, содержащего все картинки в заданном диапазоне ячеек листа Excel

Пример использования функции ShapesInRange:

Sub DeleteShapesInRange()
    Dim ra As Range: Set ra = Columns(6) ' задаём диапазон для поиска картинок
    On Error Resume Next    ' на случай, если картинок в заданном диапазоне нет
    ShapesInRange(ra).Delete    ' удаляем все картинки в диапазоне ra
End Sub

Код функции ShapesInRange:

Получение файлов из архива ZIP на VBA

Функция предназначена для получения файлов, извлечённых из архива ZIP.

Разархивирование выполняется средствами Windows, файлы извлекаются в специально созданную папку в каталоге для временных файлов (C:\WINDOWS\Temp\)

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

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

См. также функцию UnZip_File, предназначенную для разархивирования файлов
в заданную папку, с возможностью удаления исходного файла-архива.

Пример использования функции FilesFromZip:

Sub ПримерИспользования()
    On Error Resume Next
    file$ = "D:\Проекты\Прайсы\Архив.zip"    ' путь к архиву, из которого будем извлекать файлы
    
    Dim coll As Collection
    Set coll = FilesFromZip(file)
    Debug.Print "Извлечено файлов: " & coll.Count ' выводи количество файлов
    
    For Each filename In coll ' выводим пути к извлечённым из архива ZIP файлам
        Debug.Print filename
    Next
End Sub

Работа с HTML: поиск тегов (RegExp) и преобразование HTML в текст

В данной статье приведён код 2 функций, которые позволят вам найти на веб-странице нужные HTML теги,
и преобразовать HTML в текст

Особенность этого кода, - использование регулярных выражений (Regexp) для поиска в HTML

Эти функции лежат в основе моей надстройки «Парсер сайтов»

Пример использования:

' в переменной txt находится исходный код веб-страницы (целиком, или его часть)

' ищем div id="mod-lists", и берем его начинку (innerHTML)
' Индекс 1 после innerHTML означает, что если будет найдено несколько таких тегов, - макрос возьмет только первый
res = GetTags(txt, "div", "id", "mod-lists", "innerHTML 1")
 
 
' ищем ВСЕ теги span класса product (функция вернёт массив значений в переменную arr)
arr = Split(GetTags(txt, "span", "class", "product", "outerHTML"), ARSEP)
 
 
' ищем гиперссылку (тег a класса blue-link), и возвращаем атрибут href
link$ = GetTags(txt, "a", "class", "blue-link", "href")
 
 
' ищем ЛЮБОЙ ТЕГ класса price, и берем последний найденный (last), преобразовав его в текст (ConvertToText)
price = GetTags(txt, "any tag", "class", "price", "ConvertToText last")

Есть возможность удалять теги из HTML (параметр DeleteTags), а также использовать подстановочный символ * до или после значения атрибута.
Можно также выполнять поиск по маске для атрибутов:

' в переменной txt находится исходный код веб-страницы (целиком, или его часть)

' удаляем все div, где имя класса начинается с old
txt = GetTags(txt, "div", "class", "old*", "DeleteTags")
 
 
' ищем ВСЕ гиперссылки по маске /item/
links_array = Split(GetTags(txt, "a", "AttributesPattern", "*/item/*", "href"), ARSEP)

Функцией преобразования HTML в текст (ConvertHTMLtoText) можно воспользоваться отдельно (без функции GetTags)

Весь приведённый ниже код, скопируйте в отдельный стандартный модуль