Solved

OpenAsTextStream a .doc file in VBA (Excel)

Posted on 2003-12-02
6
4,221 Views
Last Modified: 2007-12-19
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

0
Comment
Question by:Zihif
  • 3
  • 3
6 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9865269
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
 

Author Comment

by:Zihif
ID: 9867456
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9867536
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:Zihif
ID: 9867969
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
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 200 total points
ID: 9868321
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
 

Author Comment

by:Zihif
ID: 9908517
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now