vivamario
05-12-2014, 03:44 PM
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
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