I fixed the code so it doesn't open the text file twice which was
giving the error. If the code is still dropping the leading zeroes then
check if the text file when open in excel is also missing the zero, or
the problem is in copying the sheet between the 2 workbooks.


Sub CombineTextFiles()
Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim sDelimiter As String

On Error GoTo ErrHandler
Application.ScreenUpdating = False

sDelimiter = "|"

FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt), *.txt", _
MultiSelect:=True, Title:="Text Files to Open")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

x = 1

While x <= UBound(FilesToOpen)
With wkbAll
Set Newsht = .Sheets.Add(after:=.Sheets(Sheets.Count))
Newsht.Cells.NumberFormat = "@"

Workbooks.OpenText Filename:=FilesToOpen(x), _
DataType:=xlDelimited, _
Other:=True, OtherChar:=sDelimiter

Set wkbTemp = ActiveWorkbook
wkbTemp.Sheets(1).Cells.Copy
Newsht.Cells.PasteSpecial Paste:=xlPasteValues
wkbTemp.close savechanges:=false

End With
Wend

ExitHandler:
Application.ScreenUpdating = True
Set wkbAll = Nothing
Set wkbTemp = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=181739

Microsoft Office Help