VBA Macro to Import the Data from Text file to Excel

VBA Macro to Import the Data from Text(.txt) file to Excel(.xls) and Save in Destination Folders
Sub Import_Txt_Data()

Dim Tgt_WB As Workbook
Dim SrcSht As Worksheet
Dim TgtSht As Worksheet

Application.DisplayAlerts = False

Set SrcSht = ThisWorkbook.Sheets("CPanel")

 Set Tgt_WB = Workbooks.Add(1)
 Set TgtSht = Tgt_WB.Sheets(1)

 TgtSht.Activate
 TgtSht.Range("A1").Select

 'ConStr = "TEXT;C:\Users\Tamatam\Desktop\Ad_hoc\Txt_Data.txt"
 ConStr = "TEXT;" & SrcSht.Range("C3").Value

    With TgtSht.QueryTables.Add(Connection:=ConStr _
        , Destination:=Range("$A$1"))
        .Name = "Data_Import"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
     '  .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) 
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

    TgtSht.Range("A1").Select
   'Saving the Imported data as Excel[97-2003] format in Target Foders.
        For S = 3 To 6
            SavePath = SrcSht.Range("D" & S).Value & "\"
            Tgt_WB.SaveAs SavePath & "Txt_Data", 56
        Next S
            Tgt_WB.Close , True
            
 MsgBox "Data Imported from Source .txt File to XL[97-2003] and Saved in Destination  Folders", vbOKOnly, "Import Success"

    Application.DisplayAlerts = True
    Set SrcSht = Nothing

    Set Tgt_WB = Nothing
    Set TgtSht = Nothing
End Sub
---------------------------------------------------------------------------------------------------------------
Notes:
The main file formats in Excel 2007-2016:

51 = xlOpenXMLWorkbook (without Macros in 2007-2013, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without Macros in 2007-2013, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without Macros, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)

We can use the File Format Numbers instead of the defined File Format Constants in the code so that it will compile OK when we copy the code into an Excel 97-2003 workbook. 

For example, Excel 97-2003 won't know what the File Format constant xlOpenXMLWorkbookMacroEnabled , but it understands File Format number 52.

In Excel for the Mac the Format Number values are +1 as follows :
xlsx = 52
xlsm = 53
xlsb = 51
xls = 57

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------