Solved

OpenAsTextStream a .doc file in VBA (Excel)

Posted on 2003-12-02
6
4,328 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
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.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

751 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