Hi Phil

Thanks for your thoughts. We are not sure since the OP has not come back. A
better way would be to use the .OpenText method as below...

Workbooks.OpenText Filename:="D:\PhoneNo\2.txt", StartRow:=1 , _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Comma:=True


--
Jacob


"Phil Hibbs" wrote:

I would probably go with a solution that involves using the Excel
import code to read each file into a blank tab, and then copy that
data from the tab into the combined tab. Here's an example of a macro
that imports data into a new tab:

ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = "Tempsheet"

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
+ FileName, Destination:=Range("A1"))
.Name = "CSVfile"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2)
.Refresh BackgroundQuery:=False
End With

The big 2, 2, 2, 2, 2 line defines all columns (up to 100) as Text.
Next you would need to copy-paste the contents of this tab into the
real tab and fill in the file name, then delete the tab and import the
next file. You could turn off screen updates while this is happening.
Or hide the temporary tab.

Phil Hibbs.
.