PDA

View Full Version : [SOLVED] Import Files With Too Many Rows



jphermans
02-08-2005, 06:06 AM
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
02-08-2005, 06:19 AM
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
02-08-2005, 06:33 AM
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
02-08-2005, 06:57 AM
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
02-08-2005, 07:27 AM
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
02-08-2005, 07:47 AM
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.:hi:

mvidas
02-08-2005, 08:02 AM
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!