Automate business processes in Excel or how to save a girl from rework
My girlfriend is engaged in purchases in the trading network. Recently, the company underwent a large reduction, so the amount of work per employee has increased dramatically. Therefore, she had to regularly stay at work, sometimes even going out on Saturdays. The same problem arose with her colleagues.
About the decision that allowed my girlfriend to return home on time, you can read under the cut.
At the same time, a significant part of what they did can be automated: receiving data from the front, filling in excel documents, updating data in the front, etc.
')
The most reasonable thing that could be done in this situation is to write the TOR for the development of the necessary functionality and, through the authorities, transfer it to the developers. However, the reduction has 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, there is increased surveillance of what employees do at work and in which applications they work.
Following all these unfavorable circumstances and my desperate desire to help, I decided to write Excel functions in VBA, thanks to which it would be possible to automate the routine operations of my girlfriend and her colleagues.
As a starting point, I chose the task for which the girl spent the most time on her feelings. As part of this task, the girl needed:
Considering that my girlfriend did not have knowledge in programming, it was necessary to make a tool with a simple and familiar interface in the form of functions in excel. Sequences of actions must 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. The most adequate in this situation seems to me to just 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, it's nothing complicated, I thought, just counting one or two cells from the active cell and write down the X and Y coordinates. However, ActiveCell.Offset (0, 1) .Value = x did not work. The value has not changed. And as a result of the execution of an error. After checking various assumptions, it turned out that a change in the value on a sheet leads to recalculation of the entire sheet, and hence the formula that causes this recalculation. In order to circumvent this restriction, it was necessary instead of a direct call from the function of certain actions to replace these calls with Evaluate, which made it possible to achieve the desired one.
As a result, we got the function PrintCursorPosition (), which wrote the cursor positions at the moment of the function execution into two cells to the right. 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 used SetCursorPos from the same user32 library. In order to use this function, it had to transfer the x and y coordinates, which were memorized earlier, to the input. With SetCursorPosition (x, y), I was able to move the cursor along the 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, I was able to simulate pressing the corresponding keys. Initially, I planned to write one function MouseButtonPrees (flag), where flag is the designation of the key pressed, but after the first demonstration, the girl realized that it was better to replace the group with the functions LeftClick (), RightClick () and DoubleClick (). This approach makes it easier to read the final function.
In VBA, there is a SendKeys operator that performs all the necessary actions. The text is easily transferred to the function by reference to the cell and works without problems. However, pressing special keys (Enter, Tab, Alt, Ctrl, arrows on the keyboard, etc.) caused rejection (you had to write them in curly brackets {ENTER} to press them). Therefore, for the most frequently used, I wrote functions like PressEnter (). For rarely used, I created a cheat sheet in the same document.
The transfer of information between the system and Excel was carried out 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 the data from the buffer was taken to MSForms.DataObject and transferred to a specific cell.
The problems started right away.
The process of writing a sequence script 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 processed, which was extremely annoying, especially if the time to complete the group of actions took more than 10 seconds. To solve this problem, I made a check for the presence of the name of the function in the text of the active cell formula. That helped.
In addition, during testing, I added the WaitS (seconds) and WaitMS (miliseconds) wait functions in order to keep track of what worked and what did not. It is based on Sleep from the kernel32 library. The difference between WaitS and WaitMS is that the WaitMS time is in milliseconds, and the WaitS time is in seconds.
Another problem was the inconsistent performance of functions when pulling into the 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, and so on. In this case, the sequences themselves were performed from beginning to end without problems. In order to get rid of this behavior, disabled multithreaded calculations in Excel settings (Excel Options -> Advanced -> Formulas).
Explaining how to work with this and teaching how to use it, he let his girlfriend automate the processes in the company in such an ungrateful way.
Thanks to this automation, it was possible to reduce the time from three hours to 30 minutes. At the same time, automation has allowed several changes in the approach to the process of uploading and downloading data. Now Unloading occurs at the time when my girlfriend leaves for lunch, and loading at night. Thus, it can be said that the workload has decreased by almost half of 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.
At the same time, a significant part of what they did can be automated: receiving data from the front, filling in excel documents, updating data in the front, etc.
')
The most reasonable thing that could be done in this situation is to write the TOR for the development of the necessary functionality and, through the authorities, transfer it to the developers. However, the reduction has 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, there is increased surveillance of what employees do at work and in which applications they work.
Following all these unfavorable circumstances and my desperate desire to help, I decided to write Excel functions in VBA, thanks to which it would be possible to automate the routine operations of my girlfriend and her colleagues.
Product Requirements
As a starting point, I chose the task for which the girl spent the most time on her feelings. As part of this task, the girl needed:
- Move the article from the excel file to the front-office system search string;
- For this article from the issuance of the search get the purchase price, sales, a number of additional values;
- Process the collected data in excel to create the final price of the product;
- Upload prices back to the system.
- It took about 3 hours to upload and download data per day.
Considering that my girlfriend did not have knowledge in programming, it was necessary to make a tool with a simple and familiar interface in the form of functions in excel. Sequences of actions must be defined simply as a sequence of functions. In a word, KISS .
Based on this case, I formed the following functional requirements:
- Control mouse (move, keystroke) to highlight the corresponding elements on the screen;
- Simulate pressing the buttons on the keyboard to enter data;
- Transfer data from Excel to third-party applications;
- Getting data from the application in Excel;
- Perform operations of the same type when drawing a formula in Excel.
Mouse and first difficulties
Before moving the cursor anywhere, you need to understand exactly where to move it. The most adequate in this situation seems to me to just 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, it's nothing complicated, I thought, just counting one or two cells from the active cell and write down the X and Y coordinates. However, ActiveCell.Offset (0, 1) .Value = x did not work. The value has not changed. And as a result of the execution of an error. After checking various assumptions, it turned out that a change in the value on a sheet leads to recalculation of the entire sheet, and hence the formula that causes this recalculation. In order to circumvent this restriction, it was necessary instead of a direct call from the function of certain actions to replace these calls with Evaluate, which made it possible to achieve the desired one.
As a result, we got the function PrintCursorPosition (), which wrote the cursor positions at the moment of the function execution into two cells to the right. 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 used SetCursorPos from the same user32 library. In order to use this function, it had to transfer the x and y coordinates, which were memorized earlier, to the input. With SetCursorPosition (x, y), I was able to move the cursor along the 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, I was able to simulate pressing the corresponding keys. Initially, I planned to write one function MouseButtonPrees (flag), where flag is the designation of the key pressed, but after the first demonstration, the girl realized that it was better to replace the group with the functions LeftClick (), RightClick () and DoubleClick (). This approach makes it easier to read the final function.
Keyboard
In VBA, there is a SendKeys operator that performs all the necessary actions. The text is easily transferred to the function by reference to the cell and works without problems. However, pressing special keys (Enter, Tab, Alt, Ctrl, arrows on the keyboard, etc.) caused rejection (you had to write them in curly brackets {ENTER} to press them). Therefore, for the most frequently used, I wrote functions like PressEnter (). For rarely used, I created a cheat sheet in the same document.
The transfer of information between the system and Excel was carried out 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 the data from the buffer was taken to MSForms.DataObject and transferred to a specific cell.
Testing and performance issues
The problems started right away.
The process of writing a sequence script 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 processed, which was extremely annoying, especially if the time to complete the group of actions took more than 10 seconds. To solve this problem, I made a check for the presence of the name of the function in the text of the active cell formula. That helped.
In addition, during testing, I added the WaitS (seconds) and WaitMS (miliseconds) wait functions in order to keep track of what worked and what did not. It is based on Sleep from the kernel32 library. The difference between WaitS and WaitMS is that the WaitMS time is in milliseconds, and the WaitS time is in seconds.
Another problem was the inconsistent performance of functions when pulling into the 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, and so on. In this case, the sequences themselves were performed from beginning to end without problems. In order to get rid of this behavior, disabled multithreaded calculations in Excel settings (Excel Options -> Advanced -> Formulas).
results
Explaining how to work with this and teaching how to use it, he let his girlfriend automate the processes in the company in such an ungrateful way.
Thanks to this automation, it was possible to reduce the time from three hours to 30 minutes. At the same time, automation has allowed several changes in the approach to the process of uploading and downloading data. Now Unloading occurs at the time when my girlfriend leaves for lunch, and loading at night. Thus, it can be said that the workload has decreased by almost half of the working day, which allowed my girlfriend to return home on time and we can do more interesting things than automation.
Source: https://habr.com/ru/post/447346/
All Articles