Vídeo recomendado
https://youtu.be/diWPPPhW-9E
https://youtu.be/diWPPPhW-9E
Importar algumas colunas do TXT para VBA
-
- Acabou de chegar
- Mensagens: 8
- Registrado em: Qua Nov 04, 2020 1:29 pm
Importar algumas colunas do TXT para VBA
Bom dia!
Sou bem iniciante e estou com dúvidas de como prosseguir. Eu tenho um TXT e consegui importar ele pra VBA e até conseguir tirar a primeira linha que não precisava.
Porém, preciso refazer e apenas importar 5 colunas que me interessam, já que a planilha vai de A até a coluna GI.
E ainda por cima preciso ter a primeira linha com o nome que darei para as 5 colunas importadas. Isso é possível e como eu faria isso?
O código que usei:
Sub importar_arquivo()
Application.ScreenUpdating = False
importaArquivo
End Sub
Private Function importaArquivo()
Dim arquivo As String
arquivo = abrirArquivo
If arquivo <> Empty Then
Debug.Print "ok"
ActiveWorkbook.Worksheets.Add 'nova planilha
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & arquivo & "", Destination:=Range("A1"))
.Name = "teste"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
arquivo = ""
End Function
Function abrirArquivo() As String
Dim arquivo As String
On Error GoTo sair:
arquivo = ""
With Application.FileDialog(msoFileDialogFilePicker)
'Selecção de apenas um aquivo
.AllowMultiSelect = False
'Filtro de extenção
.Filters.Add "All files", "*.txt; *.csv"
'Mostra a caixa de dialogo
.Show
'Armazena o caminho do arquivo
arquivo = .SelectedItems.Item(1)
End With
abrirArquivo = arquivo
sair:
arquivo = ""
End Function
E o código que preciso fazer é pegar apenas o que deixei em negrito na primeira linha.
[H2]1|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||51|20201030000000||20201030000000|||||||||||||2||||||988788|9770|9770|||||||0|0|0||||False|UNITS|12||||20250220000000|||||||12|12|0|6|988788||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]2|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||12|20201016000000||20201016000000||||||||1|||||1||||||EMB99999990020000000673|126|126|||||||0|0|0||||False|UNITS|12||||20220621000000||||||TRANSPORTADORA|4.49|17995.8|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]3|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||12|20201016000000||20201016000000||||||||1|||||1||||||EMB99999990020000000674|126|126|||||||0|0|0||||False|UNITS|12||||20220621000000||||||TRANSPORTADORA|4.49|17995.8|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]4|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||12|20201016000000||20201016000000||||||||1|||||1||||||EMB99999990020000000675|126|126|||||||0|0|0||||False|UNITS|12||||20220621000000||||||TRANSPORTADORA|4.49|17995.8|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]5|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||12|20201016000000||20201016000000||||||||1|||||1||||||EMB99999990020000000676|126|126|||||||0|0|0||||False|UNITS|12||||20220621000000||||||TRANSPORTADORA|4.49|17995.8|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]6|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||12|20201016000000||20201016000000||||||||1|||||2||||||EMB99999990020000000669|160|160|||||||0|0|0||||False|UNITS|12||||20220202000000||||||TRANSPORTADORA|4.49|6621.6|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]7|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||12|20201016000000||20201016000000||||||||1|||||2||||||EMB99999990020000000670|160|160|||||||0|0|0||||False|UNITS|12||||20220202000000||||||TRANSPORTADORA|4.49|6621.6|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]8|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||12|20201016000000||20201016000000||||||||1|||||2||||||EMB99999990020000000671|160|160|||||||0|0|0||||False|UNITS|12||||20220202000000||||||TRANSPORTADORA|4.49|6621.6|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]9|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||12|20201016000000||20201016000000||||||||1|||||2||||||EMB99999990020000000672|160|160|||||||0|0|0||||False|UNITS|12||||20220202000000||||||TRANSPORTADORA|4.49|6621.6|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]10|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||51|20201030000000||20201030000000|||||||||||||1||||||EMB99999990020000000742|127|127|||||||0|0|0||||False|UNITS|12||||20220520000000||||||TRANSPORTADORA|2|1720000|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
Sou bem iniciante e estou com dúvidas de como prosseguir. Eu tenho um TXT e consegui importar ele pra VBA e até conseguir tirar a primeira linha que não precisava.
Porém, preciso refazer e apenas importar 5 colunas que me interessam, já que a planilha vai de A até a coluna GI.
E ainda por cima preciso ter a primeira linha com o nome que darei para as 5 colunas importadas. Isso é possível e como eu faria isso?
O código que usei:
Sub importar_arquivo()
Application.ScreenUpdating = False
importaArquivo
End Sub
Private Function importaArquivo()
Dim arquivo As String
arquivo = abrirArquivo
If arquivo <> Empty Then
Debug.Print "ok"
ActiveWorkbook.Worksheets.Add 'nova planilha
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & arquivo & "", Destination:=Range("A1"))
.Name = "teste"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
arquivo = ""
End Function
Function abrirArquivo() As String
Dim arquivo As String
On Error GoTo sair:
arquivo = ""
With Application.FileDialog(msoFileDialogFilePicker)
'Selecção de apenas um aquivo
.AllowMultiSelect = False
'Filtro de extenção
.Filters.Add "All files", "*.txt; *.csv"
'Mostra a caixa de dialogo
.Show
'Armazena o caminho do arquivo
arquivo = .SelectedItems.Item(1)
End With
abrirArquivo = arquivo
sair:
arquivo = ""
End Function
E o código que preciso fazer é pegar apenas o que deixei em negrito na primeira linha.
[H2]1|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||51|20201030000000||20201030000000|||||||||||||2||||||988788|9770|9770|||||||0|0|0||||False|UNITS|12||||20250220000000|||||||12|12|0|6|988788||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]2|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||12|20201016000000||20201016000000||||||||1|||||1||||||EMB99999990020000000673|126|126|||||||0|0|0||||False|UNITS|12||||20220621000000||||||TRANSPORTADORA|4.49|17995.8|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]3|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||12|20201016000000||20201016000000||||||||1|||||1||||||EMB99999990020000000674|126|126|||||||0|0|0||||False|UNITS|12||||20220621000000||||||TRANSPORTADORA|4.49|17995.8|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]4|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||12|20201016000000||20201016000000||||||||1|||||1||||||EMB99999990020000000675|126|126|||||||0|0|0||||False|UNITS|12||||20220621000000||||||TRANSPORTADORA|4.49|17995.8|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]5|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||12|20201016000000||20201016000000||||||||1|||||1||||||EMB99999990020000000676|126|126|||||||0|0|0||||False|UNITS|12||||20220621000000||||||TRANSPORTADORA|4.49|17995.8|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]6|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||12|20201016000000||20201016000000||||||||1|||||2||||||EMB99999990020000000669|160|160|||||||0|0|0||||False|UNITS|12||||20220202000000||||||TRANSPORTADORA|4.49|6621.6|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]7|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||12|20201016000000||20201016000000||||||||1|||||2||||||EMB99999990020000000670|160|160|||||||0|0|0||||False|UNITS|12||||20220202000000||||||TRANSPORTADORA|4.49|6621.6|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]8|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||12|20201016000000||20201016000000||||||||1|||||2||||||EMB99999990020000000671|160|160|||||||0|0|0||||False|UNITS|12||||20220202000000||||||TRANSPORTADORA|4.49|6621.6|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]9|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||12|20201016000000||20201016000000||||||||1|||||2||||||EMB99999990020000000672|160|160|||||||0|0|0||||False|UNITS|12||||20220202000000||||||TRANSPORTADORA|4.49|6621.6|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
[H2]10|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||51|20201030000000||20201030000000|||||||||||||1||||||EMB99999990020000000742|127|127|||||||0|0|0||||False|UNITS|12||||20220520000000||||||TRANSPORTADORA|2|1720000|0|7|||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
- Mikel Silveira Fraga
- Jedi
- Mensagens: 1173
- Registrado em: Sex Mai 27, 2011 3:27 pm
- Localização: Governador Valadares - MG
- Contato:
Re: Importar algumas colunas do TXT para VBA
Esteves Arthur, bom dia.
Cara, em relação a extração dos dados, buscando um melhor desempenho no processo, eu utilizaria a estrutura explicada no link abaixo:
- Read Data from Text File
Para extrair uma informação específica, eu utilizaria a função Split da biblioteca VBA, jogando todos os dados para uma Matriz (Array). Dessa forma, já teria todas as colunas divididas em cada bloco da minha matriz, sendo apenas necessário referenciar em qual coluna esta a informação que desejo.
Observe a linha abaixo:
Dessa forma, será possível distinguir quais colunas deseja e capturar, sem ter que ficar dando voltas e voltas em códigos longos, e que prejudiquem o desempenho do processo.
Caso queira conhecer mais sobre a função Split e sobre Arrays, da uma procurada aqui no fórum e na documentação da Microsoft. Vai encontrar muito material sobre os temas.
Forte abraço e excelente semana.
Cara, em relação a extração dos dados, buscando um melhor desempenho no processo, eu utilizaria a estrutura explicada no link abaixo:
- Read Data from Text File
Para extrair uma informação específica, eu utilizaria a função Split da biblioteca VBA, jogando todos os dados para uma Matriz (Array). Dessa forma, já teria todas as colunas divididas em cada bloco da minha matriz, sendo apenas necessário referenciar em qual coluna esta a informação que desejo.
Observe a linha abaixo:
Adaptando essa extração para o código acima, Tendo como objetivo a extração do texto "UNITS", o trecho de código ficaria da seguinte forma:[H2]1|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||51|20201030000000||20201030000000|||||||||||||2||||||988788|9770|9770|||||||0|0|0||||False|UNITS|12||||20250220000000|||||||12|12|0|6|988788||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||
Código: Selecionar todos
Do Until EOF(1)
Line Input #1, textline
VariávelNome = VBA.Split(textline, "|") ' Segundo parâmetro é o delimitador existente no arquivo texto.
' Como o valor que desejo esta na coluna 72, utilizo o código abaixo para realizar a captura do valor.
Range("A1").Value = VariávelNome(71) ' Lembrando que o índice da primeira coluna de uma Matriz, é por padrão 0. Dessa forma, para a coluna 1, utilizo o índice 0. Para a coluna 72, utilizo o índice 71.
Loop
Caso queira conhecer mais sobre a função Split e sobre Arrays, da uma procurada aqui no fórum e na documentação da Microsoft. Vai encontrar muito material sobre os temas.
Forte abraço e excelente semana.
-
- Acabou de chegar
- Mensagens: 8
- Registrado em: Qua Nov 04, 2020 1:29 pm
Re: Importar algumas colunas do TXT para VBA
Vou ler e testar aqui e já dou um retorno até amanhã. Obrigado por responder. Gratidão!Mikel Silveira Fraga escreveu: ↑Qui Nov 05, 2020 9:19 am Esteves Arthur, bom dia.
Cara, em relação a extração dos dados, buscando um melhor desempenho no processo, eu utilizaria a estrutura explicada no link abaixo:
- Read Data from Text File
Para extrair uma informação específica, eu utilizaria a função Split da biblioteca VBA, jogando todos os dados para uma Matriz (Array). Dessa forma, já teria todas as colunas divididas em cada bloco da minha matriz, sendo apenas necessário referenciar em qual coluna esta a informação que desejo.
Observe a linha abaixo:Adaptando essa extração para o código acima, Tendo como objetivo a extração do texto "UNITS", o trecho de código ficaria da seguinte forma:[H2]1|2|||10|49.92|1818481.6|0|S0011||||||||||||S0011|||||||||||||51|20201030000000||20201030000000|||||||||||||2||||||988788|9770|9770|||||||0|0|0||||False|UNITS|12||||20250220000000|||||||12|12|0|6|988788||||||||||0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||0|0|0|0|0||||||||||||||||||||||||Dessa forma, será possível distinguir quais colunas deseja e capturar, sem ter que ficar dando voltas e voltas em códigos longos, e que prejudiquem o desempenho do processo.Código: Selecionar todos
Do Until EOF(1) Line Input #1, textline VariávelNome = VBA.Split(textline, "|") ' Segundo parâmetro é o delimitador existente no arquivo texto. ' Como o valor que desejo esta na coluna 72, utilizo o código abaixo para realizar a captura do valor. Range("A1").Value = VariávelNome(71) ' Lembrando que o índice da primeira coluna de uma Matriz, é por padrão 0. Dessa forma, para a coluna 1, utilizo o índice 0. Para a coluna 72, utilizo o índice 71. Loop
Caso queira conhecer mais sobre a função Split e sobre Arrays, da uma procurada aqui no fórum e na documentação da Microsoft. Vai encontrar muito material sobre os temas.
Forte abraço e excelente semana.
-
- Acabou de chegar
- Mensagens: 8
- Registrado em: Qua Nov 04, 2020 1:29 pm
Re: Importar algumas colunas do TXT para VBA
Mikel, eu entendi, contudo não consigo encaixar no meu código. Eu teria que fazer uma importação mais simples pra usar o Do?
- Mikel Silveira Fraga
- Jedi
- Mensagens: 1173
- Registrado em: Sex Mai 27, 2011 3:27 pm
- Localização: Governador Valadares - MG
- Contato:
Re: Importar algumas colunas do TXT para VBA
Esteves, boa tarde.
No código que você postou, na verdade foi uma gravação de macro, onde foi feita a importação de um arquivo txt, dividindo as informações em colunas, a partir de um delimitador. Por esse motivo, seu código ficou muito confuso e grande, com linhas de comando totalmente desnecessárias.
Da uma olhada nos links abaixo, que mostram de forma prática, como utilizar esse recurso:
- Importando Arquivos Txts via VBA no Excel parte 1-2
- Importando Arquivos Txts via VBA no Excel parte 2-2
No caso do seu código, eu removeria toda a parte destacada em vermelho, substituindo pelo recurso de extração do texto, passado nos links.
Private Function importaArquivo()
Dim arquivo As String
arquivo = abrirArquivo
If arquivo <> Empty Then
Debug.Print "ok"
ActiveWorkbook.Worksheets.Add 'nova planilha
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & arquivo & "", Destination:=Range("A1"))
.Name = "teste"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
arquivo = ""
End Function
O restante do que já foi feito, esta correto e vai funcionar com a adaptação.
Tente fazer a adaptação por ai e, se tiver novas dúvidas, é só chamar.
Abraços e excelente término de dia.
No código que você postou, na verdade foi uma gravação de macro, onde foi feita a importação de um arquivo txt, dividindo as informações em colunas, a partir de um delimitador. Por esse motivo, seu código ficou muito confuso e grande, com linhas de comando totalmente desnecessárias.
Da uma olhada nos links abaixo, que mostram de forma prática, como utilizar esse recurso:
- Importando Arquivos Txts via VBA no Excel parte 1-2
- Importando Arquivos Txts via VBA no Excel parte 2-2
No caso do seu código, eu removeria toda a parte destacada em vermelho, substituindo pelo recurso de extração do texto, passado nos links.
Private Function importaArquivo()
Dim arquivo As String
arquivo = abrirArquivo
If arquivo <> Empty Then
Debug.Print "ok"
ActiveWorkbook.Worksheets.Add 'nova planilha
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & arquivo & "", Destination:=Range("A1"))
.Name = "teste"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
arquivo = ""
End Function
O restante do que já foi feito, esta correto e vai funcionar com a adaptação.
Tente fazer a adaptação por ai e, se tiver novas dúvidas, é só chamar.
Abraços e excelente término de dia.
-
- Acabou de chegar
- Mensagens: 8
- Registrado em: Qua Nov 04, 2020 1:29 pm
Re: Importar algumas colunas do TXT para VBA
Entendido. Obrigado por tudo, vou ler e tentar aplicar.
ABraços
ABraços