Automação do Excel (COM)

Dicas e conselhos úteis com o AutoHotkey para os seus queridos companheiros
User avatar
Gio
Posts: 423
Joined: 30 Sep 2013, 10:54
Location: Brazil

Automação do Excel (COM)

19 Jul 2018, 12:43

Olá :angel:

O Microsoft Excel, um dos programas componentes do pacote Microsoft Office, é talvez o principal programa utilizado em escritórios no Brasil para a criação de planilhas e tabelas customizadas. Por ser um produto da Microsoft, que também desenvolve o Windows, a Microsoft desenvolveu no Excel uma forma de permitir aos programas do windows que elaborem relatórios nele de forma automática, permitindo sua automação através de linguagens de programação que ofereçam suporte à sua tecnologia COM (Component Object Model). O AutoHotkey possui acesso à tecnologia COM em todas as versões 1.1+.

Assim, temos a vantagem de poder criar programas que geram relatórios em excel ou até mesmo que formatam, editam, copiam e/ou colam valores de PastaDeTrabalhos, entre outras possibilidades. Assim, se você possui um software que disponibiliza relatórios em excel, saiba que você pode criar scripts que leiam esses valores e os trabalhem da forma como você bem entender de forma absolutamente automática usando apenas o AutoHotkey. Saiba também que se você tem o interesse de criar um programa onde o usuário vai controlar alguma operação (tipo o caixa e estoques de uma loja) e precisa imprimir relatórios com dados organizados em tabelas, gerar esses relatórios no Excel é uma excelente forma de permitir ao seu usuário manipular os dados sem alterar o que está salvo no seu programa. Assim, o usuário pode customizar os relatórios do seu programa de forma segura e confortável através do Excel (o que não é tão fácil se você gera isso em arquivo texto ou imagem ou PDF).

Neste tutorial, vamos aprender alguns comandos básicos para interagir com o Excel de dentro de um script do AutoHotkey. Não poderei cobrir tudo, pois o tutorial ficaria muito extenso e levaria muito tempo, mas espero cobrir um boa parte, e se você se interessar em automatizar alguma coisa no Excel que não encontrou aqui, sinta-se livre para perguntar neste tópico mesmo ou no fórum Eu Preciso de Ajuda :thumbup:

:!: AVISO: Este tutorial utiliza bastante os elementos de sintaxe de objetos. Se você não conhece ainda ou nunca utilizou a sintaxe referente à Programação Orientada a Objetos (OOP) do AutoHotkey, sugiro que leia este tutorial antes. De qualquer forma, para ajudá-lo o tutorial vai trazer exemplos com código executável da forma como está escrito sempre que possível.

Requisitos adicionais: AutoHotkey (qualquer versão 1.1+) e Microsoft Excel instalados na máquina.

1. A tecnologia COM

COM, uma sigla em inglês para Component Object Model, é uma tecnologia desenvolvida pela Microsoft para permitir a comunicação entre processos através de uma dinâmica de objetos (wikipedia). Isso significa, a grosso modo, que a tecnologia permite que programas diferentes se comuniquem, e no nosso caso, permite que um script do AutoHotkey se comunique com o Microsoft Excel, passando comandos para executar ações automaticamente nele.

Para fazer isso nós, através do COM, criamos ou nos conectamos a objetos que possuem métodos e valores acessíveis que podem ser manipulados para controlar através de um script o programa desejado (e os programas principais da Microsoft, como o Word, Excel, Internet Explorer e etc, todos permitem isso). O AutoHotkey possui suporte para a tecnologia COM em qualquer versão 1.1+, e o Excel, que será utilizado neste tutorial, também possui, fazendo com que seja possível a comunicação entre os dois programas.

