Word-VBA-read file & write to properties

Gudare/Craig:

>>am open to a whole new proccess

If you suggest an easier solution, I would be open to it, as i said, the solution I posted was my best guess.

Regards, rdmjrb

Well, the following code will get you through opening the files for usage, but without a file or specific examples of your datascrape/wanted results once you've got it open, I'll show you the part for dealing with the file itself:

Sub OpenCloseFiles()

    ' Load all files wanted to be used in macro into one folder with
    ' no other files inside that folder. Modify FILE_PATH constant
    ' to read correct path.
   
    Dim strFileName As String
    Dim docOpenedFile As Document
   
    Const FILE_PATH As String = "C:\My Documents\"
   
    strFileName = Dir(FILE_PATH)
   
    Do Until strFileName = ""
        If Not ((strFileName = ".") Or (strFileName = "..")) Then
            If Not GetAttr(FILE_PATH & strFileName) = vbDirectory Then
                docOpenedFile = Application.Documents.Open(FILE_PATH & strFileName)
                ' Perform datascrape and operations here.
                docOpenedFile.Close
            End If
        End If
        strFileName = Dir()
    Loop

End Sub

-Craig

Craig:

The datascrape would be something like this:

Each document contains 4 pieces of information:
1. Name (First Middle/M. Last) - which I would convert to (Last, First Middle/M.)
2. Group
3. Title
4. FocusDescription

I will place the data into 2 properties:
1. Title (Name - Group - OfficeLocation - (Title))
2. Comments (FocusDescription)

Since I do not have the OfficeLocation within the document each location will have its own function with the OfficeLocation hardcoded.

Let me know if you need further information.

Regards, rdmjrb

Definately, I'd need to see a sample document, basically, so I know how to get at the data, ie: Field bookmarks, which paragraph number, so on, so forth. Right now, I'm basically blind as to where the data is within a word document. If you'd like, email me a sample at craigt.farrell@pchelps.com, and I'll be happy to review it. Also, the properties you're discussing, where do you intend to save these, into the General Properties of the entire document as custom properties or if not that, please be more specific as to your intended storage.

Craig:

1. check your email for the document.
2. i will save them into the regular properties as said in the previous post (Title, Comments)

Regards, rdmjrb

Quick and dirty.

Option Explicit

Sub OpenCloseFiles()
On Error GoTo Err_OpenCloseFiles

   ' Load all files wanted to be used in macro into one folder with
   ' no other files inside that folder. Modify FILE_PATH constant
   ' to read correct path.
   
   Dim strFileName As String
   Dim docOpenedFile As Document
   
   Const FILE_PATH As String = "C:\My Documents\"
   
   strFileName = Dir(FILE_PATH)
   
   Do Until strFileName = ""
       If Not ((strFileName = ".") Or (strFileName = "..")) Then
           If Not GetAttr(FILE_PATH & strFileName) = vbDirectory Then
               docOpenedFile = Application.Documents.Open(FILE_PATH & strFileName)
               DataScrape docOpenedFile
               docOpenedFile.Close
           End If
       End If
       strFileName = Dir()
   Loop

Exit_OpenCloseFiles:
    On Error Resume Next
    docOpenedFile.Close
    Set docOpenedFile = Nothing
    Exit Sub

Err_OpenCloseFiles:
    Select Case Err.Number
    Case Else
        MsgBox Err.Number & ": " & Err.Description, vbCritical, "OpenCloseFiles"
        Resume Exit_OpenCloseFiles
    End Select

End Sub

Sub DataScrape(ByRef doc As Document)
   
    Dim strTitle As String
    Dim strComment As String
    Dim strTemp As String
   
    Const LOCATION As String = "Enter Location Here"
   
    ' Get Name
    ' Remove the paragraph mark at end of paragraph
    strTemp = Left(doc.Paragraphs(4).Range.Text, Len(doc.Paragraphs(4).Range.Text) - 1)
    strTitle = Right(strTemp, Len(strTemp) - InStr(InStr(strTemp, " ") + 1, strTemp, " ")) & ", " & Left(strTemp, InStr(InStr(strTemp, " ") + 1, strTemp, " ") - 1)
   
    ' Get Group
    strTemp = Left(doc.Paragraphs(8).Range.Text, Len(doc.Paragraphs(8).Range.Text) - 1)
    strTitle = strTitle & " - " & strTemp
   
    ' Get Office Location
    strTitle = strTitle & LOCATION
   
    ' Get Title
    strTemp = Left(doc.Paragraphs(5).Range.Text, Len(doc.Paragraphs(5).Range.Text) - 1)
    strTitle = strTitle & " - (" & strTemp & ")"
   
    strComment = Left(doc.Paragraphs(11).Range.Text, Len(doc.Paragraphs(11).Range.Text) - 1)

    doc.BuiltInDocumentProperties("Title") = strTitle
    doc.BuiltInDocumentProperties("Comments") = strComment

End Sub

craig:

getting invalid use ofproperty here:
>>docOpenedFile = Application.Documents.Open(FILE_PATH & strFileName)

also, these files are within the same directory, example, all of houston in the houston dir, sanFran in sanFran, etc.

thanks, ron

My bad, line of code should read: set docOpened File = ...

Craig:

The code is running fine now, but it only sets the Title property=<<IMAGE01>>


The rest of the Title or Comments properties are not being set.

Also I see the searches are hardcoded:
' Get Group
   strTemp = Left(doc.Paragraphs(8).Range.Text, Len(doc.Paragraphs(8).Range.Text) - 1)

And so on, but these values will continually be different as some Group names are 6 chars and others are 23.

