В этой статье мы описываем макрофункцию ExecuteExcel4Macro, давно забытую и фактически устаревшую функцию, которая, тем не менее, помогла нам автоматизировать процесс подбора, используя только файлы Excel, список кандидатов и библиотеку документов на SharePoint.
Разумеется, это было только временное решение, и мы уже заменили его российским специализированным ПО, автоматизирующим HR-процессы в сфере управления талантами.
Кратко про ExecuteExcel4Macro
Функция ExecuteExcel4Macro умеет считывать данные из одной ячейки из книги Excel, и не важно какой: открытой или закрытой, хранящейся на локальном жестком диске или в сети.
Смотрите программный код ниже и обратите ваше внимание на четвертый кейс, где функция считывает данные из именованной ячейки, используя только имя «диапазона» и полное наименование рабочей книги.
Sub Hello_ExecuteExcel4Macro()
'1: С локального жесткого диска, используя ссылки R1C1:
Debug.Print _
ExecuteExcel4Macro("'C:\TEMP\[Workbook.xlsx]Worksheet'!R1C1")
'2: С локального жесткого диска, используя именованный диапазон, примененный к одной ячейке:
Debug.Print _
ExecuteExcel4Macro("'C:\TEMP\Workbook.xlsx'!NAMED_RANGE_OF_ONE_CELL")
'3: Из сети, используя ссылки R1C1:
Debug.Print _
ExecuteExcel4Macro("'https://binaries.templates.cdn.office.net/support/templates/en-gb/[tf10000091.xlsx]Time Sheet'!R5C2")
'4: Из сети, используя именованный диапазон, примененный к одной ячейке:
Debug.Print _
ExecuteExcel4Macro("'https://binaries.templates.cdn.office.net/support/templates/en-gb/tf10000091.xlsx'!WorkweekHours")
End SubОграничение функции ExecuteExcel4Macro
Хотя и является устаревшей, данная функция может быть адаптирована для извлечения любых типов данных, доступных в Excel, за исключением строки более 255 символов, и в этом случае функция ничего не возвращает.
В качестве обходного решения, длинный текст в книге-источнике нужно поместить в несколько ячеек, так чтобы количество символов в каждой ячейке не превышало существующий лимит.
Что же касается типов данных, то функция импортирует данные как vbDouble, vbString и vbBoolean.
Пример применения
Предположим, что наши пользователи вводят данные в книги Excel, основанные на едином шаблоне, и сохраняют их в библиотеку документов на SharePoint. Скажем, более 20 рекрутеров, более 1000 заявок на подбор, хранящихся в более 100 папках на SharePoint.
И чтобы контролировать данных процесс, необходимо собрать данные из всех рабочих книг в одну таблицу.
Как показано на рисунке ниже, целевая книга имеет результирующую таблицу и VBA-процедуру, которая, в свою очередь:
Проверяет именованные столбцы в целевой таблице, напр., «Имя1» и «Имя2»
Находит книги-источники в требуемой директории на SharePoint
Импортирует данных из именованных ячеек из книг-источников в результирующую таблицу в соответствующие именованные столбцы
В данной статье мы не объясняем, как получить список файлов для формирования запросов, т.к. это можно сделать различными способами в зависимости от расположения файлов. Например, мы подключали SharePoint как сетевой диск.
Аналогично, мы не объясняем, как работать с циклами и массивами (или коллекциями, если более предпочтительно), т.к. использование этих инструментов может значительно различаться.
Однако, несмотря на все различия, основополагающая идея остается прежней: данные легко могут быть собраны в единую таблицу из множества книг Excel, хранящихся в сети.
Демонстрация: https://youtu.be/IMtmn2CT-0E
