Gathering a total from multiple documents?

[Angyl]

Let's say I have document A.doc, document B.doc and document C.doc and they
are identical Word documents with named form fields that perform various
calculations.

At the bottom of each document there is a field that runs a calculation
called "total"

Now I create a new document called "Grand Total.doc" and in it I put a
single form field with a calculation.

I want it to pull the "total" from document A, document B, and document C
and add them all up in this field.

Is that possible?

 

[Doug Robbins - Word MVP]

Use some code to open each document and get the .Result of the "total"
formfield and add the values together and insert it into the other document.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Angyl]

So use the code in VB to do the math and insert.

Great!

Two questions: 1. I know how to open documents and grab info from the
fields in it. I don't know anything about getting the .Result and adding it
up, though.

2. The problem I have with that method is that I will have about 97
different documents so opening them to grab info out of the fields would be
extremely intensive if not possible. Is there no way to set the calculation
of a bookmarked field to look into named documents?

 

[Doug Robbins - Word MVP]

Put all of the documents into a folder by themselves and then run the
following macro when the document into which you want to have the grand
total appear is the active document. It assumes that each of the 97
documents contains a formfield to which the bookmark named "Total" has been
assigned and that you have a formfield with the bookmark name "GrandTotal"
in the document in which you want the grand total to be inserted.

Dim MyPath As String
Dim MyName As String
Dim Source As Document
Dim Target As Document
Dim GrandTotal As Double

Set Target = ActiveDocument
'let user select a path
With Dialogs(wdDialogCopyFile)
If .Display() <> -1 Then Exit Sub
MyPath = .Directory
End With

'strip quotation marks from path

If Len(MyPath) = 0 Then Exit Sub

If Asc(MyPath) = 34 Then
MyPath = Mid$(MyPath, 2, Len(MyPath) - 2)
End If

'Initialize the GrandTotal variable to 0
GrandTotal = 0

'get files from the selected path
' and extract the .Result of the "total" formfield and add it to the
GrandTotal variable

MyName = Dir$(MyPath & "*.*")

Do While MyName <> ""
Set Source = Documents.Open(MyName)
GrandTotal = GrandTotal + Val(Source.FormFields("Total").Result)
Source.Close wdDoNotSaveChanges
MyName = Dir
Loop

'Insert the Grand Total into the formfield "GrandTotal"
Target.FormFields("GrandTotal").Result = GrandTotal

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Angyl]

THANKS, DOUG!

Put all of the documents into a folder by themselves and then run the
following macro when the document into which you want to have the grand
total appear is the active document. It assumes that each of the 97
documents contains a formfield to which the bookmark named "Total" has been
assigned and that you have a formfield with the bookmark name "GrandTotal"
in the document in which you want the grand total to be inserted.

Dim MyPath As String
Dim MyName As String
Dim Source As Document
Dim Target As Document
Dim GrandTotal As Double

Set Target = ActiveDocument
'let user select a path
With Dialogs(wdDialogCopyFile)
If .Display() <> -1 Then Exit Sub
MyPath = .Directory
End With

'strip quotation marks from path

If Len(MyPath) = 0 Then Exit Sub

If Asc(MyPath) = 34 Then
MyPath = Mid$(MyPath, 2, Len(MyPath) - 2)
End If

'Initialize the GrandTotal variable to 0
GrandTotal = 0

'get files from the selected path
' and extract the .Result of the "total" formfield and add it to the
GrandTotal variable

MyName = Dir$(MyPath & "*.*")

Do While MyName <> ""
Set Source = Documents.Open(MyName)
GrandTotal = GrandTotal + Val(Source.FormFields("Total").Result)
Source.Close wdDoNotSaveChanges
MyName = Dir
Loop

'Insert the Grand Total into the formfield "GrandTotal"
Target.FormFields("GrandTotal").Result = GrandTotal

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP