0238 – Manipulando arquivos txt via VBA – Parte 3

Concluindo a série de artigos sobre utilização do VBA para manipular arquivos texto, vamos ver como é possível utilizar comandos SQL para retornar informações de arquivos txt.

Primeiramente vamos entender o conceito de Conexão.

Através do uso de referências a outros programas, é possível executar comandos VBA nativos, por exemplo, do Access e do Word. Uma das referências mais poderosas do VBA é ADO (Activex Database Object Library). Através desta é possível fazer com o que o VBA manipule informações armazenadas em uma base de dados. Para fazer isso é necessário ativar a referência, o que é feito no VBE, conforme mostrado a seguir:

0238_01

Na caixa de diálogo que se abre, você deve buscar Microsoft Activex Objects X.Y Library, onde X.Y é a última versão disponível:

0238_02

Uma vez ativada a referência do ADO, é possível criar uma Conexão, que é uma “ponte de ligação” através da qual o VBA pode acessar os dados contidos nessa base de dados e incluir, apagar, extrair ou modificar essas informações.

Realizada a conexão, iremos criar um objeto chamado Recordset. Um Recordset é um conjunto de informações extraídas da base de dados com base em critérios informados através de um cadeia de texto com comandos em SQL (Structured Query Language) que é linguagem universal de manuseio em bancos de dados.

Feito isso, é possível, por exemplo, trazer apenas um conjunto de dados de interesse para uma planilha do Excel.

Para o nosso exemplo, criamos um arquivo txt, bem simples com as seguintes informações:

Country,Value
Brasil,2
Brasil,3
Argentina,2
Argentina,3
Brasil,4
Brasil,5

Observe que a primeira linha contém cabeçalhos de dados e as demais linhas dados de texto e numéricos separados por vírgulas.

O código a seguir, permite selecionar o arquivo txt e trazer informações para uma planilha. Para este exemplo, estamos interessados nos dados cujo pais é o Brasil. Para informar isso ao Recordset, iremos informar a seguinte linha de comando SQL:

“SELECT * FROM ArquivoTexto.txt  WHERE Country=’Brasil’;”, onde ArquivoTexto.txt deverá ser substituído pelo nome dados ao arquivo txt.

Em seguida o usuário deve informar uma célula de referência para armazenar os dados extraídos.

A seguir a listagem do código:

Sub TextFileImport()
    
'-------------------------------------------------------------------------
'SEÇÃO 00: INFORMAÇÕES DO CÓDIGO E CONTROLE DE REVISÕES
'-------------------------------------------------------------------------

'-------------------------------------------------------------------------
'PARA EXECUTAR ESSE CÓDIGO ATVE A REFERÊNCIA: Microsoft ActiveX Data Objects X.Y Library
'X.Y = VERSÃO MAIS RECENTE DA REFERÊNCIA DISPONÍVEL
'-------------------------------------------------------------------------

'-------------------------------------------------------------------------
'CÓDIGO PARA EXTRAIR DADOS DE ARQUIVO TEXTO PARA UMA PLANILHA
'-------------------------------------------------------------------------
'CRIADO POR: ADILSON SILVA SOLEDADE
'-------------------------------------------------------------------------
'CONTROLE DE REVISÕES
'-------------------------------------------------------------------------
'    Nº   |   DATA   |      AUTOR       | COMENTÁRIOS
'-------------------------------------------------------------------------
'    00   | 07/03/16 | ADILSON SOLEDADE | CRIAÇÃO DO CÓDIGO
'-------------------------------------------------------------------------


'-------------------------------------------------------------------------
'SEÇÃO 01: DECLARAÇÃO DE VARIÁVEIS
'-------------------------------------------------------------------------
   
Dim rdsTxtFileRecordset         As ADODB.Recordset
Dim cnxConnection               As ADODB.Connection
Dim strConnection               As String
Dim strTxtFilePath              As String
Dim strSQLCommand               As String
Dim strTxtFileName              As String
Dim strSQLCondition             As String
Dim strMsgBoxPrompt             As String

Dim rngCellToTransfer           As Range
Dim lngFieldHeaderCount         As Long
Dim intArrayCount               As Integer

Dim arrFileDetails()            As String


'-------------------------------------------------------------------------
'SEÇÃO 02: CONEXÃO COM O ARQUIVO TXT
'-------------------------------------------------------------------------

'Solicitar o nome do arquivo texto
strFileFullName = Application.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", Title:="Selecione um arquivo txt")

'Tratamento de erro se não for selecionado um arquivo
If strFileFullName = "" Then GoTo FalhaDefinicaoArquivo

