Macro Isssue

[pratiksuhasaria]

I have a text file that contains 5 million records. I want to import the file to excel. but the row limit for excel is around 1 million. Thus i need to split the record into different sheet so that i could all the records in one excel .
I have following code which display the data for around 1048576 lines of record but the remaining are lost.
Please help me with these.

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:="|", _
      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))
    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:="|", _
                   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))
    x = x + 1
              
        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

 

[mole999]

have a read here > https://blogs.technet.microsoft.com...les-with-more-than-a-million-rows-into-excel/