:arrow: Para utilizar a tecnologia COM corretamente, precisamos saber com exatidão o nome dos objetos, métodos e propriedades disponibilizados pelo programa alvo (neste tutorial, o Excel). Isso pode ser um pouco frustrante no início, pois não existe uma lista completa e bem elaborada desses nomes já na sintaxe do AutoHotkey, e como são muitos os programas que trabalham com a tecnologia COM (Word, Excel, Internet Explorer, etc), são muitos os diferentes nomes também. Além disso, os nomes dos objetos e a sua cadeia de conexões também não são muito intuitivos: Muitas vezes temos que ler códigos em outras linguagens e tentar traduzi-los para a linguagem do AutoHotkey. De qualquer forma, conforme nosso conhecimento cresce, fica cada vez mais fácil criar rotinas de automação dos programas usando COM, e um comando que já tiver sido testado com sucesso, vai funcionar para executar aquele tipo de ação sempre que você precisar.

Assim, é importante destacar que já temos algumas listas, e com elas já podemos fazer muita coisa no Excel a partir dos scripts. Neste tutorial, vou colocar uma boa quantidade de comandos, e além disso, vou explicar algumas formas que usamos para identificar novos comandos possíveis de automação do Excel para que possamos traduzi-los à sintaxe do AutoHotkey, como o uso da ferramenta de macros do próprio Excel. Além disso, é importante que saibamos também que já existem muitos outros exemplos de códigos nos fórums do AutoHotkey em inglês, e mesmo que não encontremos um código pronto que utiliza um método específico do objeto que queremos, ainda assim os fórums podem nos ajudar bastante, pois contamos com muitos programadores experientes nisso.

Dessa forma, se você usa Excel regularmente, vale muito a pena aprender sobre o COM e vale a pena usá-lo através do AutoHotkey para automatizar o seu Excel.

Vamos lá? :thumbup:

2. Criando um objeto COM do Excel (ou seja, iniciando o programa Excel conectado a um objeto COM)

Para criar um novo objeto COM utilizamos a função embutida do AutoHotkey ComObjCreate(). O parâmetro necessário para criar um objeto de conexão com uma instância do Excel é o Excel.Application. Assim, criar um novo objeto de excel chamado Excel é muito simples:

Code: [Select all] [Download] GeSHi © Codebox Plus

Excel := ComObjCreate("Excel.Application")


Se você executar o código acima, verá que aparentemente nada acontece. Isso ocorre porque além de criar um objeto do Excel, nós ainda temos que adicionar uma PastaDeTrabalho nele e torná-lo vísivel. Assim, o código mínimo para vermos o AutoHotkey controlar o Excel a partir do COM é este:

Code: [Select all] [Download] GeSHi © Codebox Plus

Excel := ComObjCreate("Excel.Application") ; Primeiro criamos um objeto Excel.
Excel.Workbooks.Add ; Depois adicionamos uma PastaDeTrabalho nele.
Excel.Visible := True ; E finalmente tornamos o objeto visivel.


Execute o código acima e veja o que acontece. Apareceu uma PastaDeTrabalho em branco do Excel. Se você quiser, pode suprimir a terceira linha do código acima que a PastaDeTrabalho vai existir do mesmo jeito, porém sua janela ficará invisível e você não poderá ver o resultado da sua operação. Esta necessidade de ter que programar o tornar da janela visível parece um pouco estranha a princípio, mas saiba que é um recurso muito importante pois assim podemos escrever sequências muito longas de comandos que vão editando a PastaDeTrabalho antes dela ser exibida ao usuário. Assim, posso criar um relatório de 10.000 linhas e o usuário não vai precisar ficar vendo as linhas serem escritas na sua frente. A PastaDeTrabalho vai aparecer para ele quando estiver pronta (pois só vamos usar a linha Excel.Visible := True sempre no final, exceto se de outra forma desejarmos).

Agora aprecie um pouco o código acima. Veja que a sintaxe utilizada é a sintaxe de objetos. Assim, quando executamos a primeira linha, criamos um objeto chamado Excel que vai ser basicamente a nossa conexão àquela sessão do excel que iniciamos. O nome do objeto ser excel não é obrigatório: podemos chamar o objeto como quisermos. Através desse objeto Excel vamos interagir com essa sessão do excel que criamos acionando os vários métodos e propriedades dele, de forma a efetuarmos a automação desejada. O objeto criado já possui todos esses métodos e propriedades prontos, pois pertence a uma classe definida pelo próprio Excel. Portanto, é basicamente isso que a tecnologia COM faz: solicita a criação de um objeto ao programa alvo, e depois solicita o acesso aos métodos e propriedades desse objeto, de modo que a comunicação se dê através dele.

3. Abrindo um arquivo já existente do Excel para interagir com ele

Conforme explicado no item anterior, quando nós criamos um objeto do Excel, ele inicialmente não contém nenhuma PastaDeTrabalho. Isso signfica que no passo anterior, foi preciso que chamássemos um comando na segunda linha para adicionar uma nova PastaDeTrabalho através do método Workbooks.Add pertencente ao objeto que criamos (e que chamamos de Excel).

Mas ao invés de adicionar uma nova PastaDeTrabalho, podemos também abrir uma já existente, que esteja salva em um arquivo. Para fazer isso, precisamos do caminho do arquivo, o qual será enviado como parâmetro do método (função) Open() do objeto workbooks.

O caminho do arquivo precisa estar completo (tipo c:/pasta/pasta2/arquivo.xlsx), mas é possível utilizar variáveis embutidas para completá-lo. Assim, se eu tiver um PastaDeTrabalho chamada "frutas.xlsx" na mesma pasta do script, posso abri-la da seguinte forma:

Code: [Select all] [Download] GeSHi © Codebox Plus

Excel := ComObjCreate("Excel.Application") ; Primeiro, criamos o objeto Excel.
Excel.Workbooks.Open(A_ScriptDir . "/frutas.xlsx") ; Depois, usamos o método open() para abrir o arquivo frutas.xlsx, que se encontra na mesma pasta do script.
Excel.Visible := True ; Depois, tornamos a PastaDeTrabalho visível.


:!: Atenção: Não se esqueça que arquivos do excel podem ter várias extensões (.xlsx, .xls, .csv, etc)

:arrow: Perceba que utilizamos um método no item 2 do tutorial chamado Workbooks.Add e que neste item 3 usamos outro método chamado Workbooks.Open(). A presença dos parênteses ao fim do método Open() é importante porque este método precisa que indiquemos um parâmetro. Assim, é possível chamar o método Add com parenteses usando Excel.Workbooks.Add(), embora os parenteses neste caso não sejam necessários. Uma fonte comum de confusão quando começamos a aprender os comandos COM é que a sintaxe de métodos sem o uso de parênteses se assemelha à sintaxe de acesso à propriedades. Propriedades e métodos não são a mesma coisa e portanto, é importante saber o tipo de cada elementos de sintaxe que vamos aprender. Se você estiver tendo problemas para acertar a sintaxe de um comando, saiba que você pode encontrar informações sobre o tipo do elementos nas páginas de VBA da microsoft (como esta, que demonstra que Workbooks.Add é de fato um método e não uma propriedade).

4. Conectando-se a um objeto COM do Excel já existente (uma PastaDeTrabalho já aberta)

Para conectar nosso script a um objeto já existente, faremos de outra forma. A função embutida a ser utilizada passa a ser ComObjActive(). Assim, o código abaixo se conecta a um objeto do Excel que esteja ativo.

Excel := ComObjActive("Excel.Application")

Uma vez tendo um objeto conectado àquela sessão do excel, podemos também interagir com ele através dos métodos e propriedades que vamos ver a seguir, da mesma forma que faríamos se tivéssemos criado a sessão do excel através do próprio script.

5. Atribuindo valores às células

Para atribuir um valor a uma célula do excel, precisamos saber o seu endereço dentro da PastaDeTrabalho. O excel nomeia as colunas com letras e nomeia as linhas com números. No endereçamento, a letra da coluna vem primeiro e depois vem o número da linha. Assim, a primeira célula da primeira coluna se chama A1. Da mesma forma, a primeira célula da segunda coluna se chama B1 e a segunda célula da primeira coluna se chama A2.

A propriedade Range.Value é a propriedade que utilizamos para atribuir valores diretamente às células da PastaDeTrabalho. Ela pertence nativamente ao objeto Excel. Dessa forma, para fazer com que o valor da terceira célula da quarta coluna se torne pedra azul, o que temos que fazer é chamar Excel.Range("D3").Value := "pedra azul".

