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
> VBA For Word Content Controls, Access 2007    
 
   
Paul_Churchill
post Jan 3 2013, 06:55 AM
Post#1


UtterAccess VIP
Posts: 1,406
Joined: 27-February 07
From: Barton-under-Needwood, Staffordshire, England


I've got code that will insert data from Access into a Word file using Bookmarks and that works fine but I'm just failing miserably to get the VBA syntax to write fron Access into Word using the newer 'Content Controls'
Here is the modified code but it just runs without error and fails to find and overwrite the content control "Field1"
On the one hand I could just stick with the legacy stuff but content control would seem to be the way to go if they were'n such a riddle to understand.
CODE
Dim appWord As Word.Application
Dim doc As Word.Document
Dim DB As Database
Dim Rst As Recordset
On Error Resume Next
Set appWord = GetObject(, "Word.application")
If Err = 429 Then
    Set appWord = New Word.Application
    Err = 0
End If
With appWord
    Set doc = .Documents.Open("C:\Word2Access\Doc2.docx")
    Set DB = CurrentDb
    Set Rst = DB.OpenRecordset("tblcontacts")
    Rst.MoveFirst
    Debug.Print "rst![FirstName]: " & Rst![FirstName]
    doc.ContentControls("Field1").Range.Text = "Hello World!"
    doc.Visible = True
    doc.Activate
End With
Rst.Close
Set Rst = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandler:
MsgBox Err & Err.Description
End Sub

Regards
Paul Churchill
Go to the top of the page
 
Doug Steele
post Jan 3 2013, 08:42 AM
Post#2


UtterAccess VIP
Posts: 22,149
Joined: 8-January 07
From: St. Catharines, ON (Canada)


I no longer have Word 2007, but according to the Word 2010 Help file, the Item method of the ContentControls collection only accepts an index number that specifies the ordinal position of the content control to return. In other words, it sounds that, unlike collections in Access, you can't provide a name.
Go to the top of the page
 
Paul_Churchill
post Jan 3 2013, 08:48 AM
Post#3


UtterAccess VIP
Posts: 1,406
Joined: 27-February 07
From: Barton-under-Needwood, Staffordshire, England


Doug,
Thank you for your reply & a happy New Year to you !
I was just typing in the solution when the notification of your reply came in and confirmed what I had just painfully learnt i.e.
SOLVED !
the offending line was
doc.ContentControls("Field1").Range.Text = "Hello World!"
it should have read :
doc.ContentControls(1).Range.Text = "Hello World!"
Notes:
1) The first Content Control was titled "Field1"
2) The Access VBA compiler was quite happy that I'd put in "Field1" -= no error on compile or on run
3) I only tracked this down by opening VBA in WORD and typing into the Immediate window : ? Activedocument.ContentControls("Field1").Range.Text
and it promptly responded "Runtime Error 9541 - The requested member of the collection does not exist"
Conclusion: the Access VBA compiler doesn't have enough knowledge to fully check the VBA syntax - so check it out in word first !
Oh and the Content Controls start at number 1 ( not Zero as in the columns of a comboBox)
Go to the top of the page
 
Doug Steele
post Jan 3 2013, 08:57 AM
Post#4


UtterAccess VIP
Posts: 22,149
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Glad you got it working!
FWIW, my first step when automating Word, Excel and PowerPoint is to record a macro and use that as my starting point. Yes, there's usually a lot of work required to switch (and, to be honest, the generated VBA often isn't very good!), but I find it definitely helps me with the syntax.
Go to the top of the page
 
MadPiet
post May 13 2019, 11:46 PM
Post#5



Posts: 3,043
Joined: 27-February 09



Apologies for adding on to a reaaaally old post, but I have a dumb question that's right along these lines... If you had a ton of Word documents with Word Content Controls in them, is there any way to map them to tables in a database in any sensible way, since the controls seem to be just an array of controls with a subscript? In the "good old days" of Bookmarks, they at least had a name, so I could do something really optimistic like:

Loop through bookmarks collection,
Map to fieldname of same name in my database table,
Assign values...

rs.fields(n).Value = doc.Bookmarks(rs.fields(n).Name).Text <-- so basically use the Fields collection to pull the corresponding bookmark value

rs.Update

then I could do something like close the Word file, open the next, and harvest all those values.

Is there a way to do that with Content Controls? Or are those just an array or something with no individual names (like bookmarks had)?
(I'm using 2016 if it matters... I could upgrade if I were given a good reason to!)

Thanks! (and sorry for bringing up an old thread - it was just really close to what I'm trying to do!)
Go to the top of the page
 
sxschech
post May 15 2019, 02:39 PM
Post#6



Posts: 8
Joined: 2-October 18



I've done some work with Access and Word (content controls, table cells, sentences) to verify data and transfer data between the two and has made life easier as now the program checks for errors/issues rather than me reading each one, I still do a glance at the end, but this catches things I may have missed looking at similar files over and over. As for the content controls, the ones I worked with were check boxes, so was seeing whether they were checked or unchecked and whether more than one or none were checked (indicating a problem). From the docx I was provided, the content controls seem to be numbered beginning with 1 and numbered from left to right top to bottom.
Examples:

Content Control - First checkbox
CODE
If WordApp.activedocument.contentcontrols(1).Checked = True Then


Word Table -Data in Row 7 col 2
CODE
stData=WordDoc.Tables(1).Cell(7, 2).Range.Text


Go to the top of the page
 
DanielPineault
post May 15 2019, 03:03 PM
Post#7


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



You may like to review https://www.UtterAccess.com/forum/index.php...2053765&hl= as it may offer a few ideas with regards to CCs.

--------------------
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
 
sxschech
post May 15 2019, 03:58 PM
Post#8



Posts: 8
Joined: 2-October 18



Thanks for the link, tried to run it and returned nothing. Is that because the code is looking for a textbox
QUOTE
oTBx.Type = msoTextBox
and the document I have uses tables and checkboxes?
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st May 2019 - 03:56 AM