Start Free Trial

asked on

Copy certain text from word to excel...continued

I have a excel redmondb built for me. I need two slight modifications made to it, but I can seem to figure it out. Brian (redmodb) are you able to help? You can probably figure it out in 10 minutes. Or if someone can assit I would appreciate it.

Thanks,

Dustin

What are the modifications?

Sid

ASKER

Sid,

I have the macro and the sheets on my other question that this is related to. I need to extract the Approval and then Qty Made from the sheets. I actually got the program notes and notes_1 - notes_30 stuff taken care of, so we can pull that out and just get PART_NUM, REV, MACHINE NAME and add APPROVER and QTY_MADE if its easier. When I try to add Approver and Qty Made it doesnt seem to find it.

Thanks,

Dustin

Dustin the answer that you accepted in the previous thread (ID: 35132730) contains the updated file?

The reason why I ask is so that I can directly work on that.

Sid

ASKER

Sid,

Yep, look for the latest from 3/14 called Copy-certain-text-from-word-to-e.xlsm, and then there are DOC files you can use to test with.

Thanks,

Dustin

Ok Looking at it.

BTW First things first. I just saw something in your code and I couldn't resist...

Avoid the use of "End" in your code. That is bad programming practice. Use the proper way to exit.

End stops the program immediately - it's like a bullet hitting a Steel Wall!!!

It may cause unwanted errors. END gets rids of the application, but NOT its leftovers like object that you might have created in the code and thereby occupying the memory which will still be in use even after your program has 'Ended'.

Since "End" has been used in the beginning of the code and there is nothing to unload so you may get away with it but in general, remember the judicious use of 'End'. Now let me look at the code :D

Sid

Dustin there are so many files in that thread. If you don't mind, can you reattach just the relevant files here once again?

Sid

ASKER

Here you go
3-02346-1-1.doc
5-02527-C-1.doc
1832901-A-1.doc
Copy-certain-text-from-word-to-e.xlsm

Are you working on Office 2010?

Sid

ASKER

Nope, Office 2007. The person who programmed it for me was working in Office 2010. I can go back and change it to office 07 if you are programming in Office 2010

No that is Ok. I will manage that :)

Let me first run it and test it and see what it does :)

Sid

ASKER

no problem. Like I said, the program notes and the notes_1 - notes_30 can be removed to clean it up a bit. I only need the first couple cells and then the additional information I mentioned.

Please confirm if these are the fields that you require?

"File Name"
"PART_NUM"
"REV"
"MACHINE NAME"
"PGRM_NOTES"
"APPROVER"
"QTY_MADE"
"Comment"

Sid

ASKER

"File Name"
"PART_NUM"
"REV"
"MACHINE NAME"
"APPROVER"
"QTY_MADE"
"Comment" <- this was used for errors if any of the above failed

I think there is a simple way of doing it rather than this complex way. Do you mind if I change the code?

Also What is rev Number? Is it a part of "Part name" ? If yes then what is the rev number in

3-02346-1

Sid

Ok the code is ready. Could you please answer my last question above in ID: 35245602

Sid

ASKER

Yes Rev number is part of the name. In the case of 3-02346-1 the rev is 1, but others have 123456-A and the rev is A. Pretty straight forward. You can change the code if you'd like, but I dont want it to change the data thats displayed and the way its displayed.

Thanks,

Dustin

ASKER

i'm heading home for the night, i'll if I have the chance to test tonight I will, otherwise I'll do it first thing in the morning.

Got it: 5 mins and you will have the file ready :)

Sid

Ok I tested it couple of times. Is this what you want?

Sid
Certain-text-from-word-to-e-1.xlsm

ASKER

It worked for my first batch of file, but some of the files seem to be missing the :
like Approval ds 04/12/10 instead of Approval : ds 04/12/10. When it encounters this it'd be nice if we can still grab the text after Approval, as of now, it just errors out and wont go past the file. I have 5500 of this files to go through.

ASKER

I'm also getting this error...
Run-time error '1004':
Method 'Paste' of object '_Worksheet' failed

Would that be for others as well or only for 'Approval'?

Sid

ASKER

only approval, also if there is text and ___ like _rr 3/19/09___ can we remove the extra __, if its easier we can remove the ___ no matter what. I also noticed that every once and awhile it leaves a blank row, I assume this is where there was a 123456-A-1 and 123456-A-2 so it deleted the A-1 from the sheet but left the line blank.

Thanks,

Dustin

Ok Try this file.

This file takes care of the following.

1) If there is no ":" in Approval
2) Removes "_"

Sid
Certain-text-from-word-to-e-1.xlsm

ASKER

This works much better. But I am still seeing the blank rows and getting the error
Run-time error '1004':
Method 'Paste' of object '_Worksheet' failed

Is there anything that can be done about this two items? When I run it againts 1500 files in one of the folders i'd like to walk away and not have to click debug and hit F5 to keep it going. Running against 500 files I had to debug and F5 16 times

Thanks,

Dustin

Ok lets tackle blank rows first. :)

Can you show me an example?

Sid

ASKER

Here you go on both.
error.jpg
RowsCertain-text-from-word-to-e-.xlsm

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Sid,

Ran this against 1800 files, ran great. No errors, no blank rows Thanks for the quick turn around!

Thanks,

Dustin

You are welcome Dustin :)

Sid