UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Accessing Content Of Contentcontrols Programmatically, Office 2013    
 
   
MadPiet
post May 14 2019, 01:07 AM
Post#1



Posts: 3,131
Joined: 27-February 09



Well, 2016+

If I have a document built using Bookmarks, it's easy enough to map between those and columns in a table in Access. But what if the document has Content Controls? It looks like Content Controls have a subscript to differentiate them, so how do you know how to map them to a column in a table?

With bookmarks, I could do name each bookmark so it corresponded to say a column in a table, and then mapping was something like

docWord.Bookmarks(rsBkmk.Fields(1).Name)

or
rs.Fields("Salary") = CCur(docWord.Bookmarks("Salary").Text)

and then I could loop through the bookmarks and write their values somewhere.

If Content Controls don't have individual names, how do you map them if you don't know in what order they appear?

It's a theoretical question now, but a job I looked at was trying to process a hundred thousand contracts saved as Word documents. (Or are the poor guys who got the job going to have to read each file manually? That's just plain crazy, in my opinion... hence my question.)

Thanks,

Pieter
Go to the top of the page
 
DanielPineault
post May 14 2019, 07:19 AM
Post#2


UtterAccess VIP
Posts: 6,661
Joined: 30-June 11



Content Controls are a pain programmatically. Hopefully the following might help (just quick and dirty utilities I created a couple years back and aren't properly error handled, ...)

Enumerate the title of each CC in the specified document
CODE
Function EnumerateCCTitles(sDocFile As String)
    Dim oApp                  As Object
    Dim oDoc                  As Object
    Dim oTBx                  As Object
    Dim CC                    As Object
    Dim strTemp               As String
    Dim i                     As Long
    Const msoTextBox = 17

    On Error Resume Next
    Set oApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then    'Word isn't running so start it
        Set oApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    Set oDoc = oApp.Documents.Open(sDocFile)
    oApp.Visible = True
    For Each oTBx In oDoc.Shapes
        If oTBx.Type = msoTextBox Then
            For Each CC In oTBx.TextFrame.TextRange.ContentControls
                Debug.Print CC.Title
            Next
        End If
    Next oTBx
End Function

Do note that you'd think that you could do something like
CODE
For Each CCtrl In oDoc.ContentControls

but it only works if the CCs are part of the 'main story', otherwise it won't list them, that's why I had to create the above!


To extract a value, you can use
CODE
CC.Range.Text

So you could do
CODE
Function EnumerateCCTitles(sDocFile As String)
    Dim oApp                  As Object
    Dim oDoc                  As Object
    Dim oTBx                  As Object
    Dim CC                    As Object
    Dim strTemp               As String
    Dim i                     As Long
    Const msoTextBox = 17

    '     Set oDoc = ActiveDocument
    On Error Resume Next
    Set oApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then    'Word isn't running so start it
        Set oApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    Set oDoc = oApp.Documents.Open(sDocFile)
    oApp.Visible = True
    For Each oTBx In oDoc.Shapes
        If oTBx.Type = msoTextBox Then
            For Each CC In oTBx.TextFrame.TextRange.ContentControls
                Debug.Print CC.Title, CC.Range.Text
            Next
        End If
    Next oTBx
End Function


But things don't end there.

If the CC is a checkbox then instead of .Range.Text you need to use .Checked

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
ADezii
post May 14 2019, 07:24 AM
Post#3



Posts: 2,375
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. To the best of my knowledge, you can uniquely identify Context Controls via an Index into the ContentControls Collection, as in:
    CODE
    ActiveDocument.ContentControls(1)
  2. Each Content Control also has a Unique ID Property.

This post has been edited by ADezii: May 14 2019, 07:25 AM
Go to the top of the page
 
DanielPineault
post May 14 2019, 08:13 AM
Post#4


UtterAccess VIP
Posts: 6,661
Joined: 30-June 11



Just combining ADezii's comments
CODE
Function EnumerateCCTitles(sDocFile As String)
    Dim oApp                  As Object
    Dim oDoc                  As Object
    Dim oTBx                  As Object
    Dim CC                    As Object
    Dim strTemp               As String
    Dim i                     As Long
    Const msoTextBox = 17

    On Error Resume Next
    Set oApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then    'Word isn't running so start it
        Set oApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    Set oDoc = oApp.Documents.Open(sDocFile)
    oApp.Visible = True
    Debug.Print "Index No", "Id", "Title", "Text"
    For Each oTBx In oDoc.Shapes
        If oTBx.Type = msoTextBox Then
            For Each CC In oTBx.TextFrame.TextRange.ContentControls
                i = i + 1
                Debug.Print i, CC.ID, CC.Title, CC.Range.Text
            Next
        End If
    Next oTBx
End Function


So once you identify the CC Id (assuming the documents are standardized!), then you can more easily bind to them for data extraction and simply use (assuming the id = 3772645804 in the example below)
CODE
ActiveDocument.ContentControls(3772645804).Title

Or in the case of the above
CODE
oDoc.ContentControls(3772645804).Title

CODE
oDoc.ContentControls(3772645804).Range.Text

CODE
oDoc.ContentControls(3772645804).Checked

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
ADezii
post May 14 2019, 10:23 AM
Post#5



Posts: 2,375
Joined: 4-February 07
From: USA, Florida, Delray Beach


To further complicate matters, a ContextControl exposes a Type Property which can uniquely identify what Type of Control it is:
Attached File(s)
Attached File  Type.JPG ( 89.33K )Number of downloads: 5
 
Go to the top of the page
 
MadPiet
post May 19 2019, 11:27 PM
Post#6



Posts: 3,131
Joined: 27-February 09



Okay, thanks... I'll give it a bash. I was hoping I could refer to each control by a unique name, but that appears to be impossible as two ContentControls can have the same name. So no referring to them by name and getting anywhere. Drat!

Glad I don't have to read those 100K Word files! YUCK.
Go to the top of the page
 
DanielPineault
post May 20 2019, 06:19 AM
Post#7


UtterAccess VIP
Posts: 6,661
Joined: 30-June 11



It depends on the document. If the documents you are working with are standardized (from one template), and you are confident that there is only one CC with a given name, then you can use it to bind to it, otherwise ...

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
MadPiet
post May 20 2019, 08:15 PM
Post#8



Posts: 3,131
Joined: 27-February 09



Okay, thanks. I'll test out your code and see what happens. Might need it some day.

even if I didn't harvest all the data from the files, I could at least loop through the ContentControls or something and write the names of each to a table... then at least I could figure out programmatically what had to be mapped where. Then it's as simple as (ContentControlName*, MapToColumnName) table. Would take the code a little longer to run, but still infinitely faster than reading all that by hand.
Go to the top of the page
 
MadPiet
post May 21 2019, 07:45 PM
Post#9



Posts: 3,131
Joined: 27-February 09



Okay, I'm confused. I'm trying to see if a document contains ContentControls, so I did this:

CODE
Option Compare Database
Option Explicit

Sub LoopAllFilesInAFolder()

'Loop through all files in a folder
Dim fileName As Variant
Const strPath As String = "C:\Users\NonaLand\Documents\"
fileName = Dir(strPath & "*.docx")

While fileName <> ""
    
    'Insert the actions to be performed on each file
    'This example will print the file name to the immediate window
    Debug.Print strPath & fileName
   ' EnumerateCCTitles fileName

    fileName = Dir
Wend

End Sub

Function EnumerateCCTitles(ByVal sDocFile As String)
    Dim oApp                  As Object
    Dim oDoc                  As Object
    Dim oTBx                  As Object
    Dim CC                    As Object
    Dim strTemp               As String
    Dim i                     As Long
    Const msoTextBox = 17

    On Error Resume Next
    Set oApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then    'Word isn't running so start it
        Set oApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    Set oDoc = oApp.Documents.Open(sDocFile)
    oApp.Visible = False
    Debug.Print oDoc.ContentControls.Count
'    Debug.Print "Index No", "Id", "Title", "Text"
'    For Each oTBx In oDoc.Shapes
'        If oTBx.Type = msoTextBox Then
'            For Each CC In oTBx.TextFrame.TextRange.ContentControls
'                i = i + 1
'                Debug.Print i, CC.ID, CC.Title, CC.Range.Text
'            Next
'        End If
'    Next oTBx
    oDoc.Close
    Set oDoc = Nothing
    oApp.Quit
    Set oApp = Nothing
End Function


The code is weird, I know. I did the For Each file loop bit because I wanted to make sure it looped over all the files. It does.
Then I figured I would just have it return the count of ContentControls in the document. It processes two files and then crashes.

LoopAllFilesInAFolder
C:\Users\NonaLand\Documents\Customer Support Specialist - HealthStream.docx
0
-------------------
C:\Users\NonaLand\Documents\Emdeon Change Healthcare gig.docx

On the second file I get "Error 5174: We couldn't find your file <filename>"
… but it gets listed if I loop the directory...

What am I doing wrong?
Go to the top of the page
 
MadPiet
post May 21 2019, 09:02 PM
Post#10



Posts: 3,131
Joined: 27-February 09



With a little creative copy & paste, I got it to work. I guess I'm now an official Script Kiddie, level 1!!!
This code still has some obvious issues... like LoopAllFilesInAFolder should receive a text value strPath

CODE
Option Compare Database
Option Explicit

Sub LoopAllFilesInAFolder(ByVal strPath As String = "C:\Users\NonaLand\Documents\")

'Loop through all files in a folder
Dim fileName As Variant

fileName = Dir(strPath & "*.docx")

While fileName <> ""
    
    'Insert the actions to be performed on each file
    'This example will print the file name to the immediate window
    Debug.Print strPath & fileName
    EnumerateCCTitles fileName
    Debug.Print "-------------------"
    fileName = Dir
Wend

End Sub

Function EnumerateCCTitles(ByVal sDocFile As String)
On Error GoTo ErrHandler
    Dim oApp                  As Object
    Dim oDoc                  As Object
    Dim oTBx                  As Object
    Dim CC                  As Object
    Dim strTemp              As String
    Dim i                      As Long

    
    ' -- write this to my database.
    Dim rsWordData As DAO.Recordset
    Set rsWordData = CurrentDb.OpenRecordset("WordDataGoesHere", dbOpenTable, dbAppendOnly)

    On Error Resume Next
    Set oApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then    'Word isn't running so start it
        Set oApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    Set oDoc = oApp.Documents.Open(sDocFile)
    oApp.Visible = False
    Debug.Print oDoc.ContentControls.Count
    Debug.Print "Id", "Title", "Text"

    For Each CC In oDoc.ContentControls
        Debug.Print CC.ID, CC.Title, CC.Range.Text
        With rsWordData
            .AddNew
            .Fields("ControlID") = CC.ID
            .Fields("ControlTitle") = CC.Title
            .Fields("ControlText") = CC.Range.Text
            .Fields("DocumentPath") = sDocFile
            .Update
        End With
    Next CC
    
    '-- clean up Access variables
    rsWordData.Close
    Set rsWordData = Nothing
    
    oDoc.Close
    Set oDoc = Nothing
    oApp.Quit
    Set oApp = Nothing
Exit Function


Granted, it's not pretty, but it does work. =) Thanks everybody (especially Daniel)!
This post has been edited by MadPiet: May 21 2019, 09:08 PM
Go to the top of the page
 
MadPiet
post May 28 2019, 11:32 AM
Post#11



Posts: 3,131
Joined: 27-February 09



just wondering...

Has anybody done something like this using C# or VB.Net, so I could call it from Integration Services?
Go to the top of the page
 
MadPiet
post Jun 3 2019, 03:25 AM
Post#12



Posts: 3,131
Joined: 27-February 09



Okay, here's what I have and it's working... Next thing is to fix the late biding...


Dim oDoc As Object

Function EnumerateCCTitles(sDocFile As String)
' Dim oApp As Object
' Dim oDoc As Object
' Dim oTBx As Object
Dim CC As Object
' Dim strTemp As String
Dim i As Long
Dim rsCCs As DAO.Recordset

On Error Resume Next

If oApp Is Nothing Then
Set oApp = GetObject(, "Word.Application")
End If

If Err.Number <> 0 Then 'Word isn't running so start it
Set oApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set rsCCs = CurrentDb.OpenRecordset("ControlValues", dbOpenTable, dbAppendOnly)
Set oDoc = oApp.Documents.Open(sDocFile)
oApp.Visible = True

If oDoc.ContentControls.Count > 0 Then
Debug.Print
Debug.Print oDoc.Name
Debug.Print "------------------------------------"
End If

For Each CC In oDoc.ContentControls
i = i + 1
Debug.Print i, CC.ID, CC.Title, CC.Range.Text
With rsCCs
.AddNew
.Fields("FileName") = oDoc.Name
.Fields("ContentControlID") = CC.ID
.Fields("ContentControlTitle") = CC.Title
.Fields("ContentControlText") = CC.Range.Text
.Update
End With
Next CC
' Debug.Print

rsCCs.Close
Set rsCCs = Nothing
End Function

Public Sub LoopDirectory(ByVal strPath As String)

Dim strFile As String
Dim i As Integer

If Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If
strPath = strPath & "*.docx"

strFile = Dir(strPath)

Debug.Print "Index No", "Id", "Title", "Text"

Do While strFile <> "" 'And i < 20
EnumerateCCTitles strFile
strFile = Dir
i = i + 1
'-- the file has been read... so close it!
oDoc.Close False
Loop

Set oDoc = Nothing
oApp.Quit
Set oApp = Nothing
End Sub[/code]

Guess I'll fix the final minor problems when it's daylight!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th June 2019 - 04:08 PM