Karen,
I have been there. It is beyond me why none of the fine minds at Microsoft
never seemed to think that this was a question worth addressing (directly),
however let me share with you the fix that I came up with. It is a little
complex but the solution is reasonable elegant.
The basis of my approach is to use Custom Document Properties in MS Word (I
am actually using 2007) these can be created by the file/properties/custom
properties (if you have any questions on this refer to the Help file or just
reply and I can assist).
The key to my solution is to use EXACTLY_THE_SAME name for the Word Custom
Document Property (note that using Underscores has meaning as I will discuss
below) as Cell Names that I assign in an Excel Workbook (like the input form
that you mean to use below).
So for example if I want the user to enter the Client Name into a cell in
the excel from cell B2 then I would name that cell CLIENT_NAME and add a
custom document property to the MS Word document named CLIENT_NAME. Of
course this alone does nothing (again I do not see why Microsoft never took
the natural step to do the following).
I wrote a couple of macros (below) with an assumed activedocument (Word)
that was an Enquiry Data Sheet "Enquiry_DS". Also there is an assumed
activeworkbook that is a proposal that I want to be filled in from the
Enquiry Data Sheet. The file names and locations don't matter but the correct
Word Doc and Excel Workbook need to be active.
The first "Verifiy_Enquiry_DS()" subroutine, simply requests the user to
confirm the workbook that is active (I likely should have the same done for
the Word Document but that is for another day).
The second subroutine "query_EnqDS()" does nothing but verify that each
custom property in the active Word Docuemnt has a matching Named Cell in the
active Excel Workbook. If this condition is not met, it falls to the user to
add the named cell to the workbook with some data to read. Usually you will
be producing template documents that will already contain the synconized
names so your users will never see any need to respond to these messages.
the final subroutine "read_enqDS_write_to_doc" simply looks at the list of
custom document properties "docCustomProperties" (in VBA this is refered to
as a collection of docCustomProperty Objects) each docCustomProperty in this
list includes a Name that as discussed above matches a named cell in the
active excel workbook. I recover this name from the custom document
property, save it in a variable named "dummy". I then use the following
statment
xlCellValue = xlNames(dummy).RefersToRange
to use this "dummy" variable to reference the specific Named Cell
(CLIENT_NAME) and use the "RefersToRange" to read the value (for me this is
always a string) found in the Excel Worksheet. I record this value in
another variable named "xlCellValue". I then turn around and write the value
into the specific custom document property (CLIENT_NAME).
docCustomProperty.Value = xlCellValue
OK nothing happens when the above steps are followed, (except of course the
custom document properties now agree with the associated Named Excel Cell
Values. Usually I use the CNTL+A (Select All) command followed with the F9
function key to update all fields in the active document (headers and footers
require specific attention).
If you are not familiar with VBA programming you will need to ask some
follow-up questions, (for example you are going to need to set up the
"References" in VBA prior to running this macro, but that is easy and I would
be happy to assist.
Best regards,
Sub Verifiy_Enquiry_DS()
'
' Used to address Excel File "Enquiry Data Sheet" and query user
confirmation of File
'
'Dim xlApp As Excel
Dim path As String
Dim EnquiryDS_Path As String
Dim EnquiryDS_Name As String
Dim ans As Integer
Set wdDoc = Word.ActiveDocument
Set xlApp = GetObject(, "Excel.Application")
'xlApp.Visible = True
EnquiryDS_Path = xlApp.ActiveWorkbook.FullName
ans = MsgBox("Read Data From " & EnquiryDS_Path, vbOKCancel)
Select Case ans
Case vbCancel
GoTo endsub
Case vbOK
Set xlWkBook = xlApp.ActiveWorkbook
Set xlEnqDS = xlWkBook.ActiveSheet
EnquiryDS_Name = xlEnqDS.Name
'ensure that all custom document properties are found in the active workbook
query_EnqDS
'if this condition is met then read the values found in the active workbook
into
'the custom document properties
read_enqDS_write_to_doc
End Select
endsub:
End Sub
Sub query_EnqDS()
'
' Used to check that all Custom Document Properties used in document are
associated with
'named fields in Enquiry Data Sheet.
Dim EnquiryDS_Name As String
Dim xlNameIndx As Integer
Dim EnquiryDS_Path As String
Dim xlNames As Excel.Names
Dim docCustomProperties As DocumentProperties
Dim docCustomProperty As DocumentProperty
Set xlApp = GetObject(, "Excel.Application")
Set xlWkBook = xlApp.ActiveWorkbook
Set xlEnqDS = xlWkBook.ActiveSheet
Set xlNames = xlWkBook.Names
Set docCustomProperties = ActiveDocument.CustomDocumentProperties
EnquiryDS_Name = xlEnqDS.Name
EnquiryDS_Path = xlApp.ActiveWorkbook.FullName
i = 0
'below the collection of custom document properties are checked against
'the list of named cells in the active workbook, if the custom document
property
'is not found in the active workbook names collection this error handler is
initiated
'NOTE: I have had to use an index to trap an error generated when the end of
the list is reached
For Each docCustomProperty In docCustomProperties
On Error GoTo errHandler
dummy = docCustomProperty.Name
'See NOTE on index and errHandler below
i = i + 1
xlNameIndx = xlNames(dummy).Index
Next docCustomProperty
errHandler:
'it is not clear to me why an error is generated when the end of the
collection is reached
'the test to capture this error on next line will be removed when a fix is
found
If i = docCustomProperties.Count Then
GoTo endsub
'this is the message box to tell the user that a property is included in the
document but
'has not been found in the Enquiry Data Sheet
Else: MsgBox (dummy & " was not found in file " & EnquiryDS_Path)
End
End If
endsub:
End Sub
Sub read_enqDS_write_to_doc()
'
' Used to check that all Custom Document Properties used in document are
associated with
'named fields in Enquiry Data Sheet.
Dim EnquiryDS_Name As String
Dim xlCellValue As Variant
Dim EnquiryDS_Path As String
Dim xlNames As Excel.Names
Dim docCustomProperties As DocumentProperties
Dim docCustomProperty As DocumentProperty
Set xlApp = GetObject(, "Excel.Application")
Set xlWkBook = xlApp.ActiveWorkbook
Set xlEnqDS = xlWkBook.ActiveSheet
Set xlNames = xlWkBook.Names
Set docCustomProperties = ActiveDocument.CustomDocumentProperties
EnquiryDS_Name = xlEnqDS.Name
EnquiryDS_Path = xlApp.ActiveWorkbook.FullName
i = 0
'below the collection of custom document properties are checked against
'the list of named cells in the active workbook, if the custom document
property
'is not found in the active workbook names collection this error handler is
initiated
'NOTE: I have had to use an index to trap an error generated when the end of
the list is reached
For Each docCustomProperty In docCustomProperties
On Error GoTo errHandler
dummy = docCustomProperty.Name
'See NOTE on index and errHandler below
i = i + 1
'enters the value of named cell into variable xlCellValue
xlCellValue = xlNames(dummy).RefersToRange
'writes the value of variable xlCellValue to Custom Document Property
docCustomProperty.Value = xlCellValue
Next docCustomProperty
errHandler:
'it is not clear to me why an error is generated when the end of the
collection is reached
'the test to capture this error on next line will be removed when a fix is
found
If i = docCustomProperties.Count Then
GoTo endsub
'this is the message box to tell the user that a property is included in the
document but
'has not been found in the Enquiry Data Sheet
Else: MsgBox (dummy & " was not found in file " & EnquiryDS_Path)
End
End If
endsub:
End Sub
