View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
GS[_2_] GS[_2_] is offline
external usenet poster
 
Posts: 3,514
Default XL2003: VBE Won't let me break into running code

I've never worked with text files as large as GBs but I know that MB
files process fairly quickly using arrays and normal VB I/O (as opposed
to FileSystemObject).

Essentially, I read the entire file into a Variant variable in one shot
and then use the Split() function to dump it into another Variant
variable, resulting in a dynamic array. If the file contains data
records AND the first line contains the data fieldnames, the UBound of
the array is the record count. If the first line doesn't contain
fieldnames then the line count is the UBound+1.

To parse the data into separate months would be no problem by looping
the array checking which month each element belongs to and dumping that
into a String variable. If you set up a String variable for each month
then you should only have to loop the array once, and use a Select Case
construct to build the separate month strings.

Once you've parsed the file into appropriate month strings you can
write them back to individual files in one shot (each).

Keep in mind that handling files of this size will burden resources
some, but if your machine is fit for such tasks it should be no problem
to do.

You can run a progress bar in the array loop, OR report the progress in
the StatusBar for each line as it gets processed.

Summary:
1. Read source file into array in one shot.
2. Parse data into separate month strings using one loop.
3. Write month strings in one shot to separate files.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc