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
LVL 9
dustockAsked:
Who is Participating?
 
SiddharthRoutCommented:
Ok I have tried to address both the issues (Blank Rows + Pasting error)

Just check it for me.

Sid
Certain-text-from-word-to-e-1.xlsm
0
 
SiddharthRoutCommented:
What are the modifications?

Sid
0
 
dustockAuthor Commented:
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
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
SiddharthRoutCommented:
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
0
 
dustockAuthor Commented:
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
0
 
SiddharthRoutCommented:
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
0
 
SiddharthRoutCommented:
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
0
 
SiddharthRoutCommented:
Are you working on Office 2010?

Sid
0
 
dustockAuthor Commented:
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
0
 
SiddharthRoutCommented:
No that is Ok. I will manage that :)

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

Sid
0
 
dustockAuthor Commented:
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.
0
 
SiddharthRoutCommented:
Please confirm if these are the fields that you require?

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

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

0
 
SiddharthRoutCommented:
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
0
 
SiddharthRoutCommented:
Ok the code is ready. Could you please answer my last question above in ID: 35245602

Sid
0
 
dustockAuthor Commented:
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
0
 
dustockAuthor Commented:
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.
0
 
SiddharthRoutCommented:
Got it: 5 mins and you will have the file ready :)

Sid
0
 
SiddharthRoutCommented:
Ok I tested it couple of times. Is this what you want?

Sid
Certain-text-from-word-to-e-1.xlsm
0
 
dustockAuthor Commented:
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.
0
 
dustockAuthor Commented:
I'm also getting this error...
Run-time error '1004':
Method 'Paste' of object '_Worksheet' failed
0
 
SiddharthRoutCommented:
Would that be for others as well or only for 'Approval'?

Sid
0
 
dustockAuthor Commented:
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
0
 
SiddharthRoutCommented:
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
0
 
dustockAuthor Commented:
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
0
 
SiddharthRoutCommented:
Ok lets tackle blank rows first.  :)

Can you show me an example?

Sid
0
 
dustockAuthor Commented:
0
 
dustockAuthor Commented:
Sid,

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

Thanks,

Dustin
0
 
SiddharthRoutCommented:
You are welcome Dustin :)

Sid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.