Extract data from pipe "|" delimited text file to new excel workbook - worksheet.

Question

I have used the the below code for converting the data from text delimited file to excel workbook, but it stores it on the active workbook , instead of new workbook sheet.

I have 3 text files and i need the data of all these three text files into one new workbook, in three different sheets.

Code snippet below of the macro developed so far-

Dim wkbTemp As Workbook
Dim sPath As String, sName As String
sPath = strFileToOpen

sName = "Test.txt"

Workbooks.OpenText Filename:=sPath, _
    Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
    , Comma:=False, Space:=False, Other:=True, OtherChar:="|"
 Set wkbTemp = xlapp.xlwkbInput
Application.ScreenUpdating = False

Set wkbTemp = xlapp.xlextwkbInput - i need to get this data to new excel workbook - new sheet. for eg - sheet1 , so on and so forth for other two t files, in two other sheets.

Answer 1

You have 2 ways. First you can create a new workbook and reference it in the Opentext code:

Set NewBook = Workbooks.Add
    With NewBook
    ..openText
    End With

Onother way, a little bit ugly and slow is to copy the sheet from thisworkbook to NewBook. Why this? Because you can improve your understanding of differences between ThisWorkbook and ActiveWorkbook.

Answer 2

You should create a new Workbook, and with each Worksheet, Add a QueryTable in the QueryTables collection of this Worksheet with

"TEXT;Test.txt"

as first parameter of Add for your file type and name, Range("A1") as a trivial destination range.

Have a look at the many examples on the Internet how to deal with QueryTable Properties. In your case TextFileOtherDelimiter is where your pipe | should be defined.

Then call refresh(false) and delete on this QueryTable.