David,

I gave "Importing data a try, but it is not powerfull enough to do what I
needed. One record consists of anywhere from 29 to 35 fields (The last
couple of fields are normally different on every record depending on what
information is flagged by the sender.

Like I said, I can do this very easily in PERL, but I was hoping to do so in
VBA so It's easy for the folks I built it for to use.


"David" <David@discussions.microsoft.com> wrote in message
news:C89451E9-0CDB-4AA6-ABE6-1736AEF055AA@microsoft.com...
> Hi Rob,
> You get an electronic form of this file? When I look at what you have
> below,
> this all comes on one line? A record per line? What "type" of file, what
> is
> the file extension? Have you attempted to open this file with Excel? I am
> not
> talking about how is appears on a computer screen, but form the file is is
> in, prior to it's viewing.
>
> Really, try abd open it with Excel and see what happens. If it is what
> appears below, I think you will be able to open it. Again you may have to
> play with it and attempt this more than once to get what you want. The
> process of opening it will "parse" it for you. This does not require and
> VB
> code. YOu will taken through several "Screens" called the Text Import
> Wizard", which allows you to describe the format of the text file you are
> trying to open in Excel. You will not need to parse it at this time, but
> simply put it in a form that is readable and useful.
>
> When you have it in this form, you can delete whole columns, which will
> represent fields of data. Try this and see if it will open for you. In
> Excel
> - File/Open, make sure that you tell Excel to look for "All Types" in the
> Open dialog or it will not recogize anything but Excel files.
>
> "Rob" wrote:
>
>> David,
>>
>> When I say "justified," I mean it's a database report that is justified
>> to
>> the screen (fields are arranged to fit the screen -- like a justified
>> form
>> in ACCESS):
>>
>> Name: Joe Blow SSN: 987-65-4321 Home Phone: (850) 555-1234
>> Start-Date: 12/25/2004 Occupation: Dead beat DOB: 01/01/1986
>> Education: Graduated from 6th Grade
>> address, etc...
>>
>> With Regular Expressions, I can grab a line, parse the data I want ("Joe
>> Blow", "(850) 555-1234") and insert into a new csv file/database/etc. I
>> used to do it all the time in PERL, but I haven't used the language in
>> 2 - 3
>> years. I can learn to do it in C#, but then I have the same problem -- I
>> will have to ensure every machine can run PERL or C#, and I will have to
>> train every user (sometimes it's easier to teach a monkey to fly).
>>
>> Can I do anything similar to regular Expressions with VBA?
>>
>>
>> "David" <David@discussions.microsoft.com> wrote in message
>> news:E57DAD84-4518-4B41-98F4-9B97C9EAB7DC@microsoft.com...
>> > Hi Rob,
>> > If the file is a text file and it is regularly separated, you should be
>> > able
>> > to simply open it in Excel. YOu will be taken to various screens and
>> > alternatives to decsribe the data layout. I am not sure what you mean
>> > by
>> > "justified", but Excel and open many formats. Comma, tab, space, etc.
>> > You
>> > may
>> > have to play around with the opening of it to find the right method
>> > that
>> > fits
>> > your data, but I expect it will open and be useful.
>> >
>> > "Rob" wrote:
>> >
>> >> All,
>> >>
>> >> I have a very useful report I get weekly (text document) that is in a
>> >> very
>> >> un-useful format... It gives me information on all my personnel
>> >> (hundreds
>> >> of
>> >> folks) in a "justified" format. I want to import the information I
>> >> need
>> >> from each individual into excel (CSV file) so I can manipulate the
>> >> data
>> >> or
>> >> search, etc... In the end, I'll probably use Access - but I still
>> >> have
>> >> the
>> >> same problem.
>> >>
>> >> The easiest way I know to grab the information I need is using Regular
>> >> Expressions to parse through each line of the report.
>> >>
>> >> I "use-ta-could" in PERL, or I could teach myself using C#.
>> >>
>> >> I really would like to write the code using VBA because I'm starting
>> >> to
>> >> feel
>> >> comfortable with the language and I've created about a dozen routines
>> >> that I
>> >> use in Word/Excel/PowerPoint. I'd like to continue becoming more
>> >> proficient.
>> >>
>> >> Any help putting me in the right direction would be greatly
>> >> appreciated.
>> >>
>> >> Rob F
>> >>
>> >>
>> >>
>>
>>
>>