text to columns - Comma bug

[pratiksuhasaria]

I am using the text to columns in VBA to automate my process but there seems to be a bug. As I run the macro to import text file with the delimiter set to TAB. I set the delimiter to TAB only and also made consecutive delimiter to FALSE.
As the macro gets executed I get the data in excel but except the row that contains comma get disappear.

Example source:
Ramesh<TAB>000185<TAB>New Delhi<TAB>Team Lead
Rajesh<TAB>000125<TAB>New York , USA<TAB>Software Engineer



Example result:

Ramesh	000185	New Delhi	Team Lead
Rajesh	000125	New York

I now need to remove all the commas in a text file before running the macro which is time consuming.

Any idea how to fix this ?

Sub CombineTextFiles()
    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:=True, Semicolon:=False, _
      Comma:=False, Space:=False, _
      Other:=False, 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:=True, Semicolon:=False, _
              Comma:=False, Space:=False, _
      Other:=False, 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

Thanks!

 

[Joe4]

I now need to remove all the commas in a text file before running the macro which is time consuming.

That shouldn't be. Simply do a Find/Replace, finding all commas and replacing them with nothing. That should take about 10 seconds.
If you turn on the Macro Recorder and record yourself doing that manually, you will have the VBA code that does that.

 

[pratiksuhasaria]

That shouldn't be. Simply do a Find/Replace, finding all commas and replacing them with nothing. That should take about 10 seconds.
If you turn on the Macro Recorder and record yourself doing that manually, you will have the VBA code that does that.

Thanks for your response, but I around 100 of text file and opening and changing every file is not feasible. So if there is something in the code which could be change will be helpful.

 

[Joe4]

This can all be automated.
You still use the block of code for replacing them, like I described above.
Then just put that in the middle of loop that loops through all the files you want to apply this to.
There are lots of threads that you can find with a Google search which will show you how to loop through all the files in a directory.
If you have any problems implementing it, post your attempt, along with an explanation of the the criteria of whoch files to apply this to (and their location).