Tutorial: Comparar arquivos do Excel e enviar e-mails com erros de reconciliação

Neste tutorial, criaremos uma automação para comparar os valores em uma planilha que contém faturas com os valores de um arquivo CSV que contém um resumo por fornecedor. Se ocorrer algum erro de reconciliação entre os arquivos, criaremos um arquivo CSV com detalhes e enviaremos o arquivo por e-mail.

Vamos criar um projeto com as seguintes atividades:

1. Duas atividades Use Excel File para indicar o arquivo do Excel com as faturas e o arquivo no qual queremos reconciliar os dados.
2. Uma série de atividades Write Cell para adicionar cabeçalhos de duas colunas ao arquivo de reconciliação do Excel e para calcular a soma total de faturas de um fornecedor e a diferença entre essa soma e o valor no resumo inicial. Adicionaremos uma atividade Copy Range para copiar as fórmulas que adicionamos para um fornecedor em todas as linhas da planilha, para que possamos executar os mesmos cálculos para todos os fornecedores. Em seguida adicione mais uma atividade Write Cell para adicionar a diferença total a uma célula.
3. Uma atividade If na qual adicionaremos uma condição de que, se houver erros de reconciliação (a soma das diferenças é maior que 0), um arquivo CSV com detalhes deve ser criado e enviado por e-mail para um endereço especificado. Para as tarefas de e-mail, dentro da atividade If adicionaremos uma atividade Use Desktop Outlook App para indicar a conta do Outlook da qual queremos enviar o e-mail e uma atividade Send Email dentro de Use Desktop Outlook App.
4. Configure o projeto e obtenha os arquivos necessários.
   1. Crie um novo projeto em branco usando as configurações padrão.
   2. Download and extract the archive with the automation project in this tutorial using the button at the bottom of this page. Copy the folder data to your project folder.
5. Adicione os arquivos do Excel ao projeto.
   1. Clique em Adicionar atividade no painel do Designer, localize a atividade Use Excel File na caixa de pesquisa na parte superior da tela e selecione-a. Uma atividade Use Excel File é adicionada ao painel Designer.
   2. Na atividade:

      • Clique em Procurar ao lado do campo Arquivo do Excel e, em seguida, navegue até e selecione o arquivo invoices.xlsx

      • No campo Referenciar como, insira Invoices.

        Você indicou que irá trabalhar no arquivo invoices.xlsx, que é conhecido em sua automação como Invoices.

   3. Clique em Adicionar atividade dentro da atividade Use Excel File e adicione uma outra atividade Use Excel File.
   4. Na segunda atividade:

      • Clique em Procurar ao lado do campo Arquivo do Excel e, em seguida, navegue até e selecione o arquivo reconcile.xlsx

      • No campo Referenciar como, insira Rec.

        Você indicou que irá trabalhar no arquivo reconcile.xlsx, que é conhecido em sua automação como Rec.

        

6. Copie os dados para o arquivo de reconciliação.
   1. Clique em Adicionar atividade dentro da segunda Use Excel File, localize a atividade Read CSV na caixa de pesquisa na parte superior da tela e selecione-a. A atividade será adicionada dentro da atividade Use Excel File.
   2. In the Read CSV activity:

      • Clique em Procurar ao lado do campo Ler do arquivo e, em seguida, navegue até e selecione o arquivo summary.csv.

      • Clique em Mais no lado direito do campo Saída para e selecione Rec > Summary [Planilha]. O campo é atualizado com sua seleção [Rec] Summary.

        Você indicou que deseja copiar os dados do arquivo CSV summary.csv para o arquivo Rec na Planilha Summary.

   3. Clique em Adicionar atividade dentro da segunda Use Excel File abaixo da atividade Read CSV, localize a atividade Copy Range na caixa de pesquisa na parte superior da tela e selecione-a. Uma atividade Copy/Paste Range será adicionada dentro da atividade Use Excel File.
   4. Na atividade Copy/Paste Range:

      • Clique em Mais no lado direito do campo Origem e selecione Faturas > Faturas [Planilha]. Sua seleção será exibida no campo como [Invoices] Invoices.

        Você indicou que deseja copiar a planilha Invoices do arquivo Invoices.

      • Clique em Mais no lado direito do campo Destino e selecione Rec > Faturas [Planilha]. Sua seleção é exibida no campo como [Rec] Invoices.

        Você indicou que deseja colar o intervalo copiado na planilha Invoices no arquivo Rec.

        

7. Faça os cálculos necessários para comparar os dados.
   1. No painel Atividades, selecione a guia Excel, arraste a atividade Write Cell e solte-a no segundo Use Excel File abaixo de Copy/Paste Range. Uma atividade Write Cell será adicionada ao painel Designer.
   2. Na atividade Write Cell:

      • Clique em Mais no lado direito do campo O que escrever e, em seguida, selecione Texto. No Construtor de Texto, insira o texto Calculated Amount.

      • Clique em Mais no lado direito do campo Onde escrever e selecione Rec > Indicar no Excel. A planilha é aberta no Excel. Selecione a célula C1 e clique em Confirmar. O campo é atualizado com sua seleção [Rec] Summary!C1.

        Você indicou que deseja que a primeira célula na coluna C da planilha Summary do arquivo Rec contenha o texto Calculated Amount.

   3. Adicione uma segunda atividade Write Cell abaixo da anterior e, na atividade:

      • Clique em Mais no lado direito do campo O que escrever e, em seguida, selecione Texto. No Construtor de Texto, insira o texto =IF(A2 <> "", SUMIF(Invoices!B:B, A2, Invoices!C:C), "").

      • Clique em Mais no lado direito do campo Onde escrever e selecione Rec > Indicar no Excel. A planilha é aberta no Excel. Selecione a célula C2 e clique em Confirmar. O campo é atualizado com sua seleção [Rec] Summary!C2.

        Você indicou que deseja que a célula C2 da planilha Summary no arquivo Rec contenha a fórmula =IF(A2 <> "", SUMIF(Invoices!B:B, A2, Invoices!C:C), ""). Essa fórmula verifica se a célula do fornecedor (A2) não está vazia e soma os valores na coluna C da planilha Invoices com os do fornecedor na célula A2 da planilha Summary.

   4. Adicione uma terceira atividade Write Cell abaixo da anterior e, na atividade:

      • Clique em Mais no lado direito do campo O que escrever e, em seguida, selecione Texto. No Construtor de Texto, insira o texto Difference.

      • Clique em Mais no lado direito do campo Onde escrever e selecione Rec > Indicar no Excel. A planilha é aberta no Excel. Selecione a célula D1 e, em seguida, clique em Confirmar. O campo é atualizado com sua seleção [Rec] Summary!D1.

        Você indicou que deseja que a primeira célula na coluna D da planilha Summary no arquivo Rec contenha o texto Difference.

   5. Adicione uma quarta atividade Write Cell abaixo da anterior e, na atividade:

      • Clique em Mais no lado direito do campo O que escrever e, em seguida, selecione Texto. No Construtor de Texto, insira o texto =IF(A2 <> "", C2-B2, "").

      • Clique em Mais no lado direito do campo Onde escrever e selecione Rec > Indicar no Excel. A planilha é aberta no Excel. Selecione a célula D2 e clique em Confirmar. O campo é atualizado com sua seleção [Rec] Summary!D2.

        Você indicou que deseja que a célula D2 da planilha Summary no arquivo Rec contenha a fórmula =IF(A2 <> "", C2-B2, ""). Essa fórmula verifica se a célula do fornecedor (A2) não está vazia e calcula a diferença entre os valores nas células C2 e B2 da planilha Summary.

   6. Clique em Adicionar atividade abaixo da última atividade Write Cell, em seguida localize a atividade Copy/Paste Range na caixa de pesquisa na parte superior da tela e selecione-a. Uma atividade Copy/Paste Range será adicionada ao painel Designer.
   7. Na atividade Copy/Paste Range:

      • Clique em Mais no lado direito do campo Origem e selecione Rec > Indicar no Excel. A planilha é aberta no Excel. Selecione o intervalo C2:D2 e clique em Confirmar. O campo é atualizado com sua seleção [Rec] Summary!C2:D2.

      • Clique em Mais no lado direito do campo Destino e selecione Rec > Indicar no Excel. A planilha é aberta no Excel. Selecione o intervalo C3-D20, e clique em Confirmar. O campo é atualizado com sua seleção [Rec] Resumo!C3-D20.

      • No menu suspenso O que copiar, selecione Formulas

        Você indicou que deseja colar a fórmula no intervalo copiado C2:D2 abaixo nas mesmas colunas da planilha.

   8. No painel Atividades, selecione a guia Excel, arraste a atividade Write Cell e solte-a abaixo da última atividade Copy/Paste Range. Uma atividade Write Cell será adicionada ao painel Designer.
   9. Na atividade Write Cell:

      • Clique em Mais no lado direito do campo O que escrever e, em seguida, selecione Texto. No Construtor de Texto, insira a fórmula =SUM(Summary!D:D).

      • Clique em Mais no lado direito do campo Onde escrever e selecione Rec > Indicar no Excel. A planilha é aberta no Excel. Selecione a planilha Faturas, depois a célula F1, e clique em Confirmar. O campo é atualizado com sua seleção [Rec] Invoices!F1.

        Você indicou que deseja que a primeira célula na coluna F da planilha Invoices no arquivo Rec contenha a fórmula =SUM(Summary!D:D). A fórmula adiciona a soma das diferenças na coluna D da planilha Summary à célula F1 na planilha Invoices.

        

8. Enviar um e-mail com detalhes sobre a conciliação se a soma das diferenças for maior que 0.

   1. Clique em Adicionar atividade abaixo da última atividade Write Cell e, em seguida, na caixa de pesquisa na parte superior da tela, localize e selecione If. Uma atividade If é adicionada ao painel Designer.

   2. Na atividade If, selecione Abrir no Editor avançado ao lado do campo Condição para abrir o Construtor de condição.

   3. No Construtor de Condição, adicione a condição "se a soma das diferenças for maior que 0":

      • Clique em Mais no lado direito do campo à esquerda e, em seguida, selecione Rec > Indicar no Excel. A planilha é aberta no Excel. Selecione a planilha Faturas, depois a célula F1, e clique em Confirmar. O campo é atualizado com sua seleção [Rec] Invoices!F1.

      • From the drop-down menu in the middle, select greater than.

      • Clique no Mais do lado direito do campo à direita, selecione Número, insira 0 e clique em Salvar duas vezes.

        As atividades que você adiciona na ramificação Then da atividade If serão executadas se essa condição for atendida.

   4. No painel de atividades, selecione a aba CSV , arraste a atividade Write CSV e solte-a na ramificação Then da atividade If. Uma atividade Write CSV é adicionada ao painel Designer.

   5. Na atividade Write CSV:

      • Clique em Mais no lado direito do campo Escrever em qual arquivo e, em seguida, selecione Texto. No Construtor de Texto, insira reconcile- e, em Mais no lado direito do Construtor de Texto, selecione Notebook > Data [Planilha] > YYYYMMDD [Célula]. O texto no Construtor de Texto é atualizado para reconcile-[Notes] Date!YYYYMMDD. Insira o texto .csv no final e clique em Salvar. O texto final deve ser reconcile-[Notes] Date!YYYYMMDD.csv.

      • Clique em Mais no lado direito do campo Write from e selecione Rec > Resumo [Planilha]. O campo é atualizado com sua seleção [Rec] Summary.

        Você indicou que deseja criar um arquivo CSV na pasta do projeto contendo a palavra Reconciliar no nome junto da data de hoje e que quer copiar nele a planilha Summary do arquivo Rec.

   6. No painel Atividades, selecione a guia E-mail e, em seguida, arraste a atividade Use Desktop Outlook App e solte-a na ramificação Then da atividade If abaixo da atividade Write CSV.

   7. Na atividade, a conta de e-mail padrão já está selecionada no campo Conta. Se você quiser usar uma conta diferente, selecione-a no menu suspenso.

      Na campo Referenciar como, deixe o valor padrão Outlook como o nome pelo qual se referir à conta na automação.

   8. No painel Atividades, arraste a atividade Send Email e solte-a dentro da atividade Use Desktop Outlook App.

   9. Na atividade Send Email:

      • Clique em Mais no lado direito do campo Conta e, em seguida, selecione Outlook.

      • Clique em Mais no lado direito do campo Para e, em seguida, selecione Texto. Na janela Construtor de Texto, insira um endereço de e-mail para onde será enviado o e-mail. Por exemplo, você pode inserir seu próprio endereço de e-mail para enviar o e-mail para você mesmo. Se você deixar a opção Salvar como rascunho selecionada, a automação não envia o e-mail, em vez disso, salva a mensagem na pasta Rascunhos do Outlook.

      • Clique em Mais no lado direito do campo Assunto e, em seguida, selecione Texto. Na janela Construtor de Texto, insira Reconciliation errors for e, em Mais no lado direito do Construtor de Texto, selecione Notebook > Data [Planilha] > Hoje [Célula]. O texto no Construtor de Texto é atualizado para Reconciliation errors for [Notes] Date!Today.

      • Clique em Mais no lado direito do campo Corpo e, em seguida, selecione Texto. Na janela Construtor de Texto, insira o texto para o corpo do e-mail, por exemplo One or more suppliers have reconciliation errors. See attached CSV file for details..

      • Para Anexos, selecione Arquivos e insira o mesmo nome do arquivo usado na atividade Write CSV na etapa 5, reconcile-[Notes] Date!YYYYMMDD.csv. Por exemplo, você pode abrir o Construtor de Texto para o campo Escrever em qual arquivo da atividade Write CSV, copiar todo o texto e depois colar no Construtor de Texto para o campo de anexo de arquivos.

        

   10. Clique em Salvar na faixa de opções do StudioX para salvar a automação e clique em Executar para executar a automação. As faturas e os dados de resumo são copiados para o arquivo reconcile.xlsx, os cálculos são executados e, como o valor total nas faturas não corresponde ao uma no resumo para um dos fornecedores, a planilha Summary do arquivo reconcile.xsls será copiada para o arquivo CSV cujo nome inclui a data de hoje, o arquivo será anexado a um e-mail e enviado ao endereço especificado. No arquivo, você pode identificar o fornecedor com os erros de reconciliação pelo valor na célula D3 que é maior que 0. Baixe o exemplo