Separate data at import?

[NCay]

I am about to import a large text file (9MB) into excel and have encountered a problem. The file is too big to load in, > 65000 lines available. As I would be wanting to separate the data across a number of sheets, would there be a way to "post" each line to a specific sheet based on the first few characters of each line of data. And thus allow the whole sheet to load?
Or is there something else that could assist me with this challenge?

As always I'm ever grateful for any suggestions.

 

[XL-Dennis]

NCay,

Here´s a procedure that will solve it in a smooth way:

<PRE>
<FONT color=blue>Sub </FONT>LäsaInStoraTextFiler()

<FONT color=blue>Dim </FONT>stResultat<FONT color=blue> As</FONT><FONT color=blue> String</FONT>, stFilNamn<FONT color=blue> As</FONT><FONT color=blue> String</FONT>

<FONT color=blue>Dim </FONT>lnFilNamn<FONT color=blue> As</FONT><FONT color=blue> Long</FONT>

<FONT color=blue>Dim </FONT>i<FONT color=blue> As</FONT><FONT color=blue> Double</FONT>



stFilNamn = Application.GetOpenFilename



<FONT color=blue>If </FONT>stFilNamn = ""<FONT color=blue> Then </FONT>End



lnFilNamn = FreeFile()



<FONT color=blue>Open </FONT>stFilNamn <FONT color=blue>For </FONT>Input<FONT color=blue> As</FONT> #lnFilNamn

Application.ScreenUpdating =<FONT color=blue> False</FONT>



<FONT color=blue> Workbook</FONT>s.Add Template:=xlWorksheet



i = 1



<FONT color=blue>Do </FONT>While Seek(lnFilNamn) <= LOF(lnFilNamn)

Application.StatusBar = "Import row " & _

i & " from file " & stFilNamn



Line Input #lnFilNamn, stResultat



<FONT color=blue>If </FONT>Left(stResultat, 1) = "=" Then

ActiveCell.Value = "'" & stResultat

<FONT color=blue>Else</FONT>

ActiveCell.Value = stResultat

<FONT color=blue>End If</FONT>



<FONT color=blue>If </FONT>ActiveCell.Row = 65536 Then

ActiveWorkbook.Sheets.Add

<FONT color=blue>Else</FONT>

ActiveCell.Offset(1, 0).Select

<FONT color=blue>End If</FONT>



i = i + 1

<FONT color=blue>Loop</FONT>



Close



Application.StatusBar =<FONT color=blue> False</FONT>



<FONT color=blue>End Sub</FONT>
</PRE>

Kind regards,
Dennis

 

[NCay]

A thousand thankyous, I'll go and experiment with it just now.
Can I come back to you if I have any queries/ problems though? Expect a few silly questions from me later...

 

[XL-Dennis]

On 2002-09-05 17:56, NCay wrote:
A thousand thankyous, I'll go and experiment with it just now.
Can I come back to you if I have any queries/ problems though? Expect a few silly questions from me later...

You welcome and if I´m not around there are excellent regular contributors that also can help You

Kind regards,
Dennis

 

[NCay]

Ok #1 question, I've just run the sub and it crashed with "run time error 62, input past end of file". The data it has "worked through" is all on one sheet. Is the purpose of this to separate different lines to different sheets, and if so do I have to specify what and where?
Maybe further explanation is required for me.
Any advice?

PS it stopped about half way through
This message was edited by NCay on 2002-09-05 18:16