Thursday, September 1, 2016

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog