TextToColumns delimiter setting ignored w/in VB loop
 

I've written a simple VB subroutine within Excel in order to concontenate the
contents of multiple text files into one Excel workbook. I have it set up to
open the file and then perform a TextToColumns function on the data with a
specific delimiter defined (and, more importantly, *not* the "space"
delimiter).

The subroutine works fine except when one of the text files happens to be
relatively short. Then, for some reason, Excel ignores the "space:=false"
setting on the short files only and divides up the column by spaces.

Is there some sort of Excel preference I should turn off or a command I can
use? I'm relatively new at creating subroutines so any help would be greatly
appreciated!

Here's a slightly modified version of my subroutine:
'-----------------------------------------------------------------
Private Sub Workbook_Open()

Set fs = Application.FileSearch
Dim Message, Title, Default, MyValue
Message = "Enter the Job Folder Name: "
Title = "Open Files"
Default = "U:\"
MyValue = InputBox(Message, Title, Default)

Workbooks.Add
Workbooks(Workbooks.Count).SaveAs Filename:= _
"V:\Master List.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

With fs
.LookIn = MyValue
.SearchSubFolders = True
.Filename = "*.TXT"
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.OpenText Filename:=.FoundFiles(i), Space:=False

Rows("1:1").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=True,
OtherChar _
:="=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True

' ...deleted remaining formatting for simplicity

Workbooks(Workbooks.Count).Worksheets(1).Select
Workbooks(Workbooks.Count).Worksheets(1).Move _
After:=Workbooks("Master List.xls").Sheets(i)

Next i
Else
MsgBox "There were no files found."
End If
End With

Workbooks(Workbooks.Count).Activate
Workbooks("Master List.xls").Save

End Sub
'--------------------------------------------------------------------------------
--
Thanks!

~Amy
:)