OpenAsTextStream a .doc file in VBA (Excel)

Hey all!

What I need to do is to open a .doc file, loop through each line, extract specific information and save that to a .txt file.  I have about 5,000-6,000 folders that I need to loop through, each containing anywhere from 5 to 200 files that I need this sub to run through.  Which means, this isn't a process that can be managed manually.

Below is the code that I have... it works Perfectly when trying to open .txt files... However, my current need is to have this work on .doc files as well.  I've tried .OpenAsTextStream(1, -1) and (1, 0), however neither one of those work.  I've tried Open objItem for Input as #1, but that doesn't work... Any other options here?  Any eclectic solutions out there?

Sub BID_SUMMARY_CREATION()
Dim FSO1, FSO2, FSO3, FSO4
Dim objItem As Object
Dim parFolder
Dim objFolder
Dim WLine As String
Dim VNUM As Long
Dim EndUser As String
Dim Dumpfile, RF, A1, A2
Dim Default1 As Integer
Dim BID As String
Dim ZDrive As String
Dim WriteTrue As String
Dim ExtID As String

ZDrive = "Z:\"
Set FSO2 = CreateObject("scripting.filesystemobject")
Set FSO3 = CreateObject("scripting.filesystemobject")
Set FSO4 = CreateObject("scripting.filesystemobject")

For Default1 = 2 To 5698
    BID = Worksheets("sheet1").Cells(Default1, 1).Value
    Debug.Print "BID: " & BID
    objFolder = Dir(ZDrive & BID & "*", vbDirectory)
    Debug.Print "objFolder: " & objFolder
    If Len(objFolder) >= 4 Then
    Set parFolder = FSO2.getfolder(ZDrive & objFolder)
    Set FSO1 = CreateObject("scripting.filesystemobject")
    Set A1 = FSO1.createtextfile(parFolder & "\Bid Summary.txt", True)
        For Each objItem In parFolder.Files
                Debug.Print "parFolder: " & parFolder
                Debug.Print "objItem: " & objItem
                Set Dumpfile = FSO3.getfile(objItem)
                Set RF = Dumpfile.openastextstream(1, -2)
                While Not RF.atendofstream
                    WLine = RF.readline
                    Debug.Print "WLine: " & WLine
                    If Left(WLine, 7) = "Version" Then
                        VNUM = Trim(Right(WLine, 4))
                        Debug.Print "VNUM: " & VNUM
                    End If
                    If Left(WLine, 8) = "Customer" Then
                        EndUser = Trim(Right(WLine, (Len(WLine) - 9)))
                        Debug.Print "EndUser: " & EndUser
                    End If
                    If Trim(WLine) = "Transaction Summary Table" Then
                        WriteTrue = "True"
                    ElseIf Trim(WLine) = "End Transaction Summary Table" Then
                        WriteTrue = "False"
                    End If
                    If WriteTrue = "True" Then
                        If Left(WLine, 4) <> "    " Then
                        If Trim(WLine) <> "" Then
                            A1.writeline WLine & "      V " & VNUM
                            Debug.Print "Wrote Line to file."
                        End If
                        End If
                    ElseIf Trim(WLine) = "End Transaction Summary Table" Then
                        A1.writeline WLine & "      V " & VNUM
                        Debug.Print "Wrote Line to file."
                    End If
                Wend
                RF.Close
        Next objItem
    Else
        Worksheets("sheet1").Cells(Default1, 2).Value = "No Folder Found!"
    End If
    VNUM = Empty
    EndUser = ""
    BID = ""
    WriteTrue = ""
    A1.Close
Next
End Sub

'=================
Thanks in advance for looking at this!
Zihif

ZihifAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nmcdermaidCommented:
A DOC file is not a text file its a binary file.

If you want to export lines of text from a DOC file, write a Word VBA Macro.
0
ZihifAuthor Commented:
nmcdermaid

Well, if it's a binary file then I should be able to Open objItem for Binary as #1 and read it from there?  I've tried this method and it still just returns encrypted lines.

Any other thoughts or suggestions?

Zihif
0
nmcdermaidCommented:
Its saved in whatever binary file format that Microsoft Word is in. If you can get the spec of the .DOC format from Microsoft then you can happily open it as binary, parse it using the spec, then get the text out.

However even if you were to crack the format, you would find that it would most likely change in the next version of Word. That's why a Word macro is a good idea... you can just paste the macro into the next version of Word and it'll work.

If you want you can (from your existing code) use Word to save the current doc as a text file then process that text file, then go on to the next doc

If you are interested then I can whip up some code.





0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ZihifAuthor Commented:
nmcdermaid

I wrote the code in Word, and I'm still running into the same problem.  What method should I use to open the .doc files so I can read'em?

Zihif
0
nmcdermaidCommented:
Instead of opening as a TextStream, you need to open as a Word object, then you can use native Word function to parse the data.

So, instead of this:

Set Dumpfile = FSO3.getfile(objItem)
Set RF = Dumpfile.openastextstream(1, -2)


You need something more like this:

Dim WordApp As Word.Application  ' need to set a reference to Word if in VB. Not necessary if in Word VBA

' Open the document in Word
Call WordApp.Documents.Open("")

' Select the whole first line
Call WordApp.Selection.EndKey(wdLine, wdExtend)


' Save the line of text to a text file
A1.writeline WordApp.Selection.Text

' Go to the next line
Call WordApp.Selection.GoTo(wdGoToLine, wdGoToNext)

' Something to loop that I haven't figured out yet!!

' Close the current document
WordApp.Documents.Close (False)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZihifAuthor Commented:
Holy Crap!!

Sorry I haven't responded in awhile... I got busy going here, doing this, blah blah blah...

nmcdermaid

Thanks a ton for your help on this.

Zihif
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.