Problem in exporting into excel when Separator in txt files is "|"(vertical bar) instead of ","

[anuradhagrewal]

Hello
Can anybody tell me a VB script wherein I have to export multiple txt files where the separator is instead of "," is "|"(vertical bar).
I have seen multiple codes where the separator is assumed to be a "," but no VB script for "|" which is a separator.

I have uploaded a sample txt file how it looks and there are multiple such files.

Your help is greatly and earnestly appreciated.

Regards

Anu

 

[smozgur]

@anuradhagrewal: Please only mark a solution only if there is an answer to your question, even you posted the solution yourself. Otherwise, the question looks to be answered to the future readers, but no answer actually.

Another note: I can see that you have other questions that you confirmed that you received a working answer for each, but no marked solution. That would be also great if you could mark the solution post in those questions to help future readers. You can get the list of your questions by using the following link easily:

Excel Questions

All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. Do not post questions about other spreadsheet programs (e.g. Google Sheets) in this forum.

www.mrexcel.com

Regarding your question which requires Excel 2007 and VBA (I also suggest you edit your Office version information in your account if you are working in 2007, so we won't suggest technologies which are not existing in the corresponding Excel version.

About the VBA part: I recorded a macro in Excel 2007, and this is the result:
(You need to change the text file path)

Notice the delimiter parameter: TextFileOtherDelimiter = "|"

 With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\smozgur\Desktop\data.txt", Destination:=Range("A1"))
        .Name = "data"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .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)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
End With

Hope this helps.