pratiksuhasaria
New Member
- Joined
- Mar 26, 2019
- Messages
- 24
Hello
I want to import multiple text files in singe workbook but each text file will be on new sheet.
i have following code which does the same but there is an issue with that. the issue is that my delimiter used is "|" but is the text file contains "," then the data after that is missing.
For example
Text File data -> XX,YY,ZZ
Excel data -> XX
Sub CombineTextFiles()
'updateby Extendoffice 20151015
Dim xFilesToOpen As Variant
Dim I As Integer
Dim xWb As Workbook
Dim xTempWb As Workbook
Dim xDelimiter As String
Dim xScreen As Boolean
On Error GoTo ErrHandler
xScreen = Application.ScreenUpdating
Application.ScreenUpdating = False
xDelimiter = "|"
xFilesToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "MF Tool", , True)
If TypeName(xFilesToOpen) = "Boolean" Then
MsgBox "No files were selected", , "MF Tool"
GoTo ExitHandler
End If
I = 1
Set xTempWb = Workbooks.Open(xFilesToOpen(I))
xTempWb.Sheets(1).Copy
Set xWb = Application.ActiveWorkbook
xTempWb.Close False
xWb.Worksheets(I).Columns("A:A").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, xlTextFormat), Array(2, xlTextFormat), Array(3, xlTextFormat), Array(4, xlTextFormat), Array(5, xlTextFormat), Array(6, xlTextFormat), Array(7, xlTextFormat), Array(8, xlTextFormat), Array(9, xlTextFormat), Array(10, xlTextFormat), Array(11, xlTextFormat), _
Array(12, xlTextFormat), Array(13, xlTextFormat), Array(14, xlTextFormat), Array(15, xlTextFormat), Array(16, xlTextFormat), Array(17, xlTextFormat), Array(18, xlTextFormat), Array(19, xlTextFormat), Array(20, xlTextFormat), Array(21, xlTextFormat), Array(22, xlTextFormat), Array(23, xlTextFormat), Array(24, xlTextFormat), Array(25, xlTextFormat), Array(26, xlTextFormat), Array(27, xlTextFormat), Array(28, xlTextFormat), Array(29, xlTextFormat), Array(30, xlTextFormat), Array(31, xlTextFormat), _
Array(32, xlTextFormat), Array(33, xlTextFormat), Array(34, xlTextFormat), Array(35, xlTextFormat), Array(36, xlTextFormat), Array(37, xlTextFormat), Array(38, xlTextFormat), Array(39, xlTextFormat), Array(40, xlTextFormat), Array(41, xlTextFormat), Array(42, xlTextFormat), Array(43, xlTextFormat), Array(44, xlTextFormat), Array(45, xlTextFormat), Array(46, xlTextFormat), Array(47, xlTextFormat), Array(48, xlTextFormat), Array(49, xlTextFormat), Array(50, xlTextFormat), Array(51, xlTextFormat), _
Array(52, xlTextFormat), Array(53, xlTextFormat), Array(54, xlTextFormat), Array(55, xlTextFormat), Array(56, xlTextFormat), Array(57, xlTextFormat), Array(58, xlTextFormat), Array(59, xlTextFormat), Array(60, xlTextFormat), Array(61, xlTextFormat), Array(62, xlTextFormat), Array(63, xlTextFormat), Array(64, xlTextFormat), Array(65, xlTextFormat), Array(66, xlTextFormat), Array(67, xlTextFormat), Array(68, xlTextFormat), Array(69, xlTextFormat), Array(70, xlTextFormat), Array(71, xlTextFormat), _
Array(72, xlTextFormat), Array(73, xlTextFormat), Array(74, xlTextFormat), Array(75, xlTextFormat), Array(76, xlTextFormat), Array(77, xlTextFormat), Array(78, xlTextFormat), Array(79, xlTextFormat), Array(80, xlTextFormat), Array(81, xlTextFormat), Array(82, xlTextFormat), Array(83, xlTextFormat), Array(84, xlTextFormat), Array(85, xlTextFormat), Array(86, xlTextFormat), Array(87, xlTextFormat), Array(88, xlTextFormat), Array(89, xlTextFormat), Array(90, xlTextFormat), Array(91, xlTextFormat), _
Array(92, xlTextFormat), Array(93, xlTextFormat), Array(94, xlTextFormat), Array(95, xlTextFormat), Array(96, xlTextFormat), Array(97, xlTextFormat), Array(98, xlTextFormat), Array(99, xlTextFormat), Array(100, xlTextFormat), Array(101, xlTextFormat), Array(102, xlTextFormat), Array(103, xlTextFormat), Array(104, xlTextFormat), Array(105, xlTextFormat), Array(106, xlTextFormat), Array(107, xlTextFormat), Array(108, xlTextFormat), Array(109, xlTextFormat), Array(110, xlTextFormat), Array(111, xlTextFormat), _
Array(112, xlTextFormat), Array(113, xlTextFormat), Array(114, xlTextFormat), Array(115, xlTextFormat), Array(116, xlTextFormat), Array(117, xlTextFormat), Array(118, xlTextFormat), Array(119, xlTextFormat), Array(120, xlTextFormat), Array(121, xlTextFormat), Array(122, xlTextFormat), Array(123, xlTextFormat), Array(124, xlTextFormat), Array(125, xlTextFormat), Array(126, xlTextFormat), Array(127, xlTextFormat), Array(128, xlTextFormat), Array(129, xlTextFormat), Array(130, xlTextFormat), Array(131, xlTextFormat), _
Array(132, xlTextFormat), Array(133, xlTextFormat), Array(134, xlTextFormat), Array(135, xlTextFormat), Array(136, xlTextFormat), Array(137, xlTextFormat), Array(138, xlTextFormat), Array(139, xlTextFormat), Array(140, xlTextFormat), Array(141, xlTextFormat), Array(142, xlTextFormat), Array(143, xlTextFormat), Array(144, xlTextFormat), Array(145, xlTextFormat), Array(146, xlTextFormat), Array(147, xlTextFormat), Array(148, xlTextFormat), Array(149, xlTextFormat), Array(150, xlTextFormat), Array(151, xlTextFormat), _
Array(152, xlTextFormat), Array(153, xlTextFormat), Array(154, xlTextFormat), Array(155, xlTextFormat), Array(156, xlTextFormat), Array(157, xlTextFormat), Array(158, xlTextFormat), Array(159, xlTextFormat), Array(160, xlTextFormat), Array(161, xlTextFormat), Array(162, xlTextFormat), Array(163, xlTextFormat), Array(164, xlTextFormat), Array(165, xlTextFormat), Array(166, xlTextFormat), Array(167, xlTextFormat), Array(168, xlTextFormat), Array(169, xlTextFormat), Array(170, xlTextFormat), Array(171, xlTextFormat), _
Array(172, xlTextFormat), Array(173, xlTextFormat), Array(174, xlTextFormat), Array(175, xlTextFormat), Array(176, xlTextFormat), Array(177, xlTextFormat), Array(178, xlTextFormat), Array(179, xlTextFormat), Array(180, xlTextFormat), Array(181, xlTextFormat), Array(182, xlTextFormat), Array(183, xlTextFormat), Array(184, xlTextFormat), Array(185, xlTextFormat), Array(186, xlTextFormat), Array(187, xlTextFormat), Array(188, xlTextFormat), Array(189, xlTextFormat), Array(190, xlTextFormat), Array(191, xlTextFormat), _
Array(192, xlTextFormat), Array(193, xlTextFormat), Array(194, xlTextFormat), Array(195, xlTextFormat), Array(196, xlTextFormat), Array(197, xlTextFormat), Array(198, xlTextFormat), Array(199, xlTextFormat), Array(200, xlTextFormat), Array(201, xlTextFormat), Array(202, xlTextFormat), Array(203, xlTextFormat), Array(204, xlTextFormat), Array(205, xlTextFormat), Array(206, xlTextFormat), Array(207, xlTextFormat), Array(208, xlTextFormat), Array(209, xlTextFormat), Array(210, xlTextFormat), Array(211, xlTextFormat), _
Array(212, xlTextFormat), Array(213, xlTextFormat), Array(214, xlTextFormat), Array(215, xlTextFormat), Array(216, xlTextFormat), Array(217, xlTextFormat), Array(218, xlTextFormat), Array(219, xlTextFormat), Array(220, xlTextFormat))
Do While I < UBound(xFilesToOpen)
I = I + 1
Set xTempWb = Workbooks.Open(xFilesToOpen(I))
With xWb
xTempWb.Sheets(1).Move after:=.Sheets(.Sheets.Count)
.Worksheets(I).Columns("A:A").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, xlTextFormat), Array(2, xlTextFormat), Array(3, xlTextFormat), Array(4, xlTextFormat), Array(5, xlTextFormat), Array(6, xlTextFormat), Array(7, xlTextFormat), Array(8, xlTextFormat), Array(9, xlTextFormat), Array(10, xlTextFormat), Array(11, xlTextFormat), _
Array(12, xlTextFormat), Array(13, xlTextFormat), Array(14, xlTextFormat), Array(15, xlTextFormat), Array(16, xlTextFormat), Array(17, xlTextFormat), Array(18, xlTextFormat), Array(19, xlTextFormat), Array(20, xlTextFormat), Array(21, xlTextFormat), Array(22, xlTextFormat), Array(23, xlTextFormat), Array(24, xlTextFormat), Array(25, xlTextFormat), Array(26, xlTextFormat), Array(27, xlTextFormat), Array(28, xlTextFormat), Array(29, xlTextFormat), Array(30, xlTextFormat), Array(31, xlTextFormat), _
Array(32, xlTextFormat), Array(33, xlTextFormat), Array(34, xlTextFormat), Array(35, xlTextFormat), Array(36, xlTextFormat), Array(37, xlTextFormat), Array(38, xlTextFormat), Array(39, xlTextFormat), Array(40, xlTextFormat), Array(41, xlTextFormat), Array(42, xlTextFormat), Array(43, xlTextFormat), Array(44, xlTextFormat), Array(45, xlTextFormat), Array(46, xlTextFormat), Array(47, xlTextFormat), Array(48, xlTextFormat), Array(49, xlTextFormat), Array(50, xlTextFormat), Array(51, xlTextFormat), _
Array(52, xlTextFormat), Array(53, xlTextFormat), Array(54, xlTextFormat), Array(55, xlTextFormat), Array(56, xlTextFormat), Array(57, xlTextFormat), Array(58, xlTextFormat), Array(59, xlTextFormat), Array(60, xlTextFormat), Array(61, xlTextFormat), Array(62, xlTextFormat), Array(63, xlTextFormat), Array(64, xlTextFormat), Array(65, xlTextFormat), Array(66, xlTextFormat), Array(67, xlTextFormat), Array(68, xlTextFormat), Array(69, xlTextFormat), Array(70, xlTextFormat), Array(71, xlTextFormat), _
Array(72, xlTextFormat), Array(73, xlTextFormat), Array(74, xlTextFormat), Array(75, xlTextFormat), Array(76, xlTextFormat), Array(77, xlTextFormat), Array(78, xlTextFormat), Array(79, xlTextFormat), Array(80, xlTextFormat), Array(81, xlTextFormat), Array(82, xlTextFormat), Array(83, xlTextFormat), Array(84, xlTextFormat), Array(85, xlTextFormat), Array(86, xlTextFormat), Array(87, xlTextFormat), Array(88, xlTextFormat), Array(89, xlTextFormat), Array(90, xlTextFormat), Array(91, xlTextFormat), _
Array(92, xlTextFormat), Array(93, xlTextFormat), Array(94, xlTextFormat), Array(95, xlTextFormat), Array(96, xlTextFormat), Array(97, xlTextFormat), Array(98, xlTextFormat), Array(99, xlTextFormat), Array(100, xlTextFormat), Array(101, xlTextFormat), Array(102, xlTextFormat), Array(103, xlTextFormat), Array(104, xlTextFormat), Array(105, xlTextFormat), Array(106, xlTextFormat), Array(107, xlTextFormat), Array(108, xlTextFormat), Array(109, xlTextFormat), Array(110, xlTextFormat), Array(111, xlTextFormat), _
Array(112, xlTextFormat), Array(113, xlTextFormat), Array(114, xlTextFormat), Array(115, xlTextFormat), Array(116, xlTextFormat), Array(117, xlTextFormat), Array(118, xlTextFormat), Array(119, xlTextFormat), Array(120, xlTextFormat), Array(121, xlTextFormat), Array(122, xlTextFormat), Array(123, xlTextFormat), Array(124, xlTextFormat), Array(125, xlTextFormat), Array(126, xlTextFormat), Array(127, xlTextFormat), Array(128, xlTextFormat), Array(129, xlTextFormat), Array(130, xlTextFormat), Array(131, xlTextFormat), _
Array(132, xlTextFormat), Array(133, xlTextFormat), Array(134, xlTextFormat), Array(135, xlTextFormat), Array(136, xlTextFormat), Array(137, xlTextFormat), Array(138, xlTextFormat), Array(139, xlTextFormat), Array(140, xlTextFormat), Array(141, xlTextFormat), Array(142, xlTextFormat), Array(143, xlTextFormat), Array(144, xlTextFormat), Array(145, xlTextFormat), Array(146, xlTextFormat), Array(147, xlTextFormat), Array(148, xlTextFormat), Array(149, xlTextFormat), Array(150, xlTextFormat), Array(151, xlTextFormat), _
Array(152, xlTextFormat), Array(153, xlTextFormat), Array(154, xlTextFormat), Array(155, xlTextFormat), Array(156, xlTextFormat), Array(157, xlTextFormat), Array(158, xlTextFormat), Array(159, xlTextFormat), Array(160, xlTextFormat), Array(161, xlTextFormat), Array(162, xlTextFormat), Array(163, xlTextFormat), Array(164, xlTextFormat), Array(165, xlTextFormat), Array(166, xlTextFormat), Array(167, xlTextFormat), Array(168, xlTextFormat), Array(169, xlTextFormat), Array(170, xlTextFormat), Array(171, xlTextFormat), _
Array(172, xlTextFormat), Array(173, xlTextFormat), Array(174, xlTextFormat), Array(175, xlTextFormat), Array(176, xlTextFormat), Array(177, xlTextFormat), Array(178, xlTextFormat), Array(179, xlTextFormat), Array(180, xlTextFormat), Array(181, xlTextFormat), Array(182, xlTextFormat), Array(183, xlTextFormat), Array(184, xlTextFormat), Array(185, xlTextFormat), Array(186, xlTextFormat), Array(187, xlTextFormat), Array(188, xlTextFormat), Array(189, xlTextFormat), Array(190, xlTextFormat), Array(191, xlTextFormat), _
Array(192, xlTextFormat), Array(193, xlTextFormat), Array(194, xlTextFormat), Array(195, xlTextFormat), Array(196, xlTextFormat), Array(197, xlTextFormat), Array(198, xlTextFormat), Array(199, xlTextFormat), Array(200, xlTextFormat), Array(201, xlTextFormat), Array(202, xlTextFormat), Array(203, xlTextFormat), Array(204, xlTextFormat), Array(205, xlTextFormat), Array(206, xlTextFormat), Array(207, xlTextFormat), Array(208, xlTextFormat), Array(209, xlTextFormat), Array(210, xlTextFormat), Array(211, xlTextFormat), _
Array(212, xlTextFormat), Array(213, xlTextFormat), Array(214, xlTextFormat), Array(215, xlTextFormat), Array(216, xlTextFormat), Array(217, xlTextFormat), Array(218, xlTextFormat), Array(219, xlTextFormat), Array(220, xlTextFormat))
End With
Loop
ExitHandler:
Application.ScreenUpdating = xScreen
Set xWb = Nothing
Set xTempWb = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, , "MF Tool"
Resume ExitHandler
End Sub
I want to import multiple text files in singe workbook but each text file will be on new sheet.
i have following code which does the same but there is an issue with that. the issue is that my delimiter used is "|" but is the text file contains "," then the data after that is missing.
For example
Text File data -> XX,YY,ZZ
Excel data -> XX
Sub CombineTextFiles()
'updateby Extendoffice 20151015
Dim xFilesToOpen As Variant
Dim I As Integer
Dim xWb As Workbook
Dim xTempWb As Workbook
Dim xDelimiter As String
Dim xScreen As Boolean
On Error GoTo ErrHandler
xScreen = Application.ScreenUpdating
Application.ScreenUpdating = False
xDelimiter = "|"
xFilesToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "MF Tool", , True)
If TypeName(xFilesToOpen) = "Boolean" Then
MsgBox "No files were selected", , "MF Tool"
GoTo ExitHandler
End If
I = 1
Set xTempWb = Workbooks.Open(xFilesToOpen(I))
xTempWb.Sheets(1).Copy
Set xWb = Application.ActiveWorkbook
xTempWb.Close False
xWb.Worksheets(I).Columns("A:A").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, xlTextFormat), Array(2, xlTextFormat), Array(3, xlTextFormat), Array(4, xlTextFormat), Array(5, xlTextFormat), Array(6, xlTextFormat), Array(7, xlTextFormat), Array(8, xlTextFormat), Array(9, xlTextFormat), Array(10, xlTextFormat), Array(11, xlTextFormat), _
Array(12, xlTextFormat), Array(13, xlTextFormat), Array(14, xlTextFormat), Array(15, xlTextFormat), Array(16, xlTextFormat), Array(17, xlTextFormat), Array(18, xlTextFormat), Array(19, xlTextFormat), Array(20, xlTextFormat), Array(21, xlTextFormat), Array(22, xlTextFormat), Array(23, xlTextFormat), Array(24, xlTextFormat), Array(25, xlTextFormat), Array(26, xlTextFormat), Array(27, xlTextFormat), Array(28, xlTextFormat), Array(29, xlTextFormat), Array(30, xlTextFormat), Array(31, xlTextFormat), _
Array(32, xlTextFormat), Array(33, xlTextFormat), Array(34, xlTextFormat), Array(35, xlTextFormat), Array(36, xlTextFormat), Array(37, xlTextFormat), Array(38, xlTextFormat), Array(39, xlTextFormat), Array(40, xlTextFormat), Array(41, xlTextFormat), Array(42, xlTextFormat), Array(43, xlTextFormat), Array(44, xlTextFormat), Array(45, xlTextFormat), Array(46, xlTextFormat), Array(47, xlTextFormat), Array(48, xlTextFormat), Array(49, xlTextFormat), Array(50, xlTextFormat), Array(51, xlTextFormat), _
Array(52, xlTextFormat), Array(53, xlTextFormat), Array(54, xlTextFormat), Array(55, xlTextFormat), Array(56, xlTextFormat), Array(57, xlTextFormat), Array(58, xlTextFormat), Array(59, xlTextFormat), Array(60, xlTextFormat), Array(61, xlTextFormat), Array(62, xlTextFormat), Array(63, xlTextFormat), Array(64, xlTextFormat), Array(65, xlTextFormat), Array(66, xlTextFormat), Array(67, xlTextFormat), Array(68, xlTextFormat), Array(69, xlTextFormat), Array(70, xlTextFormat), Array(71, xlTextFormat), _
Array(72, xlTextFormat), Array(73, xlTextFormat), Array(74, xlTextFormat), Array(75, xlTextFormat), Array(76, xlTextFormat), Array(77, xlTextFormat), Array(78, xlTextFormat), Array(79, xlTextFormat), Array(80, xlTextFormat), Array(81, xlTextFormat), Array(82, xlTextFormat), Array(83, xlTextFormat), Array(84, xlTextFormat), Array(85, xlTextFormat), Array(86, xlTextFormat), Array(87, xlTextFormat), Array(88, xlTextFormat), Array(89, xlTextFormat), Array(90, xlTextFormat), Array(91, xlTextFormat), _
Array(92, xlTextFormat), Array(93, xlTextFormat), Array(94, xlTextFormat), Array(95, xlTextFormat), Array(96, xlTextFormat), Array(97, xlTextFormat), Array(98, xlTextFormat), Array(99, xlTextFormat), Array(100, xlTextFormat), Array(101, xlTextFormat), Array(102, xlTextFormat), Array(103, xlTextFormat), Array(104, xlTextFormat), Array(105, xlTextFormat), Array(106, xlTextFormat), Array(107, xlTextFormat), Array(108, xlTextFormat), Array(109, xlTextFormat), Array(110, xlTextFormat), Array(111, xlTextFormat), _
Array(112, xlTextFormat), Array(113, xlTextFormat), Array(114, xlTextFormat), Array(115, xlTextFormat), Array(116, xlTextFormat), Array(117, xlTextFormat), Array(118, xlTextFormat), Array(119, xlTextFormat), Array(120, xlTextFormat), Array(121, xlTextFormat), Array(122, xlTextFormat), Array(123, xlTextFormat), Array(124, xlTextFormat), Array(125, xlTextFormat), Array(126, xlTextFormat), Array(127, xlTextFormat), Array(128, xlTextFormat), Array(129, xlTextFormat), Array(130, xlTextFormat), Array(131, xlTextFormat), _
Array(132, xlTextFormat), Array(133, xlTextFormat), Array(134, xlTextFormat), Array(135, xlTextFormat), Array(136, xlTextFormat), Array(137, xlTextFormat), Array(138, xlTextFormat), Array(139, xlTextFormat), Array(140, xlTextFormat), Array(141, xlTextFormat), Array(142, xlTextFormat), Array(143, xlTextFormat), Array(144, xlTextFormat), Array(145, xlTextFormat), Array(146, xlTextFormat), Array(147, xlTextFormat), Array(148, xlTextFormat), Array(149, xlTextFormat), Array(150, xlTextFormat), Array(151, xlTextFormat), _
Array(152, xlTextFormat), Array(153, xlTextFormat), Array(154, xlTextFormat), Array(155, xlTextFormat), Array(156, xlTextFormat), Array(157, xlTextFormat), Array(158, xlTextFormat), Array(159, xlTextFormat), Array(160, xlTextFormat), Array(161, xlTextFormat), Array(162, xlTextFormat), Array(163, xlTextFormat), Array(164, xlTextFormat), Array(165, xlTextFormat), Array(166, xlTextFormat), Array(167, xlTextFormat), Array(168, xlTextFormat), Array(169, xlTextFormat), Array(170, xlTextFormat), Array(171, xlTextFormat), _
Array(172, xlTextFormat), Array(173, xlTextFormat), Array(174, xlTextFormat), Array(175, xlTextFormat), Array(176, xlTextFormat), Array(177, xlTextFormat), Array(178, xlTextFormat), Array(179, xlTextFormat), Array(180, xlTextFormat), Array(181, xlTextFormat), Array(182, xlTextFormat), Array(183, xlTextFormat), Array(184, xlTextFormat), Array(185, xlTextFormat), Array(186, xlTextFormat), Array(187, xlTextFormat), Array(188, xlTextFormat), Array(189, xlTextFormat), Array(190, xlTextFormat), Array(191, xlTextFormat), _
Array(192, xlTextFormat), Array(193, xlTextFormat), Array(194, xlTextFormat), Array(195, xlTextFormat), Array(196, xlTextFormat), Array(197, xlTextFormat), Array(198, xlTextFormat), Array(199, xlTextFormat), Array(200, xlTextFormat), Array(201, xlTextFormat), Array(202, xlTextFormat), Array(203, xlTextFormat), Array(204, xlTextFormat), Array(205, xlTextFormat), Array(206, xlTextFormat), Array(207, xlTextFormat), Array(208, xlTextFormat), Array(209, xlTextFormat), Array(210, xlTextFormat), Array(211, xlTextFormat), _
Array(212, xlTextFormat), Array(213, xlTextFormat), Array(214, xlTextFormat), Array(215, xlTextFormat), Array(216, xlTextFormat), Array(217, xlTextFormat), Array(218, xlTextFormat), Array(219, xlTextFormat), Array(220, xlTextFormat))
Do While I < UBound(xFilesToOpen)
I = I + 1
Set xTempWb = Workbooks.Open(xFilesToOpen(I))
With xWb
xTempWb.Sheets(1).Move after:=.Sheets(.Sheets.Count)
.Worksheets(I).Columns("A:A").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, xlTextFormat), Array(2, xlTextFormat), Array(3, xlTextFormat), Array(4, xlTextFormat), Array(5, xlTextFormat), Array(6, xlTextFormat), Array(7, xlTextFormat), Array(8, xlTextFormat), Array(9, xlTextFormat), Array(10, xlTextFormat), Array(11, xlTextFormat), _
Array(12, xlTextFormat), Array(13, xlTextFormat), Array(14, xlTextFormat), Array(15, xlTextFormat), Array(16, xlTextFormat), Array(17, xlTextFormat), Array(18, xlTextFormat), Array(19, xlTextFormat), Array(20, xlTextFormat), Array(21, xlTextFormat), Array(22, xlTextFormat), Array(23, xlTextFormat), Array(24, xlTextFormat), Array(25, xlTextFormat), Array(26, xlTextFormat), Array(27, xlTextFormat), Array(28, xlTextFormat), Array(29, xlTextFormat), Array(30, xlTextFormat), Array(31, xlTextFormat), _
Array(32, xlTextFormat), Array(33, xlTextFormat), Array(34, xlTextFormat), Array(35, xlTextFormat), Array(36, xlTextFormat), Array(37, xlTextFormat), Array(38, xlTextFormat), Array(39, xlTextFormat), Array(40, xlTextFormat), Array(41, xlTextFormat), Array(42, xlTextFormat), Array(43, xlTextFormat), Array(44, xlTextFormat), Array(45, xlTextFormat), Array(46, xlTextFormat), Array(47, xlTextFormat), Array(48, xlTextFormat), Array(49, xlTextFormat), Array(50, xlTextFormat), Array(51, xlTextFormat), _
Array(52, xlTextFormat), Array(53, xlTextFormat), Array(54, xlTextFormat), Array(55, xlTextFormat), Array(56, xlTextFormat), Array(57, xlTextFormat), Array(58, xlTextFormat), Array(59, xlTextFormat), Array(60, xlTextFormat), Array(61, xlTextFormat), Array(62, xlTextFormat), Array(63, xlTextFormat), Array(64, xlTextFormat), Array(65, xlTextFormat), Array(66, xlTextFormat), Array(67, xlTextFormat), Array(68, xlTextFormat), Array(69, xlTextFormat), Array(70, xlTextFormat), Array(71, xlTextFormat), _
Array(72, xlTextFormat), Array(73, xlTextFormat), Array(74, xlTextFormat), Array(75, xlTextFormat), Array(76, xlTextFormat), Array(77, xlTextFormat), Array(78, xlTextFormat), Array(79, xlTextFormat), Array(80, xlTextFormat), Array(81, xlTextFormat), Array(82, xlTextFormat), Array(83, xlTextFormat), Array(84, xlTextFormat), Array(85, xlTextFormat), Array(86, xlTextFormat), Array(87, xlTextFormat), Array(88, xlTextFormat), Array(89, xlTextFormat), Array(90, xlTextFormat), Array(91, xlTextFormat), _
Array(92, xlTextFormat), Array(93, xlTextFormat), Array(94, xlTextFormat), Array(95, xlTextFormat), Array(96, xlTextFormat), Array(97, xlTextFormat), Array(98, xlTextFormat), Array(99, xlTextFormat), Array(100, xlTextFormat), Array(101, xlTextFormat), Array(102, xlTextFormat), Array(103, xlTextFormat), Array(104, xlTextFormat), Array(105, xlTextFormat), Array(106, xlTextFormat), Array(107, xlTextFormat), Array(108, xlTextFormat), Array(109, xlTextFormat), Array(110, xlTextFormat), Array(111, xlTextFormat), _
Array(112, xlTextFormat), Array(113, xlTextFormat), Array(114, xlTextFormat), Array(115, xlTextFormat), Array(116, xlTextFormat), Array(117, xlTextFormat), Array(118, xlTextFormat), Array(119, xlTextFormat), Array(120, xlTextFormat), Array(121, xlTextFormat), Array(122, xlTextFormat), Array(123, xlTextFormat), Array(124, xlTextFormat), Array(125, xlTextFormat), Array(126, xlTextFormat), Array(127, xlTextFormat), Array(128, xlTextFormat), Array(129, xlTextFormat), Array(130, xlTextFormat), Array(131, xlTextFormat), _
Array(132, xlTextFormat), Array(133, xlTextFormat), Array(134, xlTextFormat), Array(135, xlTextFormat), Array(136, xlTextFormat), Array(137, xlTextFormat), Array(138, xlTextFormat), Array(139, xlTextFormat), Array(140, xlTextFormat), Array(141, xlTextFormat), Array(142, xlTextFormat), Array(143, xlTextFormat), Array(144, xlTextFormat), Array(145, xlTextFormat), Array(146, xlTextFormat), Array(147, xlTextFormat), Array(148, xlTextFormat), Array(149, xlTextFormat), Array(150, xlTextFormat), Array(151, xlTextFormat), _
Array(152, xlTextFormat), Array(153, xlTextFormat), Array(154, xlTextFormat), Array(155, xlTextFormat), Array(156, xlTextFormat), Array(157, xlTextFormat), Array(158, xlTextFormat), Array(159, xlTextFormat), Array(160, xlTextFormat), Array(161, xlTextFormat), Array(162, xlTextFormat), Array(163, xlTextFormat), Array(164, xlTextFormat), Array(165, xlTextFormat), Array(166, xlTextFormat), Array(167, xlTextFormat), Array(168, xlTextFormat), Array(169, xlTextFormat), Array(170, xlTextFormat), Array(171, xlTextFormat), _
Array(172, xlTextFormat), Array(173, xlTextFormat), Array(174, xlTextFormat), Array(175, xlTextFormat), Array(176, xlTextFormat), Array(177, xlTextFormat), Array(178, xlTextFormat), Array(179, xlTextFormat), Array(180, xlTextFormat), Array(181, xlTextFormat), Array(182, xlTextFormat), Array(183, xlTextFormat), Array(184, xlTextFormat), Array(185, xlTextFormat), Array(186, xlTextFormat), Array(187, xlTextFormat), Array(188, xlTextFormat), Array(189, xlTextFormat), Array(190, xlTextFormat), Array(191, xlTextFormat), _
Array(192, xlTextFormat), Array(193, xlTextFormat), Array(194, xlTextFormat), Array(195, xlTextFormat), Array(196, xlTextFormat), Array(197, xlTextFormat), Array(198, xlTextFormat), Array(199, xlTextFormat), Array(200, xlTextFormat), Array(201, xlTextFormat), Array(202, xlTextFormat), Array(203, xlTextFormat), Array(204, xlTextFormat), Array(205, xlTextFormat), Array(206, xlTextFormat), Array(207, xlTextFormat), Array(208, xlTextFormat), Array(209, xlTextFormat), Array(210, xlTextFormat), Array(211, xlTextFormat), _
Array(212, xlTextFormat), Array(213, xlTextFormat), Array(214, xlTextFormat), Array(215, xlTextFormat), Array(216, xlTextFormat), Array(217, xlTextFormat), Array(218, xlTextFormat), Array(219, xlTextFormat), Array(220, xlTextFormat))
End With
Loop
ExitHandler:
Application.ScreenUpdating = xScreen
Set xWb = Nothing
Set xTempWb = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, , "MF Tool"
Resume ExitHandler
End Sub
