J
JakeShipley2008
I have this macro which I will attach. IT currently has two issues.
1) When it brings the files into my active workbook it doesnt recognize to
delimit the files. It dumps the data into rows but does not seperate by
column.
2) It dumps one of the files into a new worksheet - when it does this that
one file is exported correctly.
What I need to accomplish is getting the files to dump correctly into my
active workbook.
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 (True)
wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Tab:=True, Semicolon:=True, _
Comma:=True, Space:=True, _
Other:=True, OtherChar:=","
x = 1
While x <= UBound(FilesToOpen)
Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
With wkbAll
wkbTemp.Sheets(1).Move After:=ThisWorkbook.Sheets(Sheets.count)
.Worksheets(1).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Tab:=True, Semicolon:=True, _
Comma:=Ture, Space:=True, _
Other:=True, OtherChar:=","
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
1) When it brings the files into my active workbook it doesnt recognize to
delimit the files. It dumps the data into rows but does not seperate by
column.
2) It dumps one of the files into a new worksheet - when it does this that
one file is exported correctly.
What I need to accomplish is getting the files to dump correctly into my
active workbook.
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 (True)
wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Tab:=True, Semicolon:=True, _
Comma:=True, Space:=True, _
Other:=True, OtherChar:=","
x = 1
While x <= UBound(FilesToOpen)
Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
With wkbAll
wkbTemp.Sheets(1).Move After:=ThisWorkbook.Sheets(Sheets.count)
.Worksheets(1).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Tab:=True, Semicolon:=True, _
Comma:=Ture, Space:=True, _
Other:=True, OtherChar:=","
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