Para ver isso acontecer em uma nova PastaDeTrabalho, basta executar o código abaixo.

Code: [Select all] [Download] GeSHi © Codebox Plus

Excel := ComObjCreate("Excel.Application") ; Primeiro criamos um objeto Excel.
Excel.Workbooks.Add ; Depois adicionamos uma PastaDeTrabalho nele.
Excel.Range("D3").Value := "pedra azul" ; Escrevemos o valor pedra azul na célula da quarta coluna e terceira linha.
Excel.Visible := True ; E finalmente tornamos o objeto visivel.


Utilizando a mesma propriedade, podemos escrever valores para quantas células quisermos (e até uma tabela inteira).

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus



Veja que também é possível criar loops para adicionar valores às células, desde que a construção do endereço da célula possa ser sequenciada da forma correta.

6. Salvando o arquivo

Quando terminamos de editar o arquivo, podemos salvá-lo para não perder o nosso trabalho. Para fazer isso, temos algumas opções.

Para salvar um arquivo que já existe (ou seja, quando você abriu uma PastaDeTrabalho já existente ao invés de criar uma) basta chamar Excel.Workbooks.Save(), no entanto, para que isso funcione, você ter um handle (a tradução seria cabo, do tipo que se segura, mas este é na verdade um conceito de programação. É algo como um meio através do qual você pode se referir ao objeto) para o workbook com o qual você estava trabalhando. Conseguir um handle é fácil, basta indicar uma variável para contê-lo no momento em que você vai abrir a PastaDeTrabalho.

Ou seja, onde antes tínhamos Excel.Workbooks.Open(A_ScriptDir . "/frutas.xlsx") agora teremos que colocar PastaDeTrabalho := Excel.Workbooks.Open(A_ScriptDir . "/frutas.xlsx"), de forma que tenhamos um handle para a PastaDeTrabalho que será aberta (nomeamos esse handle de PastaDeTrabalho só para facilitar as coisas).

Depois que temos um handle para a PastaDeTrabalho, fica fácil salvar com o método Save(). Assim, o código completo para abrir a PastaDeTrabalho fruta.xlsx, escrever um monte de coisas nela e depois salvar fica assim:

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus



Se você executar o código acima e tentar fechar a PastaDeTrabalho sem alterar outra coisa no arquivo, verá que a mensagem de "deseja salvar..." não aparece. Isso ocorre porque a linha PastaDeTrabalho.Save() já salvou o arquivo.

Para salvar uma PastaDeTrabalho nova que foi adicionada (logo que não tem caminho anterior válido) ou então para salvar a PastaDeTrabalho que alteramos com outro nome (ou em outro lugar) basta inserir um parâmetro com o caminho do novo arquivo no método SaveAs() (O método SaveAs() é o mesmo que Salvar Como) . Assim, salvar a PastaDeTrabalho no arquivo "pedras.xlsx" na mesma pasta do arquivo do script é feito chamando PastaDeTrabalho.SaveAs(A_ScriptDir . "/pedras.xlsx").

O código acima ficaria então assim:

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus



7. Fechando o Excel

Toda vez que você cria um objeto COM do tipo Excel.Application, você está na verdade criando uma nova sessão do programa Excel. Assim, se você executar várias chamadas ao ComObjCreate("Excel.Application") você vai estar abrindo o Excel várias vezes no computador (mesmo que as PastaDeTrabalhos não estejam visíveis, o que como dito, só ocorre se você definir a propriedade Visible := True do objeto criado (Excel ou qualquer outro nome que você tenha dado).

Assim, se você não tiver o devido cuidado, aliar a rotina de automação que cria novas Pastas de Trabalhos a um botão no seu programa pode fazer o usuário criar até dezenas de sessões do Excel sem nem suspeitar. Para evitar este óbvio inconveniente, você deve sempre tornar as Pastas de Trabalhos visíveis e/ou fechá-las quando for preciso. Tem duas formas de fazer isso: uma é fechar a Pasta de Trabalho usando o método PastaDeTrabalho.Close. A outra é fechar a própria sessão do Excel usando o método Excel.Quit. Tem uma diferença entre as duas: Prefira o método Quit do objeto da aplicação sempre que você quiser terminar aquela sessão do Excel. A razão é que embora o método Close da PastaDeTrabalho possa terminar a sessão também, as vezes ele não termina, pois a pasta de trabalho faz parte da sessão (e não é a sessão em si).

Assim, o código completo, abrindo um arquivo, depois inserindo valores nas células, depois tornando visível a planilha, depois salvando ela, e depois fechando ela, é o seguinte:

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus




8. Coletando valores de uma célua (em uma variável)

Vimos anteriormente que para escrever valores em uma célula, usamos a propriedade Range().Value. Da mesma forma, podemos usar essa propriedade para coletar valores de uma célula (ou seja, podemos ler os valores que estão nas células de uma planilha do excel para dentro de uma variável do AutoHotkey e depois fazermos o que quisermos com eles, como exibir em uma GUI).

Assim, a sintaxe correta para ler o valor de uma célula do Excel com o AutoHotkey é Var := Range("A1").Value[/c]. Veja que especifiquei novamente o endereço da célula dentro da planilha. Isso é necessário, pois tenho que dizer de qual célula quero ler o valor.

Acredito que você já entendeu a característica passo-a-passo de como colocar estes comandos nas linhas correspondentes. Assim, não vou mais colocar código completos, para economizar espaço e facilitar a leitura do tutorial. Pense sempre assim: Para ler o valor da célula eu tenho que abrir o arquivo primeiro (e estar com ele aberto)? É claro que sim! Portanto, fica fácil entender onde colocar este comando na sequência anterior certo?

De qualquer forma, vamos fazer o seguinte: se você quiser criar um novo script que vai abrir o arquivo pedras.xlsx que criamos executando o script anterior e depois vai ler o que está na célula F2 e depois vai exibir em uma MsgBox, basta colocar o script abaixo na mesma pasta que ele e executá-lo:

Code: [Select all] [Download] GeSHi © Codebox Plus

Excel := ComObjCreate("Excel.Application") ; Primeiro criamos um objeto Excel.
Planilha := Excel.Workbooks.Open(A_ScriptDir . "/pedras.xlsx") ; Depois abrimos a planilha frutas.xlsx nele e ao mesmo tempo pegamos o handle da planilha.
Valor_Escrito := Excel.Range("F2").Value
Excel.Quit
Msgbox % Valor_Escrito


9. Escrevendo e coletando valores de mais de uma célula

Vimos anteriormente que para escrever valores em uma célula, usamos a propriedade Range().Value. Também podemos usar a mesma propriedade para escrever valores em várias células ao mesmo tempo. Isso ocorre porque o Excel também trabalha com intervalos de células, ou seja, podemos dizer "da célula A1 até a célula B14". Assim, para escrever o valor Abacate nas células A2, A3, A4, A5 e A6 ao mesmo tempo, basta indicar Excel.Range("A2:A6").Value := "Abacate"

Da mesma forma, podemos ler os valores de várias células. Assim, para ler todos os valores das células entre D2 e E5 (ou seja, D2, D3, D4, D5, E2, E3, E4 e E5), basta usar Var := Excel.Range("D2:E5").Value com um detalhe importante: quando fizermos isso, nossa variável vai conter um objeto de duas dimensões, comportando todos esses valores. Assim, exibir o valor da célula D3 é o mesmo que exibir Var[1,1].

Veja que o objeto criado não usa o mesmo endereçamento do Excel: isso ocorre porque é um objeto do AutoHotkey. Além disso, não copiamos a planilha inteira, de modo que a primeira linha do objeto é a linha 2 da planilha e a primeira coluna do objeto é a coluna D da planilha, mas o objeto só possui parte dessas linhas e colunas, pois ele foi criado apenas com o que tinha entre D2 e E5. Dessa forma, o último valor do objeto é Var[2,4].

10. Formatando e selecionando células

Assim como é possível trabalhar com os valores das células de uma planilha do Excel, também é possível fazer (quase) tudo que podemos fazemos diretamente no Excel através de comandos que automatizam a tarefa. Assim, é possível formatar células de diversas formas, adicionando cor, tornando a fonte maior ou negritando-a, podemos também adicionar bordas às células, aumentar o espaço que elas ocupam, centralizar o texto, etc, etc.

Assim como as possibilidades de formatar células do Excel são muitas, os comandos para automatizar essas formatações também são muitos. Portanto, se eu fosse dedicar um tópico deste tutorial para cada um, o tutorial ficaria simplesmente enorme. Mas como vejo que você já entendeu um bocado da sintaxe e das particularidades, vou fazer o seguinte: vou trazer vários exemplo de formatação, seleção e etc, e depois discutiremos como "procurar" outros códigos para executar outras formatações.

Então aqui vão exemplos de métodos e propriedades que formatam, selecionam e copiam valores e células no Excel:

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus



Ok, agora que você já tem esse monte de exemplos, deve ser capaz de criar rotinas bem intrincadas para gerar arquivos do excel ou selecionar valores para usar no seu programa em AutoHotkey. Assim, vamos ver agora finalmente o que fazer se você precisar realizar alguma ação do excel cujo código exemplo não está na lista.


11. Descobrindo os códigos para realizar outras ações do Excel

Bom, a lista do item anterior é bem generosa, mas não está absolutamente completa. É bem possível que você queira em algum momento programar um script que faça outra coisa automaticamente no Excel (tipo, implementar filtros ou coisa do tipo). Nem todas as ações tem código de exemplo do AutoHotkey, mas praticamente todas podem ser traduzidas para código do AutoHotkey. Isso ocorre porque conforme dito antes, os meios de acesso à tecnologia COM estão implementados, mas os nomes individuais dos objetos, métodos, propriedades e etc de todos os programas que usam a tecnologia COM não tem como ser conhecidos antecipadamente. Então uma das formas de descobrir o nome dos objetos, métodos, e propriedades é basicamente pesquisar em sua fonte, ou seja, nos sites do desenvolvedor do Excel (isso mesmo, a microsoft). O problema de se fazer isso é que os exemplos são disponibilizados em outras linguagens de programação e a microsoft é reconhecida por escrever as páginas de uma forma um tanto quanto pouco didática. De qualquer forma, a página do Excel em VBA pode ajudar a encontrar um método e a sintaxe para escrever isso no AutoHotkey pode ser obtida por tentativa e erro (levando-se em consideração as particularidades do tratamento dos objetos) ou então pergunta no fórum Eu Preciso de Ajuda.

Outra opção bastante utilizada também para encontrar o método correto é usar a ferramenta de gravação de macros do próprio Excel. Ela fica na aba Desenvolvedor, que precisa ser ativada antes de estar visível no seu Excel. Gravando um macro do Excel, você pode ver o código VBA gerado para aquele macro, que pode facilmente revelar o nome do método, propriedade e etc acionado quando você executou uma ação durante a gravação.

Assim, para descobrir o método de colocar um filtro posso fazer algo como iniciar a gravação de um macro do excel, colocar os filtros manualmente, encerrar a gravação, e depois consultar o código gerador buscando os nomes corretos. Uma vez encontrados os nomes, fica mais fácil traduzir para o AutoHotkey através de tentativa e erro (tudo é uma questão de conectar os métodos e propriedades aos objetos corretos, observando o encadenamento correto) e também fica mais fácil pedir ajuda no fórum.


12. Considerações finais

Ufa! Mais um tutorial interessante para quem gosta de automatizar as próprias tarefas! Interagir com planilhas do excel vai permitir ao programador ter acesso a muitos dados de outros programas, bem como fazer com que esses programas tenham acesso aos dados que ele disponibilizar. Também vai dar ao usuário uma forma de interagir com os dados, criando relatórios personalizados, sem prejudicar o que está salvo no programa.

Espero que tenha gostado e sinta-se livre para postar dúvidas sobre o tutorial abaixo ou então abrir um tópico no fórum Eu Preciso de Ajuda.

Forte abraço :thumbup:
"What is suitable automation? Whatever saves your day for the greater matters."
Barcoder - Create QR Codes and other Barcodes using only Autohotkey !!

Return to “Tutoriais”

Who is online

Users browsing this forum: No registered users and 1 guest