Trabalhando com Arquivos de Texto no VBA

(1)

OpenStax-CNX module: m47737 1

Trabalhando com Arquivos de

Texto no VBA

∗

Joao Carlos Ferreira dos Santos

This work is produced by OpenStax-CNX and licensed under the Creative Commons Attribution License 3.0†

O que é VBA

VBA ou Visual Basic for Application é a linguagem de programação utilizada para desenvolvimento em aplicativos do Microsoft Oce. Geralmente é utilizada para criação de macros ou customização de processo usando os aplicativos do Oce. Certamente quem trabalha em escritório e precisa utilizar aplicativos como planilhas eletrônicas e/ou editores de texto já se deparou com a necessidade de automatizar alguma rotina, ou seja, uma tarefa diária. Os aplicativos do MS Oce tem um recurso chamado Gravador de Macros é um recurso que facilita a automação de rotina gerando de forma automática o código VBA daquele processo, porém esse recurso é limitado se utilizado para processos mais complexos, e nesses casos que temos que criar de forma manual o nosso próprio código VBA. Nesse artigo vou mostrar como criar macros no Excel e como desenvolver seu próprio código VBA para automatizar rotinas que não seria possível fazer pelo Gravador de Macros, além de mostrar como utilizar recursos avançados do VBA: leitura de arquivos texto, execução de código SQL e criação de formulário.

Como Criar Macros

Como já foi dito uma macro é a automação de uma rotina (sequência de comandos). A macro pode ser gravada a partir de um aplicativo do MS Oce e ela é executada conforme os passos feitos durante a gravação da mesma. Para car mais claro vou criar uma macro no MS Oce Excel 2010. Para criar você pode usar o caminho: na guia Exibição Macros Gravar Macro... ou então na barra de tarefas clicar no botão

Figure 1

. Em qualquer uma das alternativas escolhidas nós teremos a tela de gravação da macro:

∗_{Version 1.1: Sep 28, 2013 9:22 pm -0500} †_{http://creativecommons.org/licenses/by/3.0/}

(2)

Figure 2

Antes de clicar em OK temos que denir os campos da macro: Nome da macro, Tecla de atalho, Armazenar macro em e Descrição. Após clicar em OK deve-se executar as tarefas que deverão ser gravadas na macro e em seguida parar a gravação. Para parar a gravação podemos usar o botão

Figure 3

(3)

Figure 4

Imagem 02:

Figure 5

Depois de criada, a macro pode ser executada em Exibição Macros Exibir Macros na caixa de dialogo:

(4)

Figure 6

Escolha a macro e clique sobre o botão Executar e todo o processo gravado será feito novamente de forma automática.

Macros Avançadas

Depois de criada a macro pelo assiste podemos edita-la. Entre na macro que foi criada pelo caminho Exibição Macros Exibir Macros quando aparecer a macro clique em Editar conforme imagem abaixo:

(5)

Figure 7

Quando clicar em Editar terá acesso ao código da macro, ou seja, a linguagem VBA propriamente dita. Código da Macro Acima:

Sub Macro1() ' Macro1 Macro ' Range(Selection, Selection.End(xlDown)).Select With Selection.Font .Name = "Arial" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("B2").Select

(6)

Range(Selection, Selection.End(xlDown)).Select Selection.Font.Italic = True Range("C2").Select Range(Selection, Selection.End(xlDown)).Select With Selection.Font .Color = -16777024 .TintAndShade = 0 End With End Sub

Não é difícil de aprender a programar na linguagem VBA isso porque podemos contar com a facilidade de criação de código usando a gravação de macro. Depois de criado o código pelo assiste de macro podemos depurar esse código e acompanhar linha por linha o que é feito na planilha assim podemos ver qual instrução em código corresponde a mudança na planilha.

Conhecendo o Ambiente de Desenvolvimento o VBE

O Visual Basic Editor é um ambiente para desenvolvimento na linguagem VBA. Ele pode ser encontrado praticamente em todas as aplicações do Oce inclusive no Excel e no Access. Para ter acesso a esse ambiente podemos pressionar Alt + F11 e pronto aparecera a seguinte tela:

(7)

Figure 8

A parte marcada de vermelho é a guia de Menus, geralmente a mais usada nessa guia é o menu Ferra-mentas nele temos os itens vistos na imagem abaixo:

(8)

Figure 9

Referencias...: aqui podemos incluir novas bibliotecas para trabalhar com serviços especícos como, por exemplo, conexão a banco de dados e execução de scripts SQL. Por padra o projeto já vem com algumas referencias como podemos ver na imagem abaixo.

(9)

Figure 10

(10)

Figure 11

(11)

Figure 12

Propriedades do Projeto...: aqui podemos congurar coisas como nome do projeto, descrever o projeto e inclusive colocar uma senha de acesso ao código do projeto.

(12)

Figure 13

Assinatura Digital...: podemos criar uma assinatura digital para o projeto e deixa-lo mais conável para execução em distintos ambientes.

(13)

Figure 14

A parte marcada com uma faixa verde é o menu com botão para comandos rápidos como, por exemplo o botão de play que serve para executar a macro e botão quadrado (parada) que serve para parar a execução da macro.

Criando Código VBA Sem o Assistente

O primeiro passo é entender o que são Sub e Function e quais as suas diferenças na criação de códigos VBA. Na pratica, Sub e Function fazem a mesma coisa com a diferença que a Sub é uma função de processamento autônomo enquanto que a Function retorna sempre algum valor ao nal de sua execução. Veja os códigos de exemplo:

Assinatura da Sub Sub somar()

Dim valor1 As Integer Dim valor2 As Integer valor1 = 5

valor2 = 10

MsgBox valor1 + valor2 End Sub

Assinatura da Function Function somar() Dim valor1 As Integer Dim valor2 As Integer valor1 = 5

valor2 = 10

somar = valor1 + valor2 End Function

Analisando os dois códigos percebemos que ambos têm assinaturas bem diferentes um do outro e que seus objetos também são diferentes. No caso da Sub toda a rotina de processamento é executada dentro de seu escopo já no caso da Function tudo indica que o processamento não para dentro de seu escopo, pois o resultado da operação é retornado para o nome da função, ou seja, o processo não acaba.

(14)

Os Principais Tipos de Dados no VBA

No VBA como em outras linguagens temos os tipos de dados para trabalhar com valores do tipo texto, numéricos e booleanos. No VBA o principal tipo é o Variant todos os outros tipos de dados na linguagem derivam dela abaixo temos a tabela dos subtipos variantes da linguagem:

Tamanho String 1 Byte Date 8 Bytes Boolean 2 Bytes Object 4 Bytes Variant 16 Bytes Byte 1 Byte Integer 2 Bytes Long 4 Bytes Single 4 Bytes Double 8 Bytes Currency 8 Bytes Decimal 12 Bytes Tamanho String 1 Byte Date 8 Bytes

(15)

OpenStax-CNX module: m47737 15 Double 8 Bytes Currency 8 Bytes Decimal 12 Bytes Tamanho String 1 Byte Date 8 Bytes Boolean 2 Bytes Object 4 Bytes Variant 16 Bytes Byte 1 Byte Integer 2 Bytes Long 4 Bytes Single 4 Bytes Double 8 Bytes Currency 8 Bytes Decimal 12 Bytes

(16)

Tipo do Dado Tamanho String 1 Byte Date 8 Bytes Boolean 2 Bytes Object 4 Bytes Variant 16 Bytes Byte 1 Byte Integer 2 Bytes Long 4 Bytes Single 4 Bytes Double 8 Bytes Currency 8 Bytes Decimal 12 Bytes Table 1

Declaração de Variáveis no VBA

Uma variável é um espaço reservado na memória do computado no momento da execução do código. Quando trabalhamos com variáveis trabalhamos também com tipos de dados agora se olharmos para tabela anterior podemos entender que a declaração de variáveis usando os tipos da tabela pode inuenciar no espaça reservado na memoria. A declaração de variáveis no VBA não obrigatória mais é muito importante, pois declarando as variáveis do projeto ca muito mais fácil a leitura do código no caso de manutenção.

A declaração é feita usando a palavra reservada Dim <Nome da Variável> As <Tipo da Variável> No código abaixo podemos ver que as variáveis soma e valor1 foram declaradas já a variável valor2 não.

Sub declaraVariaveis() ' Variáveis declaradas Dim valor1 As Integer Dim soma As Double valor1 = 4

valor2 = 5

(17)

Figure 15

Estrutura de Condição e Repetição

No VBA existem varias estruturas de repetição e condição porem falarei de apenas do estrutura de repetição For Eache ... Next e Do While ... Loop e a estrutura de Condição If Then ... End If.

If Then ... End If

Essa estrutura serve para executar instruções dentro de seu escopo caso a condição imposta seja ver-dadeira. Por Exemplo:

Sub testaIf()

Dim idade As Integer idade = 17

If idade >= 18 Then ' pode tirar a CNH End If

End Sub

For Each ... Next

Percorre todos os itens de uma região como se fosse uma lista pegando do primeiro ao ultimo item. Por exemplo, se tivemos uma região na planilha conforme o da imagem abaixo e usarmos esse comando para percorrer essa região os itens serão apresentado de cima para baixo:

Arroz Feijão Café Açúcar Leira em Pó Arroz Feijão Café Açúcar Leira em Pó

(18)

Arroz Feijão Café Açúcar Leira em Pó Produto Arroz Feijão Café Açúcar Leira em Pó Table 2 Do While ... Loop

Essa estrutura executa instruções dentro de seu escopo repetidamente desde que a condição imposta seja verdadeira. Sub testeWhile() Dim i As Integer i = 1 Do While i > 5 i = i + 1 Loop End Sub

1º Produto, 2º Arroz, 3º Feijão, 4º Café, 5º Açúcar, 6º Leite em Pó. Ou seja, não como denir de onde começar a busca, ele sempre vai pegar do índice um em diante.

Criando Códigos Customizados

Agora que já vimos como declarar variáveis e criar funções no VBA vamos a um exemplo pratico que é a importação de arquivos de texto na extensão txt para o Excel. Imaginemos o seguinte: você é um analista responsável pela criação de relatórios em uma empresa de venda de veículos então em um certo dia pediram um relatório de venda constando todas as vendas de cada vendedor até determinada data. Então você descobre que o sistema de onde extrai os relatórios só fornece essa informação em arquivos do tipo txt. Para um arquivo apenas podemos importar o arquivo para o Excel e depois usar o recurso Texto para

(19)

Nesse caso seria mais interessante criar uma rotina para importação desses dados onde colocaríamos todos os arquivos em uma pasta e de lá importaríamos todos de uma só vez. Então vamos fazer isso, criar uma rotina de importação de dados em arquivo de texto. A estrutura no Excel será essa:

Figure 17

O esquema de importa será pegar todos os arquivo da pasta e jogar todos na tabela acima, a informação sobre a lial está no nome de cada arquivo, pois o arquivo é nomeado conforme sua origem de exportação assim sendo a macro deve identicar isso esses são os arquivos na pasta:

Figure 18

A estrutura de cada arquivo é sempre a mesma o que sempre vai mudar será o nome do arquivo. Estrutura do arquivo:

(20)

Figure 19

No VBE menu Inserir Módulo. Agora vamos inserir as referencias necessárias para o funcionamento do projeto no menu Ferramentas Referências... adicione a biblioteca Microsoft Script Runtime conforme mostra a imagem e clique em OK abaixo:

(21)

Figure 20

A biblioteca Microsoft Script Runtime serve para trabalharmos com manipulação de diretórios. É muito importante o uso desse recurso em projetos grandes.

Dentro do modulo que foi criado teremos a sub abaixo: Option Explicit

Sub importaArquivoTexto() Dim pasta As Folder Dim arquivo As File

Dim fso As New FileSystemObject Dim rng As Range

Dim caminhoPasta As String Dim strLinha As String Dim arrLinha As Variant Dim linhaSheet As Long Dim i As Integer

caminhoPasta = ThisWorkbook.Path Worksheets("Consolidado").Activate Range("A1").Select

Set pasta = fso.GetFolder(caminhoPasta) Set rng = ActiveCell.CurrentRegion i = FreeFile

(22)

linhaSheet = rng.Rows.Count + 1 For Each arquivo In pasta.Files

If InStr(arquivo.Name, "Vendas") Then Open arquivo.Path For Input As #i Line Input #i, strLinha

Do While Not EOF(i)

If Not strLinha Like "Vendedor*" Then arrLinha = Split(strLinha, Chr(9)) rng.Cells(linhaSheet, 1) = arrLinha(0) rng.Cells(linhaSheet, 2) = arrLinha(1) rng.Cells(linhaSheet, 3) = arrLinha(2) rng.Cells(linhaSheet, 4) = arrLinha(3) rng.Cells(linhaSheet, 5) = Left(Right(arquivo.Name, 6), 3) linhaSheet = linhaSheet + 1 End If

Line Input #i, strLinha Loop

Close i End If Next End Sub

Vamos entender o código. Primeiro declaramos todas as variáveis da função depois através do comando ThisWorkbook.Path denimos o caminho até a pasta dos arquivos. Esse comando traz o caminho atual do Workbook onde o código é executado. Ativamos a planilha de nome Consolidado com o comando Work-sheets("Consolidado").Activate selecionamos a célula A1 usando o comando Range("A1").Select a partir da célula seleciona denimos qual será a região da planilha (linhas e colunas) que iremos trabalhar através da variável rng fazemos isso usando o comando Set rng = ActiveCell.CurrentRegion. Denimos a pasta que iremos trabalhar através do comando Set pasta = fso.GetFolder(caminhoPasta) passando como parâmetro a variável caminhoPasta que contem o caminho da pasta. Atribuímos um número de pro-cesso disponível para abertura de arquivos no Windows na variável i desta forma i = FreeFile. Para saber-mos onde começar a incluir linhas na região da planilha denisaber-mos a variável linhaSheet usando o numero de linhas encontrado na região do objeto rng mais um, com o comando linhaSheet = rng.Rows.Count + 1 assim começaremos a incluir linhas sempre após o ultimo registro da planilha. Percorrer todos os arquivos da pasta usando o comando For Each arquivo In pasta.Files abre o arquivo corrente com o comando Open arquivo.Path For Input As #i o comando Open serve para abrir o arquivo, arquivo.Path indica o cam-inha e o arquivo, For Input indica o tipo de processo que será realizado no arquivo aberto que nesse caso será apenas leitura, #i indica em que processo o arquivo será alocado. O comando Line Input lê a primeira

(23)

Trabalhando com Arquivos de Texto no VBA