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:

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:

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

 

Anúncios

Sobre adilsonsoledade

In Excel We Trust

‹ 0237 – Colar Especial

0239 – Dica de Função – FÓRMULATEXTO ›

Marcado com: Excel, Macro, Planilha, Spreadsheet, VBA
Publicado em Excel, Nível: Avançado