Import multiple text files in singe workbook

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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Forum statistics

Threads
1,148,108
Messages
5,744,876
Members
423,907
Latest member
zerocool88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top