'Separar os elementos da cadeia de texto
arrFileDetails = Split(strFileFullName, "\")

'Atribuir o último elemento da matriz para strTxtFileName
strTxtFileName = arrFileDetails(UBound(arrFileDetails))

'Utilizar os demais elementos para definir strTxtFilePath
For intArrayCount = LBound(arrFileDetails) To UBound(arrFileDetails) - 1
strTxtFilePath = strTxtFilePath & arrFileDetails(intArrayCount) & "\"
Next intArrayCount

'Criar conexão com o arquivo txt.
'Esta conexão pode ser usada para vários tipos de arquivo texto

strConnection = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" & strTxtFilePath & ";" & "Extensions=asc,csv,tab,txt;"

'Definir a intrução SQL para filtrar os registros a serem trazidos
'Neste exemplo, apenas os registros que tem Brasil no campo Country
strSQLCondition = "WHERE Country='Brasil'"
strSQLCommand = "SELECT * FROM" & Space(1) & strTxtFileName & Space(1) & strSQLCondition & ";"


'-------------------------------------------------------------------------
'SEÇÃO 03: CRIAÇÃO DO RECORDSET E CÓPIA PARA O EXCEL
'-------------------------------------------------------------------------

'Tramento de erro, caso haja falha na criação da conexão
On Error GoTo FalhaNaConexao

'Criar uma conexão com o arquivo texto
Set cnxConnection = New ADODB.Connection
Call cnxConnection.Open(ConnectionString:=strConnection)

'Tratamento de erro, caso haja falha na criação do Recordset
On Error GoTo FalhaNoRecordset

'Criar um objeto Recordset com base na conexão
Set rdsTxtFileRecordset = New ADODB.Recordset
Call rdsTxtFileRecordset.Open(Source:=strSQLCommand, ActiveConnection:=cnxConnection, CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly, Options:=adCmdText)

'Tratamento de erro, caso haja falha na seleção da célula de saída
On Error GoTo FalhaNaSelecao

'Definir a célula que receberá os dados
Set rngCellToTransfer = Application.InputBox(Prompt:="Selecione a célula para receber os dados", Type:=8)

'Desabilitar o tratamento de erros
On Error GoTo 0

'Colocar os cabeçalhos do arquivo txt na primeira linha
For lngFieldHeaderCount = 0 To rdsTxtFileRecordset.Fields.Count - 1
    rngCellToTransfer.Offset(0, lngFieldHeaderCount) = rdsTxtFileRecordset.Fields(lngFieldHeaderCount).Name
Next lngFieldHeaderCount

'Copiar os valores de rdsTxtFileRecordset
rngCellToTransfer.Offset(1, 0).CopyFromRecordset rdsTxtFileRecordset


'-------------------------------------------------------------------------
'SEÇÃO 04: ESVAZIAR VARIÁVEIS E FINALIZAR O CÓDIGO
'-------------------------------------------------------------------------

'Fechar rdsTxtFileRecordset
rdsTxtFileRecordset.Close

'Esvaziar o conteúdo de rdsTxtFileRecordset
Set rdsTxtFileRecordset = Nothing

'Fechar cnxConnection
cnxConnection.Close

'Esvaziar o conteúdo de rdsTxtFileRecordset
Set cnxConnection = Nothing

'Concluir o código
Exit Sub

'-------------------------------------------------------------------------
'SEÇÃO 05: TRATAMENTO DE ERROS
'-------------------------------------------------------------------------

FalhaDefinicaoArquivo:
strMsgBoxPrompt = "Não foi selecionado um arquivo válido"
strMsgBoxPrompt = strMsgBoxPrompt & vbLf & "Execute mais uma vez e informe o arquivo correto."
Call MsgBox(Prompt:=strMsgBoxPrompt, Buttons:=vbOKOnly + vbCritical)
Exit Sub

FalhaNaConexao:
strMsgBoxPrompt = "Houve falha na criação da conexão com o arquivo txt."
strMsgBoxPrompt = strMsgBoxPrompt & vbLf & "Verifique se a referência Microsoft ActiveX Data Objects Library está ativa"
strMsgBoxPrompt = strMsgBoxPrompt & " e se o caminho para o arquivo foi corretamente informado."
Call MsgBox(Prompt:=strMsgBoxPrompt, Buttons:=vbOKOnly + vbCritical)
Exit Sub

FalhaNoRecordset:
strMsgBoxPrompt = "Houve falha na criação do Recordset com o arquivo txt."
strMsgBoxPrompt = strMsgBoxPrompt & vbLf & "Verifique se a instrução SQL está correta e/ou o arquivo txt informado."
Call MsgBox(Prompt:=strMsgBoxPrompt, Buttons:=vbOKOnly + vbCritical)
Exit Sub

FalhaNaSelecao:
strMsgBoxPrompt = "Não foi informada uma referência de célula válida"
strMsgBoxPrompt = strMsgBoxPrompt & vbLf & "Execute mais uma vez e informe uma célula válida."
Call MsgBox(Prompt:=strMsgBoxPrompt, Buttons:=vbOKOnly + vbCritical)
Exit Sub


'-------------------------------------------------------------------------
'SEÇÃO 05: FIM DE CÓDIGO
'-------------------------------------------------------------------------

End Sub

 

In Excel We Trust

Marcado com: , , , ,
Publicado em Excel, Nível: Avançado
5 comentários em “0238 – Manipulando arquivos txt via VBA – Parte 3
  1. Ferreira disse:

    Muito conceitulal, muito bom

  2. José Carvalho disse:

    Achei o artigo muito interessante, porém testei e não funcionou. Copiei o código para um módulo VBA e o conteúdo sugerido para o arquivo texto, além de ter feito a referência ao Microsoft ActiveX Data Objects 6.1 Library, porém apresenta a mensagem de erro de conexão: “Houve falha na criação da conexão com o arquivo txt…”
    Uso o Excel 2016.

  3. Pedro M. disse:

    Como ficaria o código se o filtro de registros fosse “solicitado”, ao usuário (numa janela, por exemplo), em vez de “embutido” no código? Obrigado e parabéns pela “didática” na apresentação dos conteúdos.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Visitas dos usuários
  • 1,969,548

Clique para assinar este blog e receber notificações de novos artigos por email.

Junte-se a 1.477 outros seguidores

Aumente sua interação

Siga-nos do twitter (@usuariosdoexcel).

Acesse o nosso fórum e compartilhe as suas dúvidas.

Curta nossa página no Facebook.

Junte-se ao grupo do LinkedIn.

Visite-nos utilizando o seu smartphone ou tablet.

Recomende este Blog
Visualizar notícias
Site monitorado:
Real Time Web Analytics

Clicky

%d blogueiros gostam disto: