ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Combining two ideas? (https://www.excelbanter.com/excel-programming/404330-combining-two-ideas.html)

Zilla[_4_]
Combining two ideas?
 
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
Combining two ideas?
 
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 wrote:

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

Zilla[_4_]
Combining two ideas?
 
On Jan 15, 7:41 pm, Dave Peterson wrote:
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 wrote:

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

You rock man! Thanks!!

Ron de Bruin
Combining two ideas?
 
hi Zilla

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Zilla" wrote in message ...
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


All times are GMT +1. The time now is 05:17 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com