Como Automatizar Execução de Macros do VBA através do Pentaho Data Integration

Antes de mais nada Temos vivenciado uma forte demanda na área de análise de dados, um ritmo muito dinâmico onde os analistas de negócio precisam diariamente prover informações para reuniões, suportarem seus gestores com relatórios que os auxiliem na tomada de decisão.

Neste cenário temos uma ferramenta fantástica que é o Microsoft Excel que com sua flexibilidade permite suportar todo este processo.

Assim como neste processo os analistas de negócio ficam sempre muito sobrecarregados pois tem a tarefa de trabalhar os dados, realizar as análises, preparar materiais, construir um racional para justificar os números apresentados, sendo assim seria ótimo se pudéssemos apoiar a este time automatizando seus processos?

Muitas empresas ainda não possuem uma estrutura de BI e trabalham fortemente com uso do excel em suas rotinas e neste ponto podemos com o uso do Pentaho Data Integration aliado ao Excel, promover uma automação muito interessante e sustentável que com certeza irá melhorar produtividade.

Quero então compartilhar com vocês como podemos automatizar a execução de macros do VBA através do Pentaho Data Integration!!

Pensem no seguinte cenário, uma rotina onde você precisa todos os dias abrir um arquivo para realizar atualizações nos dados, salvar, fechar e enviar por e-mail para alguém em sua empresa. Esta rotina poderá ser executada pelo Pentaho e posteriormente agendada através do agendador de tarefas do Windows. Mas como devemos proceder para montar esse fluxo de trabalho?

Vamos lá ao passo a passo!!

Primeiramente Nosso processo terá a seguinte estrutura:

Criar um arquivo como .vbs para que o comando seja executado pelo Pentaho;
Criação de um arquivo .bat para agendar a execução do seu script;
fazer uma pasta de scripts para salvar estes comandos bem como o arquivo excel que será manipulado;
Criar um Job no Pentaho para executar esta rotina;
Configurar o agendador de tarefas para executar estes comandos;

Criando em arquivo .VBS

Primeiro Você poderá criar este arquivo utilizando o bloco de notas e depois salva-lo como script.vbs (ou o nome de sua preferência, não utilize nomes com acentos ou caracteres especiais, pois eles podem ser impeditivos para execução de seu comando). Segue abaixo como deverá ficar o seu comando:

Observem que no campo strPathExcel1 é indicado o caminho do arquivo excel, arquivo este que contem a macro que iremos executar pelo Pentaho. O Campo oXL Visible = False, indica que este processo será realizado em segundo plano, ou seja, o arquivo não será aberto para o usuário, sensacional não é mesmo? No campo oXL.run deverá ser indicado o nome da macro que será executada. Após o salvamento do comando, deverá ficar da seguinte forma:

Criando um arquivo .bat com seu comando VBS

Agora Nesta etapa aqui vai uma dica de ouro!! o Pentaho nos permite executar diretamente o script VBS, utilizando o step shell, porém quando pensamos no processo de agendamento, na implementação deste processo num ambiente corporativo através do agendador de tarefas do Windows, ele pode não funcionar devido a uma série de fatores relacionados a permissão, configuração de pastas entre outros pontos que se tornam impeditivos para colocar este processo em produção. Para contornar estes problemas temos algumas alternativas que falarei a seguir:

Utilizando o bloco de notas coloque o cscript e o caminho do local onde esta o seu comando vbs, após isso salve-o como script.bat
Exemplo:
cscript “C:\data-integration8.3\scripts\script.vbs”

O método cscript inicia um script para ser executado em um ambiente de linha de comando e aplica-se a: Windows Server 2022, Windows Server 2019, Windows Server 2016, Windows Server 2012 R2 e Windows Server 2012. Ao executar este arquivo bat pelo agendador de tarefas a manipulação do arquivo excel para execução das macros será realizado com sucesso.

Mas e se você estiver implementando esta solução no ambiente de um cliente onde a equipe de infra tenha realizado updates, atualizações de antivírus e permissões, como evitar que estas atualizações impeçam a execução do script acima? pois bem, aí vai mais uma dica de ouro!!

Criar essas pastas aqui no servidor onde roda o job pelo agendador:

C:\Windows\System32\config\systemprofile\Desktop
C:\Windows\SysWOW64\config\systemprofile\Desktop

Conceder permissão full ao arquivo excel, script vbs e ao arquivo bat. Exemplo abaixo:

No Pentaho este arquivo bat acima deverá ser executado da seguinte forma:

Crie um JOB, coloque o step shell e configure-o conforme exemplo abaixo:

Segue abaixo um exemplo de JOB para ilustrar a aplicação, neste cenário estamos executando um script que abre um arquivo excel, nesta abertura ocorre uma atualização dos dados, logo após a atualização o arquivo é salvo e aplicação é fechada, na sequência temos uma transformação que busca os dados já atualizados deste arquivo e alimenta uma tabela em banco de dados Postgres.

Criar o arquivo bat do seu Job.

Utilizando um bloco de notas precisamos colocar o comando abaixo, indique o caminho do kitchen.bat e também de seu job e arquivo de Log, salve-o como .bat como nome de sua preferência em seu diretório C:

C:
“C:\Pentaho_Desenvolvimento\data-integration\Kitchen.bat” /file:”coloque o caminho do seu job” /level:Basic>> “coloque o caminho do seu arquivo de log“
pause

Configurar o Agendador de Tarefas

Criar a Tarefa

Configurar o disparador

Configurar a ação

Escolha a ação de iniciar um programa e aponte para o local onde salvou o bat de seu Job.

Configurar as condições

E assim finalizamos mais um artigo do nosso blog

Material de aprofundamento:

E se você quiser aprender tudo sobre Pentaho data integration aprender como fazer uma ingestão de dados robusta e ágil, como fazer seu ETL bem feito, automatizar seu processo de BI e muito mais conheça nosso curso completo de Pentaho Clicando aqui.