Моя девушка занимается закупками в торговой сети. Недавно в компании прошло большое сокращение, поэтому количество работы на одного сотрудника резко выросло. Поэтому ей пришлось регулярно задерживаться на работе, иногда даже выходить по субботам. Такая же проблема возникла и у её коллег.
О решении, позволившем моей девушке возвращаться домой вовремя, можно прочитать под катом.
При этом значительную часть того, что они делали можно автоматизировать: получение данных с фронта, заполнение excel документов, обновление данных во фронте и т.д.
Самое разумное, что можно было бы сделать в этой ситуации, – это написать ТЗ на разработку необходимого функционала и через начальство передать разработчикам. Однако сокращение коснулась всех, в том числе и разработчиков. В результате, у них не было достаточно ресурсов на реализацию этой автоматизации. А специализированные RPA системы компания покупать в обозримом будущем не планировала.
Кроме того, усилилась слежка за тем, что сотрудники делают на работе и в каких приложениях они трудятся.
По итогу всех этих неблагоприятных обстоятельств и моего отчаянного желания помочь, я решил написать функции для Excel на VBA, благодаря которым можно было бы автоматизировать рутинные операции моей девушки и её коллег.
В качестве точки старта я выбрал задачу, на которую девушка тратила по её ощущениям больше всего времени. В рамках этой задачи девушке было необходимо:
Учитывая то, что моя девушка не обладала знаниями в области программирования, необходимо было сделать инструмент с простым и привычным интерфейсом в виде функций в excel. Последовательности действий должны задаваться просто как последовательность функций. Одним словом, KISS.
Основываясь на данном кейсе мной были сформированы следующие функциональные требования:
Прежде чем двигать курсор куда-либо, необходимо понять, куда именно его двигать. Наиболее адекватным в данной ситуации мне представляется просто запоминать положение курсора, когда он находится над определенным элементом. Для того, чтобы запомнить координаты, воспользовался функцией GetCursorPos из библиотеки user32.
Отлично, координаты мы получили, теперь было бы неплохо их запомнить. Ну тут ничего сложного, подумал я, просто отсчитаем от активной ячейки одну или две ячейки и запишем координаты X и Y. Однако, ActiveCell.Offset(0, 1).Value = x не сработало. Значение не поменялось. А в результате выполнения ошибка. После проверки различных предположений выяснилось, что изменение значение на листе приводит к пересчету всего листа, а значит и той формулы, которая вызывает этот пересчет. Для того, чтобы обойти это ограничение пришлось вместо прямого вызова из функции определенных действий заменить эти вызовы на Evaluate, что позволило добиться желаемого.
В результате получилась функция PrintCursorPosition(), которая записывала в две ячейки справа положения курсора в момент выполнения функции. Нужно было набрать PrintCursorPosition() в области для ввода формул, переместить курсор и нажать на клавиатуре enter.
Для перемещения мыши использовал SetCursorPos из той же библиотеки user32. Для того, чтобы использовать эту функцию, ей на вход нужно было передать значение координат x и y, которые были запомнены ранее. С помощью SetCursorPosition(x, y) я смог двигать курсор по ранее запомненным координатам. Первый видимый результат. Ура!
Для симуляции действий мыши я использовал mouse_event из той же библиотеки user32. Передавая на вход флаги клавиш я, смог симулировать нажатия соответствующих клавиш. Первоначально я планировал написать одну функцию MouseButtonPrees(flag), где flag — это обозначение нажатой клавиши, но после первой демонстрации девушке понял, что лучше заменить на группу функций LeftClick(), RightClick() и DoubleClick(). Такой подход позволяет легче читать итоговую функцию.
В VBA существует оператор SendKeys, который выполняет все необходимые действия. Текст легко передается в функцию по ссылке на ячейку и отрабатывает без проблем. Однако нажатие специальных клавиш (Enter, Tab, Alt, Ctrl, стрелки на клавиатуре и т.д.) вызывали отторжение (для их нажатия необходимо было записать их в фигурных скобках {ENTER}). Поэтому для наиболее часто используемых я написал функции по типу PressEnter(). Для редко используемых я создал шпаргалку в том же документе.
Передача информации между системой и Excel осуществлялась через копирование в буфер и вставку из буфера. Копирование в буфер по сути осуществлялось симуляцией нажатия Control + C после чего в MSForms.DataObject забирались данные из буфера и переносились в конкретную ячейку.
Проблемы начались сразу.
Процесс написание скрипта последовательности действий состоит из отработки небольших групп действий и объединение их в одну. Но при переключении на свободную ячейку вся последовательность тут же отрабатывалась, что чрезвычайно раздражало, особенно если время выполнение группы действий занимало более 10 секунд. Для решение этой проблемы внес проверку на наличие в тексте формулы активной ячейки названия функции. Это помогло.
Кроме того, во время тестирования добавил функцию ожидания WaitS(seconds) и WaitMS(miliseconds), для того, чтобы успевать следить, что отработало, а что нет. Она основана на Sleep из библиотеки kernel32. Разница между WaitS и WaitMS в том, что в WaitMS время в миллисекундах, а в WaitS в секундах.
Другой проблемой стало непоследовательное выполнение функций при протягивании в ячейки. Это было связано с асинхронным расчетом Excel. Он распределял расчет каждой ячейки на разные процессоры. В результате сначала выполняется последовательность в ячейке два, потом в пятой, потом в третьей и т.д. При этом сами последовательности выполнялись от начала и до конца без проблем. Для того, чтобы избавиться от этого поведения отключил в настройках Excel многопоточные вычисления (Параметры Excel -> Дополнительно -> Формулы).
Объяснив, как с этим всем работать и научив пользоваться, отпустил свою девушку автоматизировать процессы в компании таким неблагодарным способом.
Благодаря такой автоматизации получилось сократить время с трех часов до 30 минут. При этом автоматизация позволила несколько изменить подход к процессу выгрузки и загрузки данных. Теперь Выгрузка происходит во время того, когда моя девушка уходит на обед, а загрузка ночью. Таким образом можно сказать, что нагрузка уменьшилась почти на половину рабочего дня, что позволило моей девушке возвращаться домой вовремя и мы можем заняться более интересными вещами, чем автоматизацией.
О решении, позволившем моей девушке возвращаться домой вовремя, можно прочитать под катом.
При этом значительную часть того, что они делали можно автоматизировать: получение данных с фронта, заполнение excel документов, обновление данных во фронте и т.д.
Самое разумное, что можно было бы сделать в этой ситуации, – это написать ТЗ на разработку необходимого функционала и через начальство передать разработчикам. Однако сокращение коснулась всех, в том числе и разработчиков. В результате, у них не было достаточно ресурсов на реализацию этой автоматизации. А специализированные RPA системы компания покупать в обозримом будущем не планировала.
Кроме того, усилилась слежка за тем, что сотрудники делают на работе и в каких приложениях они трудятся.
По итогу всех этих неблагоприятных обстоятельств и моего отчаянного желания помочь, я решил написать функции для Excel на VBA, благодаря которым можно было бы автоматизировать рутинные операции моей девушки и её коллег.
Требования к продукту
В качестве точки старта я выбрал задачу, на которую девушка тратила по её ощущениям больше всего времени. В рамках этой задачи девушке было необходимо:
- Перенести из excel файла в строку поиска фронтофисной системы артикул товара;
- По данному артикулу из выдачи поиска получить цену закупки, продажи, ряд дополнительных значений;
- Обработать собранные данные в excel для создания финальной цены продукта;
- Загрузить цены обратно в систему.
- На выгрузку и последующую загрузку данных в день уходило около 3 часов.
Учитывая то, что моя девушка не обладала знаниями в области программирования, необходимо было сделать инструмент с простым и привычным интерфейсом в виде функций в excel. Последовательности действий должны задаваться просто как последовательность функций. Одним словом, KISS.
Основываясь на данном кейсе мной были сформированы следующие функциональные требования:
- Управлять мышкой (перемещение, нажатие клавиш) для выделения соответствующих элементов на экране;
- Симулировать нажатие кнопок на клавиатуре для ввода данных;
- Перенос данных из Excel в сторонние приложения;
- Получение данных из приложения в Excel;
- Выполнение однотипных операций при протягивании формулы в Excel.
Мышь и первые трудности
Прежде чем двигать курсор куда-либо, необходимо понять, куда именно его двигать. Наиболее адекватным в данной ситуации мне представляется просто запоминать положение курсора, когда он находится над определенным элементом. Для того, чтобы запомнить координаты, воспользовался функцией GetCursorPos из библиотеки user32.
Отлично, координаты мы получили, теперь было бы неплохо их запомнить. Ну тут ничего сложного, подумал я, просто отсчитаем от активной ячейки одну или две ячейки и запишем координаты X и Y. Однако, ActiveCell.Offset(0, 1).Value = x не сработало. Значение не поменялось. А в результате выполнения ошибка. После проверки различных предположений выяснилось, что изменение значение на листе приводит к пересчету всего листа, а значит и той формулы, которая вызывает этот пересчет. Для того, чтобы обойти это ограничение пришлось вместо прямого вызова из функции определенных действий заменить эти вызовы на Evaluate, что позволило добиться желаемого.
В результате получилась функция PrintCursorPosition(), которая записывала в две ячейки справа положения курсора в момент выполнения функции. Нужно было набрать PrintCursorPosition() в области для ввода формул, переместить курсор и нажать на клавиатуре enter.
Для перемещения мыши использовал SetCursorPos из той же библиотеки user32. Для того, чтобы использовать эту функцию, ей на вход нужно было передать значение координат x и y, которые были запомнены ранее. С помощью SetCursorPosition(x, y) я смог двигать курсор по ранее запомненным координатам. Первый видимый результат. Ура!
Для симуляции действий мыши я использовал mouse_event из той же библиотеки user32. Передавая на вход флаги клавиш я, смог симулировать нажатия соответствующих клавиш. Первоначально я планировал написать одну функцию MouseButtonPrees(flag), где flag — это обозначение нажатой клавиши, но после первой демонстрации девушке понял, что лучше заменить на группу функций LeftClick(), RightClick() и DoubleClick(). Такой подход позволяет легче читать итоговую функцию.
Клавиатура
В VBA существует оператор SendKeys, который выполняет все необходимые действия. Текст легко передается в функцию по ссылке на ячейку и отрабатывает без проблем. Однако нажатие специальных клавиш (Enter, Tab, Alt, Ctrl, стрелки на клавиатуре и т.д.) вызывали отторжение (для их нажатия необходимо было записать их в фигурных скобках {ENTER}). Поэтому для наиболее часто используемых я написал функции по типу PressEnter(). Для редко используемых я создал шпаргалку в том же документе.
Передача информации между системой и Excel осуществлялась через копирование в буфер и вставку из буфера. Копирование в буфер по сути осуществлялось симуляцией нажатия Control + C после чего в MSForms.DataObject забирались данные из буфера и переносились в конкретную ячейку.
Тестирование и проблемы исполнения
Проблемы начались сразу.
Процесс написание скрипта последовательности действий состоит из отработки небольших групп действий и объединение их в одну. Но при переключении на свободную ячейку вся последовательность тут же отрабатывалась, что чрезвычайно раздражало, особенно если время выполнение группы действий занимало более 10 секунд. Для решение этой проблемы внес проверку на наличие в тексте формулы активной ячейки названия функции. Это помогло.
Кроме того, во время тестирования добавил функцию ожидания WaitS(seconds) и WaitMS(miliseconds), для того, чтобы успевать следить, что отработало, а что нет. Она основана на Sleep из библиотеки kernel32. Разница между WaitS и WaitMS в том, что в WaitMS время в миллисекундах, а в WaitS в секундах.
Другой проблемой стало непоследовательное выполнение функций при протягивании в ячейки. Это было связано с асинхронным расчетом Excel. Он распределял расчет каждой ячейки на разные процессоры. В результате сначала выполняется последовательность в ячейке два, потом в пятой, потом в третьей и т.д. При этом сами последовательности выполнялись от начала и до конца без проблем. Для того, чтобы избавиться от этого поведения отключил в настройках Excel многопоточные вычисления (Параметры Excel -> Дополнительно -> Формулы).
Результаты
Объяснив, как с этим всем работать и научив пользоваться, отпустил свою девушку автоматизировать процессы в компании таким неблагодарным способом.
Благодаря такой автоматизации получилось сократить время с трех часов до 30 минут. При этом автоматизация позволила несколько изменить подход к процессу выгрузки и загрузки данных. Теперь Выгрузка происходит во время того, когда моя девушка уходит на обед, а загрузка ночью. Таким образом можно сказать, что нагрузка уменьшилась почти на половину рабочего дня, что позволило моей девушке возвращаться домой вовремя и мы можем заняться более интересными вещами, чем автоматизацией.
