VBA. I try to convert a txt to excel

Question

VBA. I try to convert a txt to excel

Navigation

#1 by (1 votes)

2

I have this macro to convert several txt to excel , but when converting it does not separate the file by column ";"

Sub Convert_Csv()

    Dim File_Names As Variant
    Dim File_count As Integer
    Dim Active_File_Name As String
    Dim Counter As Integer
    Dim File_Save_Name As Variant

    File_Names = Application.GetOpenFilename(, , , , True)
    File_count = UBound(File_Names)
    Counter = 1
    Do Until Counter > File_count
        Active_File_Name = File_Names(Counter)
        Workbooks.Open Filename:=Active_File_Name
        Active_File_Name = ActiveWorkbook.Name
        File_Save_Name = InStr(1, Active_File_Name, ".txt", 1) - 1
        File_Save_Name = Mid(Active_File_Name, 1, File_Save_Name) & ".xlsx"
        ActiveWorkbook.SaveAs Filename:=File_Save_Name,   FileFormat:=xlOpenXMLWorkbooklocal:=true
        ActiveWindow.Close
        Counter = Counter + 1
    Loop

End Sub

excel vba

asked by anonymous 15.07.2016 / 23:08

1 answer

pass variables to view EJS from different queries How to solve this error with the Drawer component of Materialui

score 1 · Accepted Answer

So I've looked at the ActiveWorkbook.SaveAs command that does not include or does not predict text separation by a semicolon.

As you need to have these texts placed in the worksheet with the ";" section, the part of the code below resolves.

With ActiveSheet.QueryTables.Add(Connection:= _
    "C:\Texto.txt", Destination:=Range("$A$1"))
    .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 = True
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

After reading the text file you have in your code, adapt to pass the path and name data, because here they are fixed. That's it!