O Microsoft Excel possui vários recursos e um deles é a geração automática de relatórios. Você pode criar planilhas interativas para simplificar o processo de inserção de dados para outras pessoas na pasta de trabalho, ao mesmo tempo que automatiza a geração de relatórios. Ambos os recursos requerem conhecimento suficiente do Visual Basic. As etapas para realizar as duas tarefas são descritas a seguir.
Etapa
Método 1 de 2: Criando uma planilha interativa
Etapa 1. Determine o layout da planilha
O layout da planilha deve ser feito para que outras pessoas possam encontrar os campos necessários para inserir os dados.
Os layouts de planilha podem ser dispostos horizontal ou verticalmente. A maioria dos usuários acha mais fácil trabalhar com um layout vertical, especialmente se a planilha for impressa
Etapa 2. Crie rótulos de texto para a planilha
Escreva um rótulo no topo de cada coluna e à esquerda de cada célula na coluna que você planeja usar como entrada de dados.
Etapa 3. Pressione as teclas alt="Image" e F11 juntas
Essa combinação de teclas abrirá o editor do Microsoft Visual Basic.
Etapa 4. Clique duas vezes em "This Workbook" no painel "Project-VBA Project" no canto superior esquerdo
Uma janela para escrever o código aparecerá na seção principal do editor.
Etapa 5. Selecione "Procedimento" no menu Inserir
A caixa de diálogo Adicionar procedimento será exibida.
Etapa 6. Insira o nome do procedimento no campo Nome
Digite um nome significativo para o procedimento, como "SumExpenses" se a planilha for usada para relatar despesas de viagem. Clique em OK para fechar a caixa de diálogo.
- Os nomes de procedimentos não podem conter espaços, mas podem usar um sublinhado (_) para substituir os espaços.
- Depois que a caixa de diálogo Adicionar procedimento fechar, uma linha aparecerá com o rótulo "Public Sub" seguida do nome do procedimento. Abaixo dessa linha há um espaço e as palavras "End Sub".
Etapa 7. Insira o código para cada campo de entrada na planilha
Você escreverá duas linhas de código para cada entrada.
- A primeira linha de código está na forma de "Intervalo (" nome da célula "). Selecione", "nome da célula" representa a célula onde a entrada é inserida. Preencha o nome da célula imediatamente à direita do rótulo de texto. Se o rótulo de texto estiver na célula A2, o campo de entrada será a célula B2 (Intervalo ("B2"). Selecione). As aspas antes e depois do nome da célula ainda são escritas neste estágio, mas não estão mais incluídas na instrução de código completa.
- O código na segunda linha é "ActiveCell. Value = InputBox (" InputPrompt ")". "InputPrompt" representa o texto que aparecerá para notificar o usuário sobre o tipo de dados a inserir na célula de entrada. Por exemplo, se a célula de entrada for preenchida com despesas de alimentação, substitua "InputPrompt" por "Insira despesas totais para refeições, incluindo dicas." (As aspas para o texto de instrução de entrada ainda estão incluídas, enquanto as aspas antes e depois do comando não precisam ser escritas.)
Etapa 8. Insira o código para cada campo de cálculo
Novamente, as duas linhas usadas são as mesmas de antes, mas desta vez ActiveCell. Value é um cálculo de uma função numérica, por exemplo SUM, onde a função InputBox é usada para exibir instruções de entrada.
Etapa 9. Adicione uma linha de código para salvar sua planilha interativa
O formato é "ActiveWorkbook. SaveAs Filename: =" Filename.xls "." Filename "é o nome da planilha interativa. (As aspas antes e depois de" Filename.xls "permanecem escritas, enquanto as aspas para todos os comandos são não requerido.)
Se você estiver usando o Microsoft Excel versão 2007 ou posterior, use ".xlsx" em vez de ".xls". No entanto, se houver usuários de planilhas interativas com a versão do Excel 2003 e anterior, eles não poderão usar a planilha sem um plug-in de leitor
Etapa 10. Pressione alt="Image" e as teclas Q simultaneamente
O editor do Visual Basic será fechado.
Etapa 11. Pressione alt="Image" e as teclas F8 simultaneamente
A caixa de diálogo Macro aparecerá.
Etapa 12. Clique no nome do procedimento na lista Macros
Se o procedimento criado for o único da lista, ele será selecionado automaticamente.
Etapa 13. Clique no botão Opções
Você será solicitado a inserir um caractere do teclado para usar como um atalho com a tecla Ctrl. Escolha uma letra significativa que não tenha sido usada como caractere de atalho, como "e" para "entrada".
Etapa 14. Clique em "OK" para fechar a caixa de diálogo Opções de macro
Agora você pode distribuir planilhas interativas aos usuários. Depois de abri-lo, o usuário pode usar as teclas de atalho para fazer as entradas e seguir as instruções que você criou para preencher os dados.
Método 2 de 2: Automatizar a geração de relatórios
Etapa 1. Crie um relatório em uma Tabela Dinâmica
As tabelas dinâmicas são projetadas para resumir dados para que você possa comparar números e identificar tendências. A tabela dinâmica deve estar relacionada aos dados que estão em um dos processadores de dados ou importados de um banco de dados específico.
Etapa 2. Escreva um script do Visual Basic para abrir e fechar o relatório
O script deve ser capaz de executar as várias funções listadas abaixo. Cada função será descrita seguida pelo código fornecido entre parênteses para implementá-la. Ao escrever o código real, escreva-o em um único bloco, substituindo o nome no exemplo pelo seu próprio e não inclua parênteses no início e no final do exemplo de código.
- Abra a planilha no modo somente leitura. [DIM XLAppSet XLApp = CreateObject ("Excel. App") xlapp.visible = falsexlapp.workbooks.open / excelloc / filename.xls, 3,]
- Recarregue os dados e salve o relatório, neste exemplo ele é salvo como um PDF com a legenda da data. [Truexlapp.activeworkbook. RefreshAllxlapp.activeworkbook. ExportAsFixedFormat xlTypePDF, / pdfloc / reportname_ & DatePart ("yyyy, Now ()) &" - "& Right (" 0 "& DatePart (" m ", Now ()), 2) & "-" Right ("0" & DatePart ("d", Now ()), 2) & ".pdf"] Se o formato do documento de saída for diferente, substitua o formato ".pdf" por a extensão adequada conforme desejado.
- Feche a planilha sem salvá-la e feche o Excel. [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]
- Use ".xlsx" em vez de ".xls" no final da planilha se a planilha for salva no Excel 2007 e em um formato posterior baseado em XML.
Etapa 3. Escreva um script em lote para iniciar o script Visual Basic
O propósito de escrever o script é que os scripts do Visual Basic possam ser executados automaticamente. Sem o script em lote, os scripts VB precisam ser executados manualmente.
O script é escrito no seguinte formato, substituindo por seu próprio nome de pasta e nome de arquivo para o nome dado neste exemplo: [cscript / nologo / fileloc / script.vbs]
Etapa 4. Grave um script em lote para garantir que o arquivo de saída exista conforme criado
Seu script deve executar as funções abaixo. Cada função será seguida pelo código fornecido entre parênteses para implementá-la. Ao escrever o código real, escreva-o em um único bloco, substituindo o nome no exemplo pelo nome de que você precisa e não inclua parênteses no início e no final do código de amostra.
- Verifique se há um arquivo de saída. [Para / f "tokens = 2-4 delims = /" %% a in ('date / t') defina rreport = reportname _ %% c - %% a - %% b.pdf)] Se o formato do arquivo de saída não for PDF, substitua ".pdf" pela extensão apropriada.
- Se o arquivo / relatório de saída existir, envie-o como um e-mail para a pessoa que precisa dele. [Se existir / pdfloc \% rreport% (sendmail -f [email protected] -t destinatá[email protected] -u Relatório agendado -m relatório %% report% anexado. -A / pdfloc \% rreport% -s seu servidor: porta -xu nome de usuário -xp senha)]
- Se o arquivo / relatório de saída não existir no local especificado, crie um procedimento para que ele possa enviar a você uma mensagem de que a entrega falhou. [Caso contrário (sendmail -f [email protected] -t [email protected] -u Relatório não executado -m arquivo% rreport% não existe em / pdfloc / -s seu servidor: porta -xu nome de usuário -xp senha)]
Etapa 5. Certifique-se de que a pasta "Desktop" esteja no computador
Você deve verificar a existência da pasta Desktop para sistemas de 32 e 64 bits. Caso contrário, o Excel e a planilha devem ser abertos manualmente.
- Local do sistema de 32 bits: c: / windows / system32 / config / systemprofile
- Local do sistema de 64 bits: c: / windows / syswow64 / config / systemprofile
Etapa 6. Agende tarefas para executar scripts conforme necessário
Os scripts em lote devem ser executados sequencial e continuamente, mesmo se ninguém estiver usando o computador. Os privilégios devem ser definidos para a configuração mais alta possível.
