krishhi
Active Member
- Joined
- Sep 8, 2008
- Messages
- 328
Hi there,
I need some help on a Macro. I have more than 160 Text files, I need to copy the text files into seperate sheet of excel. You all know it is a time consuming.
Today I google for the macro, luckly i found this one:
It works very well but the problem is that, In that text file, I have some numbers like 005654, 009845 etc.. When The macro copies the data from text file to excel, the numbers changed to 5654, 9845.
Is there any way to overcome this problem. I want to place all the exact data from text to excel without chaning its format.
Thanks in advance,
I need some help on a Macro. I have more than 160 Text files, I need to copy the text files into seperate sheet of excel. You all know it is a time consuming.
Today I google for the macro, luckly i found this one:
Code:
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
Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
wkbTemp.Sheets(1).Copy
Set wkbAll = ActiveWorkbook
wkbTemp.Close (False)
wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:="|"
x = x + 1
While x <= UBound(FilesToOpen)
Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
With wkbAll
wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:=sDelimiter
End With
x = x + 1
Wend
ExitHandler:
Application.ScreenUpdating = True
Set wkbAll = Nothing
Set wkbTemp = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
It works very well but the problem is that, In that text file, I have some numbers like 005654, 009845 etc.. When The macro copies the data from text file to excel, the numbers changed to 5654, 9845.
Is there any way to overcome this problem. I want to place all the exact data from text to excel without chaning its format.
Thanks in advance,