function to qualify plain text rows before importing (xlFixedWidth)

[reteptnarg]

I don't know enough vb to script my way out of a paper bag. I got myself buried when i posted at experts-exchange ( http://www.experts-exchange.com/Prog..._26226748.html ) because my question was too vague and chatty and the thread got too long because people kept asking " why use excel !? "

anyway I learned at least what macro code I'd need to use to import from a nondelimited fixed width plain text
file and put the data ( based on colx to coly from a text file copybook ) into the columns I want . I have this code so far :
This module works !

Please Login or Register  to view this content.

but I'd like help to create a function that would first reduce the text file input to only the set of rows that contain a qualifier ... for example only those rows where the text in columns 1 and two is the string '50', and also the text in col 1400 thru 1403 = 'TEST' ...
and then have the file returned by this qualifier function parsed into my activesheet.

[rylo]

Hi

See if this gets you going.

Please Login or Register  to view this content.

rylo

[reteptnarg]

much appreciated rylo ... what's even better is i can follow it ! but like a maroon i left my test data at work this afternoon, will try it out tomorrow and post back Monday afternoon.
I can see it will work so I'll ask ahead of time: how do you write an user input box [similar to the initial Application.GetOpenFilename(...] where i can have the user input the filter requirements ??

as in Mid(fred, 1400, 4) = "TEXT"
an input box that would ask for (in the case of condition # 2 above)
start column scol2 , characters char2, targetstring TEXT

so i could change the script to be more flexible:
ie. pass these three variables as parameters to the Mid function,

Mid(fred, scol2, char2) = "TEXT"

by the way for the moderators, i got the message about code tags on posts but I'm assuming that doesn't apply to one-liner pseudo-code references. Correct me if I'm wrong . Mahalos !

[rylo]

Hi

One way

Please Login or Register  to view this content.

Another way would be to create a form that has to be completed.

Re the one liners. Yes, please do add the code tags. Also with the pseudo code as it makes things stand out and be easily identified.

rylo

[reteptnarg]

greetings rylo !
yep that works quite nicely

Please Login or Register  to view this content.

a couple of things I'm still struggling with :

- one thing that drives me nuts is Querytables.Add is touchy as all get out ... I'm only using it because it was on the original MS parsing example i read and I got it to work,
but have discovered the hard way that when .Refresh BackgroundQuery:=False goes bad
it stays bad, and you'll never get another successful run without a clean excel start.
anybody knows a simpler way to parse a text file into a worksheet without using the whole backgroundQuery, I'm all ears ! : )

-how do you do an input form ? I was trying to read up on an input box with multiple fields and ended up down the .HTA rabbithole ... and at the very least I'd like to make it conditional, ie. the user could decline to enter filters and an empty temp file wouldn't give an out of memory error

- how can I clear the temp file ? ( both clear and/or delete ) ie. azaz.txt after a run

I'm pretty happy to get this far and much appreciate your timely help ! : )

[rylo]

Hi

1) Text Files: One way is to do a record macro, open the text file using fixed width, add the break points etc. When the file is opened, you have the code to open and parse out the text file. You can use this to open it, and either work in the opened text file, or copy it across to your working sheet.

Another way would be to break up the text as required with some sort of delimiter when you are making the file c:\temp\azaz.txt, then when you open the file, you can use a delimited opening.

2) Input forms.

Go to the VBE, then Insert Userform. This will generate a form, and you can add textboxes, listboxes, comboboxes etc. You could also put in a checkbox that if selected, will allow users to enter no details. Plenty of examples on the forum of userforms, and associated problems :-)

3) To remove the interim text file, use the kill command

Please Login or Register  to view this content.

rylo

[reteptnarg]

Mahalos again rylo ... that gives me plenty to work with, and I now have the filtering solution I needed so will close this thread as SOLVED.
Appreciate your time and recommendations !