Hello. I have the following code (thanks to Ron de Bruin) and am trying to
adapt it to my needs. What I have to do is to have the information go across
the spreadsheet columns rather then straight down the worksheet. There are 4
columns of info, skip a column, then 4 more, and so on. The code has it all
in one column down the worksheet. I seek the wisdom of the newsgroup to see
where and what needs to be added, replaced, etc as I am at a loss. I'm
guessing it is in the Workbooks.OpenText The code is lengthy.
Thanks in advance for any assistance providede.
.... John

Sub Merge_TXT_Files()
Dim BatFileName As String
Dim TXTFileName As String
Dim XLSFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim DefPath As String
Dim Wb As Workbook
Dim oApp As Object
Dim oFolder
Dim foldername

'Create two temporary file names
BatFileName = Environ("Temp") & "\CollectTXTData" & Format(Now,
"dd-mm-yy-h-mm-ss") & ".bat"
TXTFileName = Environ("Temp") & "\AllTTXT" & Format(Now,
"dd-mm-yy-h-mm-ss") & ".txt"

'Folder where you want to save the Excel file
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) < "\" Then
DefPath = DefPath & "\"
End If

'Set the extension and file format
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else

'You use Excel 2007
'FileExtStr = ".xlsx": FileFormatNum = 51

'If you want to save as xls(97-2003 format) in 2007 use
FileExtStr = ".xls": FileFormatNum = 56
End If

'Name of the Excel file with a date/time stamp
XLSFileName = DefPath & "MasterTXT " & Format(Now, "dd-mmm-yyyy
h-mm-ss") & FileExtStr

'Browse to the folder with TXT files
Set oApp = CreateObject("Shell.Application")
Set oFolder = oApp.BrowseForFolder(0, "Select folder with TXT files", 512)
If Not oFolder Is Nothing Then
foldername = oFolder.Self.Path
If Right(foldername, 1) < "\" Then
foldername = foldername & "\"
End If

'Create the bat file
Open BatFileName For Output As #1
Print #1, "Copy " & Chr(34) & foldername & "*.txt" _
& Chr(34) & " " & TXTFileName
Close #1

'Run the Bat file to collect all data from the TXT files into a TXT file
ShellAndWait BatFileName, 0
If Dir(TXTFileName) = "" Then
MsgBox "There are no txt files in this folder"
Kill BatFileName
Exit Sub
End If

'Open the TXT file in Excel
Application.ScreenUpdating = False
Workbooks.OpenText _
Filename:=TXTFileName, _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, 2), Array(3, 4))


'Save text file as a Excel file
Set Wb = ActiveWorkbook

Application.DisplayAlerts = False
Wb.SaveAs Filename:=XLSFileName, FileFormat:=FileFormatNum
Application.DisplayAlerts = True

Wb.Close savechanges:=False
MsgBox "You find the Excel file he " & vbNewLine & XLSFileName

'Delete the bat and text file you used temporarily
Kill BatFileName
Kill TXTFileName

Application.ScreenUpdating = True
End If

End Sub