On Jan 15, 7:41 pm, Dave Peterson <peter...@verizonXSPAM.net> 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!!
Similar Threads
