Minha namorada compra em uma cadeia de varejo Recentemente, a empresa passou por uma grande redução, portanto a quantidade de trabalho por funcionário aumentou dramaticamente. Portanto, ela teve que ficar regularmente no trabalho, às vezes até sair aos sábados. Seus colegas tiveram o mesmo problema.
Sobre a decisão que permitiu que minha namorada voltasse para casa a tempo, você pode ler abaixo.
Além disso, uma parte significativa do que eles fizeram pode ser automatizada: recebendo dados de frente, preenchendo documentos do Excel, atualizando dados de frente, etc.
A coisa mais razoável que poderia ser feita nessa situação é escrever uma declaração de trabalho sobre o desenvolvimento da funcionalidade necessária e transmiti-la aos desenvolvedores por meio das autoridades. No entanto, a redução afetou a todos, incluindo desenvolvedores. Como resultado, eles não tinham recursos suficientes para implementar essa automação. E a empresa não planejava comprar sistemas RPA especializados em um futuro próximo.
Além disso, maior monitoramento do que os funcionários fazem no trabalho e em quais aplicativos eles trabalham.
Com base no resultado de todas essas circunstâncias adversas e no meu desejo desesperado de ajudar, decidi escrever funções para o Excel no VBA, graças às quais seria possível automatizar as operações rotineiras da minha namorada e de seus colegas.
Requisitos do produto
Como ponto de partida, escolhi a tarefa pela qual a garota passava a maior parte do tempo de acordo com seus sentimentos. Como parte dessa tarefa, a garota precisava:
- Transfira o número do artigo do arquivo do Excel para a barra de pesquisa do sistema de front-office;
- Para este artigo, a partir dos resultados da pesquisa, obtenha a compra, o preço de venda e vários valores adicionais;
- Processe os dados coletados no excel para criar o preço do produto final;
- Faça o upload dos preços de volta ao sistema.
- O descarregamento e o carregamento subsequente de dados por dia levaram cerca de 3 horas.
Como minha namorada não possuía conhecimentos de programação, era necessário criar uma ferramenta com uma interface simples e familiar na forma de funções no excel. Sequências de ações devem ser definidas simplesmente como uma sequência de funções. Em uma palavra,
KISS .
Com base nesse caso, formei os seguintes requisitos funcionais:
- Controle o mouse (movimento, pressionamento de tecla) para destacar os elementos correspondentes na tela;
- Simule um pressionamento de tecla no teclado para inserir dados;
- Transferir dados do Excel para aplicativos de terceiros;
- Recuperando dados de um aplicativo no Excel;
- Executando as mesmas operações ao extrair uma fórmula no Excel.
O mouse e as primeiras dificuldades
Antes de mover o cursor para qualquer lugar, você precisa entender exatamente para onde movê-lo. Parece-me o mais apropriado nessa situação simplesmente lembrar a posição do cursor quando ele está sobre um determinado elemento. Para lembrar as coordenadas, usei a função GetCursorPos da biblioteca user32.
Bem, nós temos as coordenadas, agora seria bom lembrar delas. Bem, não há nada complicado, pensei, basta contar uma ou duas células da célula ativa e anotar as coordenadas X e Y. No entanto, ActiveCell.Offset (0, 1) .Value = x não funcionou. O valor não foi alterado. E, como resultado da execução, um erro. Depois de verificar várias suposições, verificou-se que a alteração do valor na folha leva a uma recontagem de toda a folha e, portanto, a fórmula que causa essa recontagem. Para contornar essa restrição, em vez de chamar diretamente da função de determinadas ações, foi necessário substituir essas chamadas por Avaliar, o que nos permitiu alcançar o resultado desejado.
O resultado foi a função PrintCursorPosition (), que registrou em duas células à direita a posição do cursor no momento em que a função foi executada. Era necessário digitar PrintCursorPosition () na área para inserir fórmulas, mover o cursor e pressionar enter no teclado.
Para mover o mouse, usei SetCursorPos da mesma biblioteca user32. Para usar essa função, era necessário transmitir o valor das coordenadas xey que foram armazenadas anteriormente como entrada. Usando SetCursorPosition (x, y), consegui mover o cursor sobre as coordenadas armazenadas anteriormente. O primeiro resultado visível. Viva!
Para simular ações do mouse, usei mouse_event da mesma biblioteca user32. Ao passar os sinalizadores das teclas para a entrada, eu consegui simular o pressionamento das teclas correspondentes. Inicialmente, planejei escrever uma função MouseButtonPrees (flag), em que flag é a designação da tecla pressionada, mas após a primeira demonstração, a garota percebeu que era melhor substituir o grupo de funções LeftClick (), RightClick () e DoubleClick (). Essa abordagem facilita a leitura da função resultante.
Teclado
No VBA, há uma instrução SendKeys que executa todas as ações necessárias. O texto é facilmente passado para a função por referência à célula e é preenchido sem problemas. No entanto, pressionar as teclas especiais (Enter, Tab, Alt, Ctrl, setas do teclado etc.) causou rejeição (para pressioná-las, era necessário escrevê-las entre colchetes {ENTER}). Portanto, para os mais usados, escrevi funções como PressEnter (). Para os raramente usados, criei uma folha de dicas no mesmo documento.
As informações foram transferidas entre o sistema e o Excel, copiando para o buffer e colando no buffer. A cópia no buffer foi essencialmente realizada simulando-se pressionar Control + C, após o qual os dados do buffer foram levados para o MSForms.DataObject e transferidos para uma célula específica.
Problemas de teste e desempenho
Os problemas começaram imediatamente.
O processo de escrever uma sequência de ações de script consiste em elaborar pequenos grupos de ações e combiná-los em um. Mas, ao mudar para uma célula livre, toda a sequência foi imediatamente trabalhada, o que foi extremamente irritante, especialmente se o tempo necessário para o grupo de ações levar mais de 10 segundos. Para resolver esse problema, verifiquei a presença no texto da fórmula da célula ativa do nome da função. Isso ajudou.
Além disso, durante o teste, ele adicionou as funções de espera WaitS (segundos) e WaitMS (milissegundos), para acompanhar o que funcionou e o que não funcionou. É baseado no modo de suspensão da biblioteca kernel32. A diferença entre WaitS e WaitMS é que, no WaitMS, o tempo é em milissegundos e no WaitS, em segundos.
Outro problema foi a execução inconsistente de funções quando inseridas nas células. Isso ocorreu devido ao cálculo assíncrono do Excel. Ele distribuiu o cálculo de cada célula para diferentes processadores. Como resultado, a sequência é executada primeiro na célula dois, depois na quinta, depois na terceira, etc. Além disso, as próprias seqüências foram realizadas do começo ao fim sem problemas. Para me livrar desse comportamento, desativei os cálculos multithread nas configurações do Excel (Configurações do Excel -> Avançado -> Fórmulas).
Resultados
Depois de explicar como trabalhar com tudo isso e aprender a usá-lo, ele liberou sua namorada para automatizar os processos na empresa de maneira tão ingrata.
Graças a essa automação, reduziu o tempo de três horas para 30 minutos. Ao mesmo tempo, a automação tornou possível alterar ligeiramente a abordagem do processo de upload e download de dados. Agora a descarga ocorre durante o tempo em que minha namorada sai para almoçar e carrega à noite. Assim, podemos dizer que a carga diminuiu quase metade do dia útil, o que permitiu à minha namorada voltar para casa a tempo e podemos fazer coisas mais interessantes do que a automação.