importing text into excel
Posted by Marc on March 20, 2001 7:42 PM
hi - I want to import a large data file into excel. I know how to do text-to-columns and delineate the data etc. but unfortunately this data comes from a report that is generated with page header and footers interspersed about every 80 lines of data or so.. I need to eliminate that data, as it wreaks havoc on my imports. how do i tell excel to disregard that extraneous text and just access the data i'm interested in. is there an advanced import function? or do i have to create a macro? thanks
Posted by David Hawley on March 21, 2001 12:39 AM
Hi Marc
I think you will be looking at a macro to do this.
Try avoiding using a loop though as they are horribly slooowwww. Make use of Excels built in features such as: Find, SpecialCells, AvancedFilter, AutoFilter to name a few.
Dave
OzGrid Business Applications
Posted by tom venn on March 21, 2001 4:28 PM
the question is, is it 80 or so, or is it exactly 80, exactly 81, and so on?
if your range is the same everytime, the following macro may work (for a range of exactly 80). To start the macro, go to cell A1. This macro goes to the 80th row then deletes information from column a through o.
if your range is different everytime, then there is a consistent header information which would allow you to sort, then delete the header.
you can find out what your range size is and if it is consistent by placing the number 1 in the cell next to the first header, then number 2 the next cell, then number 3 the cell below that, and so on until you get to the next range. stop one cell above the next header, and repeat the 1, 2, 3, 4.... repeat until the end of data. This is one method to see if your data range is cosistent.
Sub Macro2()
ActiveCell.Offset(79, 0).Range("A1:O1").Select
Selection.ClearContents
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Run "Macro2"
Application.Run "Macro2"
End Sub
cheers,
thomas
