Dynamic link from Word to Excel...

[Matteo85]

I have a Word template that is used by various contributors to submit articles. At the bottom of the template, I have document fields that calculate the total number of pages and words (as separate document fields). I need to know how to dynamically link these document field results (the calculated result, not the link text) into various cells in an Excel file to dynamically track the total number of pages for each edition through the draft, revision, editing and approval phases of publishing. I can easily insert an Excel cell into Word and format it to look pretty, etc., but I can't figure out how to do the same in reverse (and I don't need it to look pretty, I just need the dynamically-generated numbers.

The Excel sheet has the article serial number, the section# and title, the chapter number and title (which can change as content is moved around for readibility, etc.), the chapter name and then the statistical information I'm looking for - number of pages, number of paragraphs, number of words, etc. I figure that if I can get the number of pages to work, the rest will be just doing the same thing for each data point.

Any ideas?

 

[NdNoviceHlp]

Hi Matteo85. I'm not sure that I entirely understand your needs but maybe this will get you started. This is XL VBA code that outputs Word field results. The document is selected and Field results are provided via msgbox. The different field types can be altered within the "Select Case" code. The msgbox results can be outputted to your worksheet. HTH. Dave

Private Sub Test()
'output Word field values
Dim wrdDoc As Object, AllFields As Object, wrdApp As Object
Dim filePath As String, WdFlag As Boolean
Dim ObjRange As Variant, strWord As String, ObjField As Object
'select document
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select a Word Document or Text File with File Paths"
.Filters.Add "Word Documents", "*.docx, *.doc"
If .Show = -1 Then
    filePath = .SelectedItems(1)
Else
    Exit Sub
End If
End With

'start Word
On Error Resume Next
Set wrdApp = GetObject(, "Word.Application")
On Error GoTo 0
If wrdApp Is Nothing Then
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True 'False
    WdFlag = True
End If

'open doc
Set wrdDoc = wrdApp.Documents.Open(filePath)
'update fields
Set AllFields = wrdDoc.Fields
AllFields.Update
'field type enumeration
'https://learn.microsoft.com/en-us/previous-versions/
                  'office/developer/office-2007/bb213727(v=office.12)?redirectedfrom=MSDN
For Each ObjField In AllFields
   Set ObjRange = ObjField.Result
   strWord = ObjRange.Text
    Select Case ObjField.Type
    Case 33 'page field
    MsgBox "Page " & strWord
    Case 65 'section field
    MsgBox "Section " & strWord
    Case 27 'number of words
    MsgBox "Number of Words " & strWord
    Case 31 'number of words
    MsgBox "Date " & strWord
    'etc.
    Case Else
    MsgBox "Not enumerated " & ObjField.Type
    End Select
Next ObjField
'clean up
Set AllFields = Nothing
wrdDoc.Close SaveChanges:=0 'no save
Set wrdDoc = Nothing
'quit Word if initiated
If WdFlag Then
wrdApp.Quit
Set wrdApp = Nothing
Else
Set wrdApp = Nothing
End If
End Sub

 

[Macropod]

Assuming you want just the document totals, you can use code like:

Sub Demo()
'Note: This code requires a VBA reference to Word, set via Tools|References in the VBE
Dim StrDocNm As String, lPages As Long, lParas As Long, lWords As Long
'select document
With Application.FileDialog(msoFileDialogFilePicker)
  .Title = "Select a Word Document or Text File with File Paths"
  .Filters.Add "Word Documents", "*.docx, *.doc"
  If .Show = -1 Then
      StrDocNm = .SelectedItems(1)
  Else
      Exit Sub
  End If
End With
Dim wdApp As New Word.Application, wdDoc As Word.Document
With wdApp
  .Visible = False
  'open doc
  Set wdDoc = .Documents.Open(Filename:=StrDocNm, ReadOnly:=True, AddToRecentFiles:=False)
  With wdDoc
    lPages = .ComputeStatistics(wdStatisticPages)
    lParas = .ComputeStatistics(wdStatisticParagraphs)
    lWords = .ComputeStatistics(wdStatisticWords)
    .Close SaveChanges:=False
  End With
End With
Set wdDoc = Nothing: Set wdApp = Nothing
MsgBox "Document: " & Split(StrDocNm, "\")(UBound(Split(StrDocNm, "\"))) & vbCr & _
  "Contains:" & vbCr & _
    vbTab & lPages & vbTab & " Pages" & vbCr & _
    vbTab & lParas & vbTab & " Paras" & vbCr & _
    vbTab & lWords & vbTab & " Words"
End Sub

 

[Macropod]

  End With
End With
Set wdDoc = Nothing: Set wdApp = Nothing

should be:

  End With
  .Quit
End With
Set wdDoc = Nothing: Set wdApp = Nothing

 

[NdNoviceHlp]

Hi Macropod. It seems like this part of the ask was important... "At the bottom of the template, I have document fields that calculate the total number of pages and words". So maybe whole document values are suffice. My testing was that document fields can be placed anywhere in the document and therefore produce different results based on where in the document they are placed. There's also quite a few field values that are available that aren't available with the document statistics such as Section information. I thought the learning task was to output document field values.... I guess we will wait to maybe find out if Matteo85 replies. I converted your code to late binding (no need for Word reference) and I'm hoping it will be okay to post the adjusted code for others users to trial. Dave
Macropod's adjusted code....

Sub test()
Dim wrdDoc As Object, wrdApp As Object
Dim filePath As String
Dim lPages As Long, lParas As Long, lWords As Long
'select document
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select a Word Document or Text File with File Paths"
.Filters.Add "Word Documents", "*.docx, *.doc"
If .Show = -1 Then
    filePath = .SelectedItems(1)
Else
    Exit Sub
End If
End With

'start Word
On Error Resume Next
Set wrdApp = GetObject(, "Word.Application")
On Error GoTo 0
If wrdApp Is Nothing Then
    Set wrdApp = CreateObject("Word.Application")
End If
wrdApp.Visible = False
'open doc
Set wrdDoc = wrdApp.Documents.Open(filePath)
  With wrdDoc
    lPages = .ComputeStatistics(2) 'wdStatisticPages
    lParas = .ComputeStatistics(4) 'wdStatisticParagraphs
    lWords = .ComputeStatistics(0) 'wdStatisticWords
    .Close SaveChanges:=False
  End With
wrdApp.Quit
Set wrdDoc = Nothing: Set wrdApp = Nothing
MsgBox "Document: " & Split(filePath, "\")(UBound(Split(filePath, "\"))) & vbCr & _
  "Contains:" & vbCr & _
    vbTab & lPages & vbTab & " Pages" & vbCr & _
    vbTab & lParas & vbTab & " Paras" & vbCr & _
    vbTab & lWords & vbTab & " Words"
End Sub

ps. I accidently left the Word application visible in the code I provided. It should be...

wrdApp.Visible = False

instead of the posted...

wrdApp.Visible = True 'False

 

[Macropod]

My testing was that document fields can be placed anywhere in the document and therefore produce different results based on where in the document they are placed. There's also quite a few field values that are available that aren't available with the document statistics such as Section information.

The fields that calculate the number of words, pages, etc. and, except for the SECTION and SECTIONPAGES fields, have no regard to where they're placed. All the rest return is just the document totals. The NUMWORDS field even counts itself...

I'm not sure what you mean by:

There's also quite a few field values that are available that aren't available with the document statistics such as Section information.

The .ComputeStatistics method can easily return the values for a given Section, Heading range, page range, etc., none of which Word fields can do except for the SECTIONPAGES field that returns the # of pages in its own Section and the SECTION field, which returns its own Section #.

 

[NdNoviceHlp]

Thanks for the learning Paul. The MS documentation that I found re. .ComputeStatistics data types wasn't that informative.

WdStatistic enumeration (Word)

Office VBA reference topic

learn.microsoft.com

When I was messing around with testing, along with the Section field, the Page field also returned the page it was inserted on. The number of words field didn't seem to matter on it's placement. Again, I appreciate you sharing your expertise. Thanks. Dave

 

[Macropod]

Yes, the PAGE field returns its own page #. I should have mentioned that.

Here's a simple demo of .ComputeStatistics being used to get the word count for a specified paragraph:

Sub Test()
MsgBox ActiveDocument.Paragraphs.First.Range.ComputeStatistics(wdStatisticWords)
End Sub

 

[Matteo85]

I will have to take a couple of days to implement the code suggestions given above. Thanks to everyone who contributed; I'll follow up on each implementation as I test them.