Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 30 of 30

Thread: Extracting Word form Data and exporting to Excel spreadsheet

  1. #21
    Quote Originally Posted by macropod View Post
    Hi candameron,

    The following Excel macro will pull the data from all content controls in all Word files in a specified folder into the active worksheet. To run it, simply press Alt-F8 and choose the 'GetFormData' macro. You'll be asked to navigate to the source folder. Once you've done that, the macro will populate the worksheet. If you want, you could create a keyboard shortcut or a button to trigger the macro.

    If you initially put just one document containing meaningful data in all of the content controls into the 'source' folder, the macro will go through through the content controls and output the data into the worksheet. you should then be able to marry-up the output with whatever column headings you'll want in the workbook.
    Sub GetFormData()
    'Note: this code requires a reference to the Word object model
    'To do this, go to Tools|References in the VBE, then scroll down to the Microsoft Word entry and check it.
    Application.ScreenUpdating = False
    Dim wdApp As New Word.Application, wdDoc As Word.Document, CCtrl As Word.ContentControl
    Dim strFolder As String, strFile As String, WkSht As Worksheet, i As Long, j As Long
    strFolder = GetFolder
    If strFolder = "" Then Exit Sub
    Set WkSht = ActiveSheet
    i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
    strFile = Dir(strFolder & "\*.doc", vbNormal)
    While strFile <> ""
      i = i + 1
      Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
      With wdDoc
        j = 0
        For Each CCtrl In .ContentControls
          With CCtrl
            Select Case .Type
              Case Is = wdContentControlCheckBox
               j = j + 1
               WkSht.Cells(i, j).Value = .Checked
              Case wdContentControlDate, wdContentControlDropdownList, wdContentControlRichText, wdContentControlText
               j = j + 1
               WkSht.Cells(i, j).Value = .Range.Text
              Case Else
            End Select
          End With
        Next
        .Close SaveChanges:=False
      End With
      strFile = Dir()
    Wend
    wdApp.Quit
    Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
    Application.ScreenUpdating = True
    End Sub
    
    
    Function GetFolder() As String
    Dim oFolder As Object
    GetFolder = ""
    Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
    If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
    Set oFolder = Nothing
    End Function
    Hi,

    i see that this code go run through each of the content control in the form and output them one by one into the excel. Is there any way such that i can select a few of the content controls in the form and output them into specific columns in the excel?
    Thanks for the help!
    Last edited by macropod; 07-11-2022 at 02:48 PM.

  2. #22
    VBAX Newbie
    Joined
    Sep 2015
    Posts
    1
    Location
    This looks like a really useful macro for me. However, I have used activeX controls (Check Boxes, Option Buttons, and Text Boxes) in my word form instead of content controls or form fields. Does anyone know how to change the macro to be able to work with these activeX controls?
    Last edited by macropod; 07-11-2022 at 02:49 PM.

  3. #23

    thanks

    Your programme helps a lots! thanks

  4. #24
    Banned VBAX Newbie
    Joined
    Sep 2016
    Posts
    1
    Location

    Is there a way of doing this with subfolders?

    Hi,

    Sorry for coming back to an old thread, but I was wondering if there is a way this can be altered so you can select a higher folder and it looks in all subfolders? Thanks
    Last edited by macropod; 07-11-2022 at 02:49 PM.

  5. #25
    VBAX Newbie
    Joined
    Oct 2016
    Posts
    1
    Location
    Thanks for the discussion

  6. #26
    VBAX Newbie
    Joined
    Jun 2017
    Posts
    2
    Location
    HI,
    can you write me how I can modify this code to import checkbox (word content control) from word to excel.
    I am a beginner person in vba and I can’t find a solution to my problem.

  7. #27
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    So what do you want the output to be for checkboxes?
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  8. #28
    VBAX Regular
    Joined
    Jun 2017
    Posts
    9
    Location
    Hey, so i got everything working fine, was wandering how i let it pull from a set folder location instead of having to navigating to it each time.

    Thanks!

  9. #29
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    You could replace:
    strFolder = GetFolder
    with:
    strFolder = "C:\MyPath\MyFolder"
    where 'C:\MyPath\MyFolder' is the drive, path & folder name.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  10. #30
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Thread closed. Any further discussion on issues related to the discussion in this thread should be started in a new thread - referencing this one if appropriate.
    Last edited by macropod; 04-05-2019 at 08:31 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

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