If you show an example of how to serach for, say a colon, then begin the search at that point and stop when there is a chr(13) or chr(10), this would encompass all groups.

Example:
Group Name:
Information Technology Group chr(13) & chr(10)
<<New Line Here>>

Once I see this logic I can code for the rest of the searchs in this manner.
   

Actually, it's hardcoded to look for the paragraph number, not the length of text, it doesn't care about the length of text, only removing the char(13) at the end, therefore you shouldn't need to search as long as they keep the same # of paragraphs. As to pulling the wrong information, this worked perfectly against the test file you sent me, so I don't know why you're seeing image1. *shrugs* Sorry, according to what you gave me, the scrape works correctly. Are you running it against the same file?  If so, what version of Word, we might be in diff versions?

Craig:

Ok I dig. It's working fine for the test doc,but not for a realone. Check email, getting this error:

5:Invalid procedure or call.

How can I use the code in a directory that contains multiple files?

Two problems were occuring:
First, Paragraph 5, not 4, for the user's name, was failing on blank line, had to change both halves of the equation.

I was using a nested instr that needed the middle initial to exist or it would fail, not a big worry. It now contains an if statement to deal with the issue.

This code if you run: OpenCloseFiles will loop through *every* file in a folder, so it's already setup to do that, that's what the dir() is all about.

I modified this code slightly to deal with the save over message, so you should be good to go. Copy/paste this in and give it a whirl.

Option Explicit

Sub OpenCloseFiles()
On Error GoTo Err_OpenCloseFiles

  ' Load all files wanted to be used in macro into one folder with
  ' no other files inside that folder. Modify FILE_PATH constant
  ' to read correct path.
 
  Dim strFileName As String
  Dim docOpenedFile As Document
 
  Const FILE_PATH As String = "C:\data\client\rdm\"
 
  strFileName = Dir(FILE_PATH)
 
  Do Until strFileName = ""
      If Not ((strFileName = ".") Or (strFileName = "..")) Then
          If Not GetAttr(FILE_PATH & strFileName) = vbDirectory Then
              Set docOpenedFile = Application.Documents.Open(FILE_PATH & strFileName)
              DataScrape docOpenedFile
              docOpenedFile.Save
              docOpenedFile.Close
          End If
      End If
      strFileName = Dir()
  Loop

Exit_OpenCloseFiles:
   On Error Resume Next
   docOpenedFile.Close
   Set docOpenedFile = Nothing
   Exit Sub

Err_OpenCloseFiles:
   Select Case Err.Number
   Case Else
       MsgBox Err.Number & ": " & Err.Description, vbCritical, "OpenCloseFiles"
       Resume Exit_OpenCloseFiles
   End Select

End Sub

Sub DataScrape(ByRef doc As Document)
   
   Dim strTitle As String
   Dim strComment As String
   Dim strTemp As String
   Dim LastNamePosition As Integer
   
   Const LOCATION As String = "Toronto"
   
   ' Get Name
   ' Remove the paragraph mark at end of paragraph
   strTemp = Left(doc.Paragraphs(5).Range.Text, Len(doc.Paragraphs(5).Range.Text) - 1)
   LastNamePosition = InStr(strTemp, " ")
   If LastNamePosition <> 0 Then
        If InStr(LastNamePosition + 1, strTemp, " ") <> 0 Then
            LastNamePosition = InStr(LastNamePosition + 1, strTemp, " ")
        End If
    End If
   strTitle = Right(strTemp, Len(strTemp) - LastNamePosition) & ", " & Left(strTemp, LastNamePosition - 1)
   
   ' Get Group
   strTemp = Left(doc.Paragraphs(8).Range.Text, Len(doc.Paragraphs(8).Range.Text) - 1)
   strTitle = strTitle & " - " & strTemp
   
   ' Get Office Location
   strTitle = strTitle & " - " & LOCATION
   
   ' Get Title
   strTemp = Left(doc.Paragraphs(6).Range.Text, Len(doc.Paragraphs(6).Range.Text) - 1)
   strTitle = strTitle & " - (" & strTemp & ")"
   
   strComment = Left(doc.Paragraphs(11).Range.Text, Len(doc.Paragraphs(11).Range.Text) - 1)

   doc.BuiltInDocumentProperties("Title") = strTitle
   doc.BuiltInDocumentProperties("Comments") = strComment

End Sub

Craig:

ok...its running like a champion,however I placed some files in the directory to test and they were not modified. Only 2 files were modified: original(containing the macro) and another misc one. Then I deleted the misc one and re-ran itand only the orig one was modified.

What do you think the problem is?

And do I have to open all the files before the code works?

To confirm:
The files are in the directory main, not in a subfolder of the directory.

The files are .doc files

If you step through, do the files open and close without saving changes?

Yes
Yes

and sometimes

Here is what happens:
1. I have 3 files: 1.doc, 2.doc, 3.doc
2. The macro is in 1.doc
3. I run the macro successfully
4. Changes occur in all files
5. Add 3 more files:4.doc and so on
6. Run the macro from 1.doc and changes do not occur
7. Add the macro to 4.doc and run
8. changes occur

Its strange...i want to have one directory and one template file that contains the macro, and any file in that directory willhave changes made no matter what order and so on.

I am trying to troubleshoot it, but without much luck.

perhaps there is a variable being left open or some liek this...

I don't know, let me think on that set of problems over the weekend. Something's gotta be triggering it but I'll be danged if I know what...

Gudare:

The problem mentioned above is in-frequent and is minuscule compared to the precision and speed in which you delivered a the solution.

Cheers - rdmjrb