Need an excel macro to extract data from a large Txt. file (Beyond 1.04 excel lines) & paste the remaining data to another sheet after 1.04Mn lines

Good Day,

I am new to this forum.

I have a problem while extracting data from a large .”txt” file to an excel (64 bit version). When the data exceeds 1.04 Mn lines then it just stops there and i have no clue how to extract the remaining data. 

So i need my below VBA code to be modified to extract circa 1 Mn lines of data on one sheet called “data popl”and remaining data to another sheet called “data popl2”. 

I am currently using the below code.

Thanks for your help


Sub Data Pull()

strDate = Format(Sheet1.Cells(2, 3), “yyyymmdd”)

Dim wbOpen As Workbook
Dim wbPath As String
Dim fName As String
Dim mfName As String

wbPath = “C:\Documents\New folder”
fName = “Population” & strDate & “.txt”
mfName = “Data Population_Lead.xlsm”

If Len(fName) > 0 Then Set wbOpen = Workbooks.Open(wbPath & fName)

Selection.TextToColumns Destination:=Range(“A1”), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)), _
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.WindowState = xlMinimized

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.WindowState = xlMaximized
Application.CutCopyMode = False
ActiveWorkbook.Close False

End Sub

Participant Asked 3 hours ago in VBA: Macros.
Add Comment
0 Answer(s)
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.