Automation of business processes in Excel or how to save a girl from processing
My girlfriend is engaged in purchases in the distribution network. Recently, the company has undergone a large reduction, so the amount of work per employee has increased dramatically. Therefore, she had to regularly stay at work, sometimes even go out on Saturdays. Her colleagues had the same problem.
About the decision that allowed my girlfriend to return home on time, you can read under the cut.
Moreover, a significant part of what they did can be automated: receiving data from the front, filling out excel documents, updating data in the front, etc.
The most reasonable thing that could be done in this situation is to write a statement of work on the development of the necessary functionality and pass it on to the developers through the authorities. However, the reduction affected everyone, including developers. As a result, they did not have enough resources to implement this automation. And the company did not plan to buy specialized RPA systems in the foreseeable future.
In addition, increased monitoring of what employees do at work and in what applications they work.
Based on the result of all these adverse circumstances and my desperate desire to help, I decided to write functions for Excel in VBA, thanks to which it would be possible to automate the routine operations of my girlfriend and her colleagues.
As the starting point, I chose the task for which the girl spent the most of her time according to her feelings. As part of this task, the girl needed:
Given that my girlfriend did not have programming knowledge, it was necessary to make a tool with a simple and familiar interface in the form of functions in excel. Sequences of actions should be defined simply as a sequence of functions. In a word, KISS .
Based on this case, I formed the following functional requirements:
Before moving the cursor anywhere, you need to understand exactly where to move it. It seems to me the most appropriate in this situation to simply remember the position of the cursor when it is over a certain element. In order to remember the coordinates, I used the GetCursorPos function from the user32 library.
Well, we got the coordinates, now it would be nice to remember them. Well, there’s nothing complicated, I thought, just count one or two cells from the active cell and write down the coordinates X and Y. However, ActiveCell.Offset (0, 1) .Value = x did not work. The value has not changed. And as a result of execution, an error. After checking various assumptions, it turned out that changing the value on the sheet leads to a recount of the whole sheet, and hence the formula that causes this recount. In order to get around this limitation, it was necessary to replace these calls with Evaluate instead of a direct call from a function of certain actions, which allowed us to achieve the desired result.
The result was the PrintCursorPosition () function, which recorded in two cells on the right the cursor position at the time the function was executed. It was necessary to type PrintCursorPosition () in the area for entering formulas, move the cursor and press enter on the keyboard.
To move the mouse, I used SetCursorPos from the same user32 library. In order to use this function, it was necessary to pass the value of the x and y coordinates that were previously stored to it as an input. Using SetCursorPosition (x, y), I was able to move the cursor over previously stored coordinates. The first visible result. Hooray!
To simulate mouse actions, I used mouse_event from the same user32 library. By passing the flags of the keys to the input, I was able to simulate the keystrokes of the corresponding keys. Initially, I planned to write one function MouseButtonPrees (flag), where flag is the designation of the pressed key, but after the first demonstration, the girl realized that it was better to replace the group of functions LeftClick (), RightClick () and DoubleClick (). This approach makes it easier to read the resulting function.
In VBA, there is a SendKeys statement that performs all the necessary actions. The text is easily passed to the function by reference to the cell and fulfills without problems. However, pressing special keys (Enter, Tab, Alt, Ctrl, keyboard arrows, etc.) caused rejection (to press them, it was necessary to write them in curly brackets {ENTER}). Therefore, for the most frequently used ones, I wrote functions like PressEnter (). For rarely used, I created a cheat sheet in the same document.
Information was transferred between the system and Excel through copying to the buffer and pasting from the buffer. Copying to the buffer was essentially carried out by simulating pressing Control + C, after which data from the buffer were taken to the MSForms.DataObject and transferred to a specific cell.
The problems started right away.
The process of writing a script for a sequence of actions consists of working out small groups of actions and combining them into one. But when switching to a free cell, the entire sequence was immediately worked out, which was extremely annoying, especially if the time it took for the group of actions to take more than 10 seconds. To solve this problem, I checked for the presence in the formula text of the active cell of the function name. That helped.
In addition, during testing, he added the wait function WaitS (seconds) and WaitMS (miliseconds), in order to keep track of what worked and what didn’t. It is based on Sleep from the kernel32 library. The difference between WaitS and WaitMS is that in WaitMS time is in milliseconds, and in WaitS in seconds.
Another problem was the inconsistent execution of functions when pulled into cells. This was due to the asynchronous calculation of Excel. He distributed the calculation of each cell to different processors. As a result, the sequence is first executed in cell two, then in the fifth, then in the third, etc. Moreover, the sequences themselves were carried out from beginning to end without any problems. In order to get rid of this behavior, I turned off multi-threaded calculations in Excel settings (Excel Settings -> Advanced -> Formulas).
After explaining how to work with all this and learning how to use it, he let his girlfriend automate the processes in the company in such an ungrateful way.
Thanks to such automation, it turned out to reduce the time from three hours to 30 minutes. At the same time, automation made it possible to slightly change the approach to the process of uploading and downloading data. Now unloading occurs during the time when my girlfriend leaves for lunch and loading at night. Thus, we can say that the load decreased by almost half the working day, which allowed my girlfriend to return home on time and we can do more interesting things than automation.
About the decision that allowed my girlfriend to return home on time, you can read under the cut.
Moreover, a significant part of what they did can be automated: receiving data from the front, filling out excel documents, updating data in the front, etc.
The most reasonable thing that could be done in this situation is to write a statement of work on the development of the necessary functionality and pass it on to the developers through the authorities. However, the reduction affected everyone, including developers. As a result, they did not have enough resources to implement this automation. And the company did not plan to buy specialized RPA systems in the foreseeable future.
In addition, increased monitoring of what employees do at work and in what applications they work.
Based on the result of all these adverse circumstances and my desperate desire to help, I decided to write functions for Excel in VBA, thanks to which it would be possible to automate the routine operations of my girlfriend and her colleagues.
Product requirements
As the starting point, I chose the task for which the girl spent the most of her time according to her feelings. As part of this task, the girl needed:
- Transfer the article number from the excel file to the front-office system search bar;
- For this article, from the search results, get the purchase, sale price, a number of additional values;
- Process the collected data in excel to create the final product price;
- Upload prices back to the system.
- Unloading and subsequent data loading per day took about 3 hours.
Given that my girlfriend did not have programming knowledge, it was necessary to make a tool with a simple and familiar interface in the form of functions in excel. Sequences of actions should be defined simply as a sequence of functions. In a word, KISS .
Based on this case, I formed the following functional requirements:
- Control the mouse (move, keystroke) to highlight the corresponding elements on the screen;
- Simulate a keystroke on the keyboard to enter data;
- Transfer data from Excel to third-party applications;
- Retrieving data from an application in Excel;
- Performing the same type of operation when pulling a formula in Excel.
The mouse and the first difficulties
Before moving the cursor anywhere, you need to understand exactly where to move it. It seems to me the most appropriate in this situation to simply remember the position of the cursor when it is over a certain element. In order to remember the coordinates, I used the GetCursorPos function from the user32 library.
Well, we got the coordinates, now it would be nice to remember them. Well, there’s nothing complicated, I thought, just count one or two cells from the active cell and write down the coordinates X and Y. However, ActiveCell.Offset (0, 1) .Value = x did not work. The value has not changed. And as a result of execution, an error. After checking various assumptions, it turned out that changing the value on the sheet leads to a recount of the whole sheet, and hence the formula that causes this recount. In order to get around this limitation, it was necessary to replace these calls with Evaluate instead of a direct call from a function of certain actions, which allowed us to achieve the desired result.
The result was the PrintCursorPosition () function, which recorded in two cells on the right the cursor position at the time the function was executed. It was necessary to type PrintCursorPosition () in the area for entering formulas, move the cursor and press enter on the keyboard.
To move the mouse, I used SetCursorPos from the same user32 library. In order to use this function, it was necessary to pass the value of the x and y coordinates that were previously stored to it as an input. Using SetCursorPosition (x, y), I was able to move the cursor over previously stored coordinates. The first visible result. Hooray!
To simulate mouse actions, I used mouse_event from the same user32 library. By passing the flags of the keys to the input, I was able to simulate the keystrokes of the corresponding keys. Initially, I planned to write one function MouseButtonPrees (flag), where flag is the designation of the pressed key, but after the first demonstration, the girl realized that it was better to replace the group of functions LeftClick (), RightClick () and DoubleClick (). This approach makes it easier to read the resulting function.
Keyboard
In VBA, there is a SendKeys statement that performs all the necessary actions. The text is easily passed to the function by reference to the cell and fulfills without problems. However, pressing special keys (Enter, Tab, Alt, Ctrl, keyboard arrows, etc.) caused rejection (to press them, it was necessary to write them in curly brackets {ENTER}). Therefore, for the most frequently used ones, I wrote functions like PressEnter (). For rarely used, I created a cheat sheet in the same document.
Information was transferred between the system and Excel through copying to the buffer and pasting from the buffer. Copying to the buffer was essentially carried out by simulating pressing Control + C, after which data from the buffer were taken to the MSForms.DataObject and transferred to a specific cell.
Testing and performance issues
The problems started right away.
The process of writing a script for a sequence of actions consists of working out small groups of actions and combining them into one. But when switching to a free cell, the entire sequence was immediately worked out, which was extremely annoying, especially if the time it took for the group of actions to take more than 10 seconds. To solve this problem, I checked for the presence in the formula text of the active cell of the function name. That helped.
In addition, during testing, he added the wait function WaitS (seconds) and WaitMS (miliseconds), in order to keep track of what worked and what didn’t. It is based on Sleep from the kernel32 library. The difference between WaitS and WaitMS is that in WaitMS time is in milliseconds, and in WaitS in seconds.
Another problem was the inconsistent execution of functions when pulled into cells. This was due to the asynchronous calculation of Excel. He distributed the calculation of each cell to different processors. As a result, the sequence is first executed in cell two, then in the fifth, then in the third, etc. Moreover, the sequences themselves were carried out from beginning to end without any problems. In order to get rid of this behavior, I turned off multi-threaded calculations in Excel settings (Excel Settings -> Advanced -> Formulas).
results
After explaining how to work with all this and learning how to use it, he let his girlfriend automate the processes in the company in such an ungrateful way.
Thanks to such automation, it turned out to reduce the time from three hours to 30 minutes. At the same time, automation made it possible to slightly change the approach to the process of uploading and downloading data. Now unloading occurs during the time when my girlfriend leaves for lunch and loading at night. Thus, we can say that the load decreased by almost half the working day, which allowed my girlfriend to return home on time and we can do more interesting things than automation.