VBA to read text file fast and efficient

[DaveNUFC]

Hi,

I have the below vba code which reads in a tab delimited text file fine. The issue however is the length of time it takes as the file is in excess of 500mb. Basically in this example I only want to bring back the records where the first column is in range B5. Is there a slick way this can run much faster?

Please Login or Register  to view this content.

[mikeTRON]

Can you import the TEXT file, then filter on whatever data you need ONCE then copy and paste?

[6StringJazzer]

Since it's tab delimited, I would use VBA to open it in Excel instead of reading it as a text file. Without having the file I can't do a test for you or confirm performance improvements but it would look something like this:

Please Login or Register  to view this content.

[DaveNUFC]

Thanks Jeff
Does the maximum number of records in excel pose a problem when opening the file via excel?
Basically I type a reference number in cell B5 on sheet1, click the macro then I want to it put those records from cell A11 down on sheet1 from the tab delimited file.

e.g. if cell b5 = "123" then I want to it bring back all records from the tab delimited file & insert from row A11 downwards on my spreadsheet. The original code I had did that however it took too long to read from a significantly big text file.

The code above, when I click the macro it isn't bringing back any records when I know they are there.

[DaveNUFC]

The above code does seem to open the file much quicker however it is not outputting the records.
Your help will be much appreciated.

[6StringJazzer]

I can't run this code without a sample file. Can you provide one? If the data is private just mock up a few rows of fake data.

[6StringJazzer]

Does the maximum number of records in excel pose a problem when opening the file via excel?

Your version of Excel has a limit of about 65,000 rows. Later versions have a bigger limit of about 1,000,000 rows. I don't know what happens if you try to open a tab-delimited file that is over the row limit.

[DaveNUFC]

Cheers
See attached, this is an example of a massively trimmed down version.
Say for instance I wanted a macro which read in this file & only brought back records for reference 123- the first bit of code I posted would do that however on a huge file it takes considerably long to process line by line.

I am after a more efficient bit of code. I think the one you posted will truncate at the maximum number of excel records when the file becomes in excess of the max in excel

[6StringJazzer]

I will take a look but if your files exceed the row limit in Excel then my solution is not the right one for you. But I don't know a way to make text file I/O any more efficient. My thought would be to use something besides Excel.

I'll get back to you tomorrow.

[jindon]

This creates new text file with the rows that is specified in Sheet1!B5 and read it to Excel.
It is working on your uploaded text file.
Not sure if this makes it faster though, but give it a try...

Please Login or Register  to view this content.