Pulling Data from Spreadsheet and Using It to Update Fields in Word

[vivamario]

I have an Excel Spreadsheet that employees will fill out in the field. It's very simple. A column for each of the following: the field name, a description, and their input. In Excel, I run a macro to build to array variables, one that stores the field name, and one that stores their input. I then open Word and I want to update the fields with the user input that share the field name.

For example in Excel I have:


ProposalNo
Write the proposal number.
02.20140001.00



Then, in Word, I have a MergeField with the Field Name ProposalNo.
I cannot get this to happen, though.

Here is my code:


'Store Inputs as variables
Dim intCount, intStep As Integer
intCount = Application.WorksheetFunction.Count(Range("A:A"))
Dim FieldNames(1 To 27) As String
Dim Database(1 To 27) As String
ActiveSheet.Range("B2").Select
For intStep = 1 To intCount - 1
ActiveCell.Offset(1, 0).Select
FieldNames(intStep) = ActiveCell.Value
Database(intStep) = ActiveCell.Offset(0, 4).Value
Next
'Opening Word Document
Dim fd As FileDialog, fileName As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
If fd.Show = True Then
fileName = fd.SelectedItems(1)
End If
Set fd = Nothing
Set wordapp = CreateObject("word.Application")
wordapp.documents.Open (fileName)
wordapp.Visible = True
'Update Fields
For intStep = 1 To intCount
ActiveDocument.Fields(FieldNames(intStep)).Result = Database(intStep)
Next
End Sub

[macropod]

Is there a reason you're not simply using a mailmerge or linking the Word document to the Excel file? Seems like a whole lot less work to me.

[vivamario]

Is there a reason you're not simply using a mailmerge or linking the Word document to the Excel file? Seems like a whole lot less work to me.

Yes, because I've never done any type of information transfer between documents before. Could you point me in the right direction of where I should go to learn how to achieve my task most efficiently?

I appreciate the response.

EDIT: I've looked into linking and I don't like the idea of it. It seems it could become a mess with the amount of documents we move around. I'm trying to figure out this mail merge. I can't see to figure out how get mail merge to find the names of my fields.

[macropod]

If you use a template linked to your workbook, once you create a new document and the data are updated you can break the link. That way the data remain static and there are no issues with documents being moved around. In any event, moving the documents isn't an issue provided the path to the Excel workbook remains the same.

Without knowing more about how your workbook is structured, I can't give specific advice on connecting to the fields. However, see: http://word.mvps.org/FAQs/MailMerge/CreateAMailMerge.htm