Import data from a txt to EXCEL spreadsheet via VBA

Asked

Viewed 2,411 times

0

I would like help to import this file to an excel spreadsheet through VBA

These data below are the ones that are in the txt file for import, to help me, just save them in the notepad 

01|20190521|20190522|0|0|0|0|0|0|0|0|0
02|AALR3|CENTRO DE IMAGE|23|139855|2090832.25|0.10|0.23|0.29|0.25|0.27|0.35
02|ABCB4|BANCO ABC BRASI|169|318440|5754210.80|0.17|0.27|0.35|0.29|0.65|2.00
02|ABEV3|AMBEV S/A|111|6343826|107274097.66|0.04|0.11|0.20|0.09|0.21|1.05
02|ADHM3|HUB COSMETICOS S.A.|2|6|8.16|3.00|3.00|3.00|5.00|5.00|5.00
02|AGRO3|BRASILAGRO CIA|3|207369|3241177.47|0.06|0.15|0.15|0.31|0.33|1.30
02|ALPA4|ALPARGATAS S/A|35|214257|3578091.90|0.08|0.15|0.25|0.15|0.30|0.90
02|ALSC3|ALIANSCE SHOPPI|28|452498|8466237.58|0.05|0.09|0.10|0.13|0.14|0.80
02|ALUP11|ALUPAR INVESTIM|11|86479|1969991.62|0.05|0.12|0.20|0.15|0.22|0.60
02|ALUP3|ALUPAR INVESTIM|1|1|7.87|2.00|2.00|2.00|5.00|5.00|5.00
02|AMAR3|MARISA LOJAS S.|8|42302|291037.76|0.13|0.21|0.48|0.30|0.39|1.60
02|ANIM3|ANIMA HOLDING S|6|11522|225715.98|0.12|0.32|1.11|0.25|0.50|3.00
02|APER3|BR INSURANCE CO|1|5|115.80|0.41|0.41|0.41|1.10|1.10|1.10
02|ARMT34|ARCELOR MITTAL|1|664|22808.40|6.40|6.40|6.40|6.45|6.45|6.45
02|ARZZ3|AREZZO INDUSTRI|12|118700|5711844.00|0.25|0.92|1.10|0.25|1.11|1.60
02|AZUL4|AZUL S.A|21|349446|12335443.80|0.15|0.28|0.50|0.30|0.31|2.00

thank you in advance

vba txt

1 answer

2


Henrique good afternoon!

Hope it helps, run the sub import_file()

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 = 1
            .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

Browser other questions tagged vba txt

You are not signed in. Login or sign up in order to post.