Buttons in Word to save and get form field data

[Mike Express]

I have a word document that has form fields that I fill out in the process of testing product. Some of those fields are common to a handful of tests such as the equipment used and technician doing the test, etc. I'd like to create buttons in the Word document to get this common data or to save this common data in a separate Word or possibly Excel file. That way I click one button to fill out these fields and not have to fill them out by hand every time I open the document. What I've seen so far on line are examples of saving and retrieving from within the Excel file. But I want my buttons to be in the Word document. Can anyone help me with this? Thanks.

[gmaxey]

Write a macro that fills in the fields then add a button on your Quick Access Tool bar (or ribbon) that calls the macro. You really don't want to add ActiveX buttons to your document itself and if you are using anything newer than Word 2007, ditch the formfields and use content controls instead.

[Mike Express]

Write a macro that fills in the fields then add a button on your Quick Access Tool bar (or ribbon) that calls the macro. You really don't want to add ActiveX buttons to your document itself and if you are using anything newer than Word 2007, ditch the formfields and use content controls instead.

There are a number of technicians who might use the form. Is it the case that I would have to separately configure each techs Word application to have the button in their tool bar? Or would the document itself put a button in the ribbon?

Even with the button in the ribbon, I have yet to learn how to open a file (excel file?) to write or retrieve info and put it in the document. This sounds like it should be easy enough to do, but I've never done it. Any help is appreciated.

[gmayor]

QAT buttons may be saved with the document (the default is the Normal template). Ribbon buttons are a bit more complicated, but more elegant - see Greg's web site for more information. As for the macros, without knowing what you want to read from and write to the worksheet it is more difficult to advise. Normally forms are intended to be read into the worksheet.

Maybe https://www.gmayor.com/ExtractDataFromForms.htm would help?

[gmaxey]

Mike,

Additionally, a descriptive example of what you are trying to do may help. When you click the button, what specifically do you want to happen?

E.g., I click button labeled "Demo" and in the document the formfield bookmarked "Test ID" is filled with "0102-012" the formfield bookmarked "Test Director" is filled with "Samuel L. Smuckatelli" etc.

[gmaxey]

You can pull data from an Excel workbook using ADODB:

Option Explicit
Const strWorkbook As String = "D:\Data Stores\Simple Data Sheet.xlsx"
Const strSheet As String = "Sheet1"
Sub Macro1()
Dim varData
  varData = xlFillArray(strWorkbook, strSheet)
lbl_Exit:
   Exit Sub
End Sub

Private Function xlFillArray(strWorkbook As String, _
                             strRange As String) As Variant
Dim RS As Object
Dim CN As Object
Dim lngRecs As Long

  strRange = strRange & "$]"
  Set CN = CreateObject("ADODB.Connection")
  CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                              "Data Source=" & strWorkbook & ";" & _
                              "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
  Set RS = CreateObject("ADODB.Recordset")
  RS.Open "SELECT * FROM [" & strRange, CN, 2, 1
  With RS
    .MoveLast
    lngRecs = .RecordCount
    .MoveFirst
  End With
  xlFillArray = RS.GetRows(lngRecs)
  If RS.State = 1 Then RS.Close
  Set RS = Nothing
  If CN.State = 1 Then CN.Close
  Set CN = Nothing
lbl_Exit:
  Exit Function
End Function