Consulting

Results 1 to 6 of 6

Thread: Buttons in Word to save and get form field data

  1. #1

    Buttons in Word to save and get form field data

    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.

  2. #2
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,096
    Location
    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.
    Greg

    Visit my website: http://gregmaxey.com

  3. #3
    Quote Originally Posted by gmaxey View Post
    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.

  4. #4
    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?
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,096
    Location
    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.
    Greg

    Visit my website: http://gregmaxey.com

  6. #6
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,096
    Location
    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
    Greg

    Visit my website: http://gregmaxey.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •