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

Сортировка двумерного массива на VB (VBA)

Сортировка двумерного массива по нулевому столбцу

Public Function CoolSort(SourceArr As Variant) As Variant
    ' сортировка двумерного массива по нулевому столбцу

Макрос для снятия ограничений Excel (безопасность макросов и файлов)

Если вы работаете с книгами Excel древних форматов (таких, например, как Excel 4),
то в Excel 2010 вы можете столкнуться с проблемой, что редактирование этих файлов по-умолчанию запрещено.

В настройках Excel 2010 для этого предусмотрены специальные опции (скриншот)

Мало того, что Excel блокирует редактирование этих устаревших файлов,
так ещё и макросы начинают вести себя очень странно
(например, если активен файл Excel4, простейший макрос чтения пути текущего файла не запускается)

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

Функция перевода с одного языка на другой (с использованием Google Translate)

Данная VBA функция позволяет перевести текст с любого языка на другой
Язык исходного текста можно не указывать - Google распознает его самостоятельно.
(т.е. вызовы res$ = Translate(txt$, "en", "ru") и res$ = Translate(txt$, "en") равнозначны)

Добавлено 17.10.2011
В данный момент функция не работает для большого числа запросов - Google недавно ввёл ограничение.
Решение для обхода этого ограничения сложное, потому пока мной не реализовано (не было необходимости)

Добавлено 07.03.2012
Исправленный вариант функции вы можете найти в конце статьи. (теперь снова можно выполнять автоматизированный перевод любого количества фраз на любые языки)
Алгоритм функции немного изменился - но это и не важно, главное, что перевод снова работает.
Пример использования перевода на листе Excel - во втором прикреплённом файле

Sub ПримерИспользованияФункцииПеревода()
    txt$ = "Привет! Это функция перевода текста на иностранный язык"
    res$ = Translate(txt$, "en", "ru")
    MsgBox "Результат перевода на английский:" & vbNewLine & res$, vbInformation, txt$
    res$ = Translate(txt$, "de")
    MsgBox "Результат перевода на немецкий:" & vbNewLine & res$, vbInformation, txt$
End Sub

Список кодов доступных языков для перевода:
(используются в качестве параметров функции Translate)

ar - арабский; bg - болгарский; cs - чешский;
da - датский ; de - немецкий; el - греческий;
en - английский; es - испанский; fi - финский;
fr - французский; hi - хинди; hr - хорватский;
it - итальянский; ja - японский; ko - корейский;
nl - голландский; no - норвежский; pl - польский;
pt - португальский; ro - румынский; ru - русский;
sv - шведский; zh-cn - китайский упрощенный; zh-tw - китайский традиционный

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

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

1c04;1J0-698-151-G;1 комплект тормозных накладок;1J0698151G;1J0698151G;5;1
1c04;1H0698151A;Тормозные колодки;1H0698151A;1H0698151A;1;1
1c04;1K0-698-151-B;Тормозные колодки;1K0698151B;1K0698151B;2;1

А надо из всего этого сформировать табличку в Excel - приблизительно такого вида:

Пример таблицы в Excel

На помощь придёт функция DATfolder2Array

Sub ПримерИспользованияФункции_DATfolder2Array()
    Папка = "D:\Проекты\DATs\"    ' папка, в которой будет производиться поиск файлов DAT для обработки
    Dim ErrorsArray    ' пустой массив для ошибок

    ' считываем данные из все файлов .DAT в папке в двумерный массив
    DataArr = DATfolder2Array(Папка, 7, "1,2,4,5", ErrorsArray)
 
    ' результаты выводим на листы "errors" и "result" (они должны существовать)
    Array2worksheet Worksheets("errors"), ErrorsArray, _
                    Array("Имя файла", "Номер строки", "Данные из строки")
    Array2worksheet Worksheets("result"), DataArr, _
                    Array("Ячейка", "Штрих-Код", "Наименование", "код 1С", "код произв.", "кол-во", "счетовод")
End Sub

Прикрепление и извлечение различных файлов из книги Excel

Скриншот программы, позволяющей прикреплять файлы к книге Excel

Можно ли прикрепить (вложить) произвольные файлы в обычную книгу Excel?
А потом извлечь эти файлы в заданную папку, и работать с ними?

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

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

В прикреплённом к статье файле находятся 2 модуля класса (AttachedFiles и AttachedFile), а также примеры их использования в виде макросов, позволяющих управлять вложениями в книге Excel.

Пример использования функционала модулей класса для сохранения в книге Excel исполняемого файла, с последующим извлечением:

Sub ПрикрепитьФайл()    ' прикрепляем файл к книге Excel
    Dim FileManager As New AttachedFiles, res As Boolean
    res = FileManager.AttachNewFile("C:\WINDOWS\notepad.exe")
End Sub

Sub ИзвлечьФайл()    ' из книги Excel на диск
    Dim FileManager As New AttachedFiles, res As Boolean
    On Error Resume Next ' на случай, если среди вложений нет файла notepad.exe
    res = FileManager.GetAttachment("notepad.exe").SaveAs("C:\MyProgram.exe")
End Sub

Sub ЗапуститьВложенныйФайл()    ' из книги Excel на диск
    Dim FileManager As New AttachedFiles
    On Error Resume Next ' на случай, если среди вложений нет файла notepad.exe
    FileManager.GetAttachment("notepad.exe").Run
End Sub