limit number rows that Excel imports

[crawfme]

I would like to write a macro that would read in only the first couple lines of a very large text file. Basically I want to read the first few lines to determine if the file meets specific criteria before deciding if the entire file will be opened. I can specify the StartRow for reading data, but is there a similar capability to specify the end row? Thanks.

 

[kjv1611]

Are you using a custom-built VBA module downloaded from a site online? Reason I ask is that there are at least a couple such modules folks have put together and posted online. The one I used does specify start and stop lines... probably as optional.. so if you downloaded the code, you may want to check for any comments therein that will explain it..

And this Blog posting I came across shows you how to do the import character by character....

http://vbadud.blogspot.com/2007/06/vba-read-text-files-with-leading.html

So if you wanted to go that route, you could basically have a couple of loops and at least one counter... so something like:

1. Outer loop for Rows.. based on Chr(13) or vbCrLf perhaps for the line breaks.. You would go to the new record at each one of those, and also count based on those... So once intLinesRead > 2 or whatever number you want, you'd quit out of the loops...

2. Inner loop for fields... based on whatever delimiter is in the text file... It would be inside this loop where you would first build the full string for the given field's value, and then do the rs.Fields("MyField1") = strTextField1 afterwards, before moving to the next field and record... so you'd have to have another loop for building the string value for each field.

Anyway, that does sound like it'd be an alright option if you only need to read in a couple lines of data for certain.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[PHV]

What have you tried so far and where in your code are you stuck ?
Hint: either use the Scripting.FileSystemObject or the native VBA instructions (Open, Line Input, Close)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[crawfme]

Thanks to both of you. It's going to take me a little time to digest what you've suggested.

 

[kjv1611]

crawfme,

You might want to first look into what PHV suggested:

Hint: either use the Scripting.FileSystemObject or the native VBA instructions (Open, Line Input, Close)

I don't remember off-hand, b/c I've only rarely used the text file reading/writing functions, but the Line Input portion... that's what I'd be digging into.. see if there is a way to get a counter or something for that, and you could simply use that to limit your input/import.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57