Reading from a text file in a macro

[LECXE]

COuld someone please assist me with reading from a text file in a macro without all the opening and closing appearing to the user.None of my books seem to cover this topic.I know you have to use Workbooks.OpenText Filename:="C:\dir\filename.txt", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(6, _
9), Array(9, 1), Array(34, 1), Array(43, 9)) , but how would I work out what will be returned.

But where and how do I start counting to return only the information I need?

 

[ETID]

You would not need to use code, (too many steps)...

Create a live link to the text file from within excel,

From excel click Data>Get External Data>New Database Query>....text files* from the list...follow the wizard from there (to select your text file)
then return your data to excel.

You can opt to use the wizard or MsQuery (a gui type interface, similar to access)at this point...
after your comfy with this...I would recommend the MsQuery option, there you can set criteria about the data that is returned from your text file easier.


when data is returned,...you can right click in the data and select properties to refresh on open...auto fill formulas etc.

this is now a live link, any time the workbook is opened. your current / up to date text file data comes in (if you set the refresh on open option)

 

[kylua]

I would use the sequential file option depending on how the text file is formatted. This way, you have absolute control of how the information is read and it can all be stored and processed within the VBA before you output what's required to the sheet.

 

[RobBroekhuis]

Depending on the length of the file and the complexity of trying to find the information you're looking for, it may be just as easy to open the file as a text file (as your original statement does) with

application.screenupdating=false

so that the user doesn't see the opening and processing that goes on, then find your information and close the file. You can indeed do the same thing using sequential files, but if you are already familiar with programming within the Excel framework, you may find it easier to manipulate a worksheet rather than a stream of data items. Plus, this gives you access to useful Excel features such as find and/or replace, which may come in handy.

Rob