Search Word for text and send values next to text to Excel file

[fordma2831]

This is my first post and I am not certain that I have exhausted the search function on the board, but I was hoping someone could point me in the right direction. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am using Windows XP and Office 2003. <o></o>
<o></o>
I am looking to automate a process where information is sent to someone in a text format and they in turn transfer that data to an excel file. It basically looks like this (but includes a lot more info):<o></o>
<o></o>
Service Request #: 123456<o></o>
Instrument Type: New Instrument<o></o>
Lot/Serial #: 123456<o></o>
SR Type: Product Complaint<o></o>
Service Coverage: Maintenance Agreement<o></o>
<o></o>
The info on the left (text preceding the colon) is always the same but the values following the colon can change. The excel file has all of the text before the colon and they just enter the information into the cells. I am looking to make it so that an Excel macro (or maybe a word/outlook macro) automatically scans the text document and sends this info to the exact same cells in excel every time. <o></o>
<o></o>
The text file is actually a message from an Oracle database and I am not going to be able to make any changes to the way the data comes in. <o></o>
<o></o>
My only real experience with VBA is from recording macros and looking at them in the editor. I did just pick up a book though...<o></o>
<o></o>
Does this seem feasible? <o></o>
<o></o>
Thanks for the help. <o></o>
<o></o>
Matt <o></o>

 

[Legacy 68668]

but includes a lot more info

What kind of more info ?

automatically scans the text document and sends this info to the exact same cells in excel every time

Does this mean the document from Oracle is not in order everytime ?

 

[Ed in Aus]

open the text file in excel hit record macro then highlight column a, then go to Data -> text to Columns -> Select delimited -> go to next screen -> and check on semicolon for the delimeter hit finished.

This will produce the recorded macro for you.

 

[fordma2831]

Sorry. The more info is actually just more text with fields that follow. It is exactly the same format as what I posted but it is jsut 3 times longer in real use.

All of the fields from Oracle are in the same order and the text before the colons are the same, the values that follow are different with every message. The excel file that enter the data into is basically a form, so the people enter the data into the same cells every time. The macro would hopefully just speed this up and reduce errors.

 

[Legacy 68668]

Sorry. The more info is actually just more text with fields that follow. It is exactly the same format as what I posted but it is jsut 3 times longer in real use.

All of the fields from Oracle are in the same order and the text before the colons are the same, the values that follow are different with every message. The excel file that enter the data into is basically a form, so the people enter the data into the same cells every time. The macro would hopefully just speed this up and reduce errors.

Hummm

What do you want to do with the macro?

Create a txt file ?
Populate the cell(s) from the text file ?

 

[fordma2831]

Just populate the cells from the text file. I am working on using the text import idea that Ed in Aus gave me. It definitley a good idea but I am not sure that it will work long term since it is a few extra steps and 95 different excel novices will be using this process.

 

[Ed in Aus]

Eliminate the users, control the macro from one location then redistibute the output to the other users... something like a generic email account that they all have access to maybe.

can you get that .txt file sent sent to a location? like a folder that none of the users have access to then set the macro to run every 5 mins?

not sure if this is going to help but I have had to do something like this in the past from an oracle database out put into another one.

 

[Legacy 68668]

Ok

How do you exactly want to do?

1) You have already populated the headings in col.A
2) Read the text file and place the corresponding data in col.B

Is this what you wanted ?

 

[fordma2831]

Ed- that's a good idea. I think I will look into doing something like that.

 

[fordma2831]

Ok

How do you exactly want to do?

1) You have already populated the headings in col.A
2) Read the text file and place the corresponding data in col.B

Is this what you wanted ?

I was really looking to see if there was a way to scan a file for specific terms and then return a string of values following that term. Then, copy the string and send that string to a specific cell in excel. Sorry if I am not helping much here by being a little vague.

The data import method certainly works and I hadn't really explored that part. I guess the only concern with that one is if something changes in the formatting of the text file and then that impacts how the excel file is created. I haven't tried it enough yet to know though.