В этой статье мы описываем макрофункцию 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. Проверяет именованные столбцы в целевой таблице, напр., «Имя1» и «Имя2»

2. Находит книги-источники в требуемой директории на SharePoint

3. Импортирует данных из именованных ячеек из книг-источников в результирующую таблицу в соответствующие именованные столбцы

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

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

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

Демонстрация: https://youtu.be/IMtmn2CT-0E