Combining two ideas?

[Zilla]

Thanks to the NG, I'm able to open file(s) into my CURRENT workbook
this way...

If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
mybook.Worksheets(1).Copy after:= _

basebook.Sheets(basebook.Sheets.Count)

On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0

' You can use this if you want to copy only the values
' With ActiveSheet.UsedRange
' .Value = .Value
' End With

mybook.Close savechanges:=False
Next Fnum
End If

Now, recording a macro, I'm able to open a "|" delimited (NOT comma)
text file this way

For Fnum = LBound(MyFiles) To UBound(MyFiles)
Workbooks.OpenText Filename:= _
fName(Fnum), Origin:=437, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=True, OtherChar:="|",
FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Next Fnum

However, how can I effectively do this?

For Fnum = LBound(MyFiles) To UBound(MyFiles)
' SYNTAX IS FOR ILLUSTRATION ONLY - OBVIOUSLY IT"S WRONG
Set mybook = Workbooks.OpenText Filename:= _
fName(Fnum), Origin:=437,
StartRow _
:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False,
Tab:=False, Semicolon:=False, Comma:=False _
, Space:=False, Other:=True,
OtherChar:="|", FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
mybook.Worksheets(1).Copy after:= _

basebook.Sheets(basebook.Sheets.Count)

On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0

' You can use this if you want to copy only the values
' With ActiveSheet.UsedRange
' .Value = .Value
' End With

mybook.Close savechanges:=False
Next Fnum

 

[Dave Peterson]

As soon as you open the text file, you can assign it to myBook. It'll be the
activeworkbook:

If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)

Workbooks.OpenText Filename:=fName(Fnum), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, _
Other:=True, OtherChar:="|", FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

Set mybook = ActiveWorkbook 'the .txt file you just opened

mybook.Worksheets(1).Copy _
after:=basebook.Sheets(basebook.Sheets.Count)

On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0

' You can use this if you want to copy only the values
' With ActiveSheet.UsedRange
' .Value = .Value
' End With

mybook.Close savechanges:=False
Next Fnum
End If

 

[Zilla]

As soon as you open the text file, you can assign it to myBook. It'll be the
activeworkbook:

If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)

Workbooks.OpenText Filename:=fName(Fnum), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, _
Other:=True, OtherChar:="|", FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

Set mybook = ActiveWorkbook 'the .txt file you just opened

mybook.Worksheets(1).Copy _
after:=basebook.Sheets(basebook.Sheets.Count)

On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0

' You can use this if you want to copy only the values
' With ActiveSheet.UsedRange
' .Value = .Value
' End With

mybook.Close savechanges:=False
Next Fnum
End If

You rock man! Thanks!!

 

[Ron de Bruin]

hi Zilla

Have you test this one
http://www.rondebruin.nl/txtcsv.htm