Find info from a .doc file 3

[bencnu]

I am trying to open a document and retrieve some info from it and place it into excel. The file name would be 9005-607.doc or something like that, and i want it to search through the file till it finds the sentence seq # 20 and then i want it to put anything that starts with 2100 or 2000 to be put into a cell in excel. Like if it came across 2100-003 i want it to put that into cell a1 and if it finds 2000-032 next i want it to put it into cell b1 and so on until it finds seq #30. Any suggestions? I would really appreciate it. THANKS!

BEN

 

[tbl]

Can you give us a bit more info please as to what sort of files you are dealing with ?

I would read the file into Excel and then perform the search, locate the offending words and enter them into another sheet before clearing the input sheet which acts as a scratch pad

 

[bencnu]

Here is a sample of the document. I bolded the info i want to put into excel. I am putting it into excel so the user can just simply print out the wire or cable they are going to need to cut, instead of printing off the whole process, which could be a about 15 pages long.

--

SEQ # 20 WIRE CUT

SCHLEUNIGER 9500

WIRE P/N WIRE
DESCRIPTION
LENGTH
QTY STRIP END #1 STRIP END #2

2100-2371 CBL, 9 TWPR, 22 AWG, GRY 38 IN 1 3 IN 3 IN

2100-2114 CBL, COAX, BLK 41.25 IN 1 --- ---

EUBANKS 4600
NSI P/N DESCRIPTION LENGTH QTY
2200-387 HEATSHRINK, BLK 1 IN 2

2200-733 HEATSHRINK 1 IN 2

2200-028 TUBING, CLEAR .1 IN 14

 

[bencnu]

Does that info help any? Any tips would be much appreciated!

 

[bencnu]

....bump....
I still can't figure this out...
Any help would be awesome!

 

[tbl]

Hi bencnu,
sorry that I didn't respond to your last post but I got no mail notification.
Before spending time on reading in files and parsing them, I would like a few more answers. A file with the extension doc might be a Word file, but it might also be a text file.Could you please read one of your files into Excel using File OPEN. Just rename your file to NAME.txt and open it with Excel. The text wizard will start up and by playing with the delimiters you will get your info into Excel. I would like to know what delimeters you used to get your text import, and if you get a consistent import of data. I am thinking on the lines of importing the document into a new excel sheet, and scanning for the seq # ? , and then looking for lines starting with the numbers you want. But to do this your data would have to arrive in excel formatted exactly as you have shown it.

Richard

 

[TonyJollans]

Hi bencnu,

Not entirely sure about precise strings to find and copy, but this should give you something to work with ..

Sub GetWordData()

Dim wdApp As New Word.Application

Dim Seq30 As Long
Dim xlCol As Integer

wdApp.Visible = True
wdApp.Documents.Open ("C:\My Documents\9005-607.doc")

With wdApp.Selection.Find
    .ClearFormatting
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
End With

With wdApp.Selection
    
    .HomeKey Unit:=wdStory
    .Find.Text = "SEQ # 30"
    .Find.Execute
    Seq30 = .Start
    
    .HomeKey Unit:=wdStory
    .Find.Text = "SEQ # 20"
    .Find.Execute
    .Collapse wdCollapseEnd
    
    .Find.Text = &quot;<2[0-9]00-[0-9]{3,4}&quot;
    .Find.MatchWildcards = True

    Do
        .Find.Execute
        If Not .Find.Found Then Exit Do
        If .Start > Seq30 Then Exit Do
        xlCol = xlCol + 1
        Cells(1, xlCol) = .Text
        .Collapse wdCollapseEnd
    Loop

End With

wdApp.Documents.Close
wdApp.Quit
Set wdApp = Nothing

End Sub

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[bencnu]

tbl, did excel 2000 have a text wizard? I tryed to open the file as a .txt file and I recieved a "File format is not valid" error. :/

TonyJollans, thank you for the code. I wasn't quite able to get it working though..... I would get an error right away on this line ---> "Dim wdApp As New Word.Application"

I appreciate you two helping out

 

[TonyJollans]

Hi bencnu,

Make sure you have a reference to Microsoft Word under Tools > References.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[tbl]

Hi bencnu, sorry for the slight delay,been on holiday.
You have 2 options for solving your problem. The first is my amateurish solution which is not the most professional one, but does have the advantage that it is completely transparent. The second is the professional programmer's solution. What Tony has proposed is the most elegant solution and works fine if you do as he says and select Microsoft Word under VB Tools References, otherwise Excel VBA does not recognise the reference to Word VBA. It depends on your confidence in programming the code to get what you want. If I was doing this for myself I would use Tony's code and modify it as he intended. The one virtue that my solution has is that you start in Excel and stay in Excel and evrything that happens is visible - easy for debugging. Tony's code runs perfectly first time, (he's very good at this !), but I suspect that you will get bogged down trying to extract the info that you want.
For what it's worth I will detail a very simple method that I have successfully tested with your data. First save your Word file as MSDOS Text. Secondly open your .txt file with Excel and you will see that Excel 2000 does have a text wizard for .txt files only. If we program this with VBA this will be automatic.You will find , I hope , that your data has arranged itself in data blocks per line so you will need no delimeters. The next step is simply to delete lines tht DO NOT contain the data that you wish to keep. The result will be a separate Excel book containing the data that you do want.The last step is to program the previous 3 steps which is dead easy. If you are interested test this manually and let me know what problems you have had.

Richard

 

[bencnu]

Thank you guys! I finaly got the code working. You were right about the VBA reference. I was trying to reference word through excel not VBA. Thanks Again

 

[bencnu]

Actually I have one more question...

If Left(FileEntered, 1) = "A" Then
FileName = Join(Array("C:\PLANT\ENG\DOC\GE-A\", Left(FileEntered, 4), ".DOC&quot, "&quot
ElseIf Left(FileEntered, 1) = "B" Then
FileName = Join(Array("C:\PLANT\ENG\DOC\GE-B\", Left(FileEntered, 4), ".DOC&quot, "&quot
ElseIf Left(FileEntered, 1) = "C" Then
FileName = Join(Array("C:\PLANT\ENG\DOC\GE-C\", Left(FileEntered, 4), ".DOC&quot, "&quot
Else
FileName = Join(Array("C:\PLANT\ENG\DOC\", Left(FileEntered, 4), "\", Right(FileEntered, 5), ".DOC&quot, "&quot
End If

Label13 = FileName

wdApp.Documents.Open (FileName)

I was wondering how you could figure out if there was an actual file before it tries to open it. I keep getting an error msg when there is a file that can't be opened. And if there is no file, I would like to have it open a browse menu.

Some thing like this?
Filename = Excel.Application.GetOpenFilename()

 

[PHV]

Try something like this:
Set fso = CreateObject("Scripting.FileSystemObject&quot
If fso.FileExists(FileName) Then
wdApp.Documents.Open(FileName)
Else
MsgBox FileName & " doesn't exist."
End If

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[bencnu]

Thank you PH that did help a lot . I have ran into another problem.
When it opens the .doc file and searches for the part numbers starting with 2200,2100 and 2000 it thinks I modified something and ask me to save it. I would put in code to make it automaticaly save it, but they are read only files. And when the Save as window pops up i get a error in VBA because it didn't close.

wdApp.Documents.Close

Any suggestions?

Thank you all again!

 

[PHV]

Try this before closing:
wdApp.ActiveDocument.Saved = True

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

Or another way:
wdApp.Documents.Close wdDoNotSaveChanges ' =0

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[bencnu]

yay it worked!
wdApp.ActiveDocument.Saved = True
thanx