breaking a file of 4000 records into smaller files?? 1

[ftpdoo]

I have a file of 79,000 records in excel - any suggestions how i could quickly break it up into smaller files of 4000 records???

Thanks,
Jon

 

[SBendBuckeye]

I'm not sure exactly how to make this work, but you might want to check out the the range object in Excel. It is based on cells(M, N) where M is the row number and N is the column number. Once you have a range defined from row 1 to row 4000 by the total number of columns, I think you could then copy the range to a different worksheet and save the second worksheet as its own file. Then delete the 4,000 rows in worksheet 1 and repeat the process until you've split them all out.

Sorry this is so generic but hopefully it will give you a couple ideas to get started. You will need a refence to the Microsoft Excel stuff in Tools, References to be able to use the Excel objects. When I get to work, I'll try to post some code that sets up the objects for you. Check out the Tek-Tips forum Microsoft: Access Modules (VBA Coding) for other examples also.

Hope this helps and good luck!

As an aside, does your handle imply you are an experienced FTP person? If so, I may have some questions for you as I am going to try to use Access as an application server for a process that includes at least one piece on a Unix box which I hope to be able to control with FTP.

 

[andrew299]

Just a quick snippet of code

Range("a1", ["IV4000"]).Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste

Same idea as SBendBuckEye above. You will have to refine it into a loop to run on a lrage number of cells

 

[andrew299]

Ta for the Star
Andrew299