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
Muito conceitulal, muito bom
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.
Meu caro, se quiser me enviar o arquivo, posso dar uma olhada: usuariosdoexcel@gmail.com.
[ ]s
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.
Pedro, você está se referindo ao filtro de país?
Excelente.
Parabéns.
Resolveu um problema do meu trabalho.
Socializei o conhecimento com os colegas de trabalho.
Parabéns, de verdade!