A
Amy
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
'--------------------------------------------------------------------------------
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
'--------------------------------------------------------------------------------