VBA. I try to convert a txt to excel

Question

Hello guys I have this macro to convert several txt for excel, but when it converts 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

Answer 1

So I analyzed the command ActiveWorkbook.SaveAs does not contain or does not provide for the separation of text by semicolon.

Since you need to have these texts placed in the spreadsheet with the ";" tab, the part of the code presented below solves.

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 that has in your code, adapt to pass the data of path and name, because here they are fixed. That’s it!