Import Files With Too Many Rows

[jphermans]

I've large txt (10mb)file that i must inport in excel.
But have more lines in the file than rows in the sheet.
Is it possible via vba to fill the first sheet and then continue on the second and so on ,

:rofl

[mvidas]

Hi jphermans,
Is it fixed width, or a delimited file? If delimited, what is the delimiter? If fixed width, what are the field positions?
Matt

[mvidas]

Well, I'm going to assume it is a delimited file. If it turns out to be otherwise, we can make the modifications as needed. You can do this with a macro, just set the filename and delimiter, and run! If you'd like we can have the filename chosen at runtime, I left it as hardcoded for now. Try the following:

Sub ImportBigDelimitedTextFile()
 Dim openFile As String, vFileNum As Integer, eLine As String
 Dim vLine, vCount As Long, vDelim As String
openFile = "C:\mac.txt" 'Filename to import
 vDelim = ","                 'Delimiter
vCount = 0
 Workbooks.Add (-4167)
 vFileNum = FreeFile()
 Open openFile For Input As #vFileNum
 Do While Not EOF(vFileNum)
  Line Input #vFileNum, eLine
  vCount = vCount + 1
  vLine = Split(eLine, ",", -1, 1)
  Range(Cells(vCount, 1), Cells(vCount, UBound(vLine) + 1)) = vLine
  If vCount = 65000 And Not EOF(vFileNum) Then
   Worksheets.Add After:=Sheets(Sheets.Count)
   vCount = 0
  End If
 Loop
 Close #vFileNum
End Sub

Matt

[jphermans]

mvidas,

It's working fine so, going to change it for choosing the txt file . Is it possible when i have an second txt to append it to the last sheet of the first file and continue then ?

[mvidas]

OK, To choose the file at runtime, change the openFile = line to:

' openFile = "C:\filename.txt" 'Filename to import
 openFile = Application.GetOpenFilename("Text Files (*.txt), *.txt, All Files (*.*), *.*")
 If UCase(openFile) = "FALSE" Then Exit Sub

As far as appending it after the previous file, that would be possible. Would you be able to append them together beforehand, then just import the one file? Seems to me like this would be the better idea for you. You can do this very easily thanks to a KBase entry from mdewis ( http://www.vbaexpress.com/kb/getarticle.php?kb_id=273 ).

But if you do want to append them after the previous one, change the vCount=0 and Workbooks.Add lines to:

' If Msgbox("Append to previous file?", vbYesNo, "Append?") = vbNo Then
 If ActiveWorkbook Is Nothing Then
  vCount = 0
  Workbooks.Add (-4167)
 Else
  vCount = Range("A65536").End(xlUp).Row
 End If

BUT this checks to see if a workbook is active to determine whether to append or not. So if you have an unrelated workbook open, and you want to freshly import the txt file (and not append), it will add it to the unrelated workbook. The only other way around this would be to ask the user at runtime whether to append or add new. I added that option (the commented Msgbox line), but I usually try and avoid msgboxes when possible. If you don't mind being asked every time, comment out the If ActiveWorkbook line and uncomment the If Msgbox line.

I hope this is not too confusing to you, if you need any clarification please let me know!
Matt

[jphermans]

OK, To choose the file at runtime, change the openFile = line to:

' openFile = "C:\filename.txt" 'Filename to import
 openFile = Application.GetOpenFilename("Text Files (*.txt), *.txt, All Files (*.*), *.*")
 If UCase(openFile) = "FALSE" Then Exit Sub

As far as appending it after the previous file, that would be possible. Would you be able to append them together beforehand, then just import the one file? Seems to me like this would be the better idea for you. You can do this very easily thanks to a KBase entry from mdewis ( http://www.vbaexpress.com/kb/getarticle.php?kb_id=273 ).

But if you do want to append them after the previous one, change the vCount=0 and Workbooks.Add lines to:

' If Msgbox("Append to previous file?", vbYesNo, "Append?") = vbNo Then
 If ActiveWorkbook Is Nothing Then
  vCount = 0
  Workbooks.Add (-4167)
 Else
  vCount = Range("A65536").End(xlUp).Row
 End If

BUT this checks to see if a workbook is active to determine whether to append or not. So if you have an unrelated workbook open, and you want to freshly import the txt file (and not append), it will add it to the unrelated workbook. The only other way around this would be to ask the user at runtime whether to append or add new. I added that option (the commented Msgbox line), but I usually try and avoid msgboxes when possible. If you don't mind being asked every time, comment out the If ActiveWorkbook line and uncomment the If Msgbox line.

I hope this is not too confusing to you, if you need any clarification please let me know!
Matt

Hello Matt,

It al clear and already fixed.
Thank you for the great and very quick help.

[mvidas]

Glad to hear it, and glad to help!
Just so you know, you can mark this solved by going to Thread Tools at the top of the page, then Mark Solved. Helps clean up a bit and saves other users time by knowing your problem is all set.
Welcome to vbax!