Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 31

Thread: Extract data from Word Form responses to Excel Spreadsheet

  1. #1
    VBAX Newbie
    Joined
    Aug 2006
    Posts
    4
    Location

    Extract data from Word Form responses to Excel Spreadsheet

    Disclaimer: My only prior experience with the VBA Editor has been minor editing of Word Macros after they have been recorded. I know I'm in over my head and badly in need help, and I don't think I can do what we need done without using VBA -- of course, please advise me if there is an easier way.

    Our group is beginning to plan for a 30th high school reunion and the very first thing we must do is to update information about each of our 500+ classmates so we can effectively communicate with them.

    he school gave us an Excel spreadsheet containing over 30 fields for information about our class members (not all of which contain data). Some of the alumni information has been updated, most has not, and we cannot be sure of the accuracy of anything other than each person's name at graduation. Of course, many female classmates' last names have changed, as has the address, phone #, etc. for almost everyone. So we need to verify and update the informaton for everyone.

    We have e-mail addresses for a number of these people, each of whom we hope will have e-mail addresses for others. What we hope to do is send a form that can be (1) completed by the recipient and e-mailed back to us and (2) forwarded by that recipient to his/her e-mail contacts to be completed, returned to us, and again forwarded along to others. We wish to circulate a "form" so that, when returned, the data can more easily be extracted and reliably placed into a spreadsheet.

    While it would be nice to be able to have something that would cause the imported data to automatically update the old data on the existing spreadsheet, we would be happy just to be able to pull the data from the forms and create a new spreadsheet with the correct fields.

    It seemed to me that the most ubiquitous (other than pure text) file format is Word ( I don't think I can expect nearly as many people to have or use Excel, for example). So, I have created a Word Template to serve as a data collection form that will only allow the user to put data into specific fields. This is something we can e-mail. What I cannot figure out is:

    1. How to automate getting to that data (either file by file, or from a group all at once) and

    2. How to move that data into an Excel spreadsheet.

    I can not imagine that someone has not already developed a way to accomplish this, but I do not even know where to go look. Any help would be greatly appreciated.

    I have attached the form I created, in case that helps.

    - Paul Dalton

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Paul,
    Welcome to VBAX
    This can certainly be done, but your attachment is missing.
    Regards
    MD
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Newbie
    Joined
    Aug 2006
    Posts
    4
    Location

    Extract data from Word Form responses to Excel Spreadsheet

    >> your attachment is missing

    Hmmm. I thought I had dome the steps correctly.

    I've tried again &, this time, there is data in the form.

    - Paul

  4. #4
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    A bit basic but something like this. (Save it in the same folder as your documents)
    Last edited by mdmackillop; 08-20-2006 at 05:19 PM. Reason: Browse function added
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    Word allows you to save form data in a comma-delimited text file, so you could save all returned documents in a single folder, loop through them and save the form data.

    [vba]
    ActiveDocument.SaveAs FileName:="Tom Johnson.txt", FileFormat:= _
    wdFormatText, SaveFormsData:=True
    [/vba]

  6. #6
    VBAX Newbie
    Joined
    Aug 2006
    Posts
    4
    Location
    Thanks! It took me a bit to figure out what to do with it, so I ended up entering the filename into the blank several different ways -- what worked was putting in the filename without an extension. I assume this was as intended.

    A couple of problems that occurred, however, are that the data populates a column, instead of a row, AND it seems to always want to populate the SAME row, so that processsing a second file overwrites the data from the first file.

    Can you tell me how to edit it to:

    1. cause it to populate a row (instead of a column); and

    2. have it index to the next empty row before inserting the data?

    Also, and this may be asking too much, but it also would be helpful if, instead of prsenting a blank into which teh filename must be typed correctly (that requires me to go look at the file to assure that I get the spelling right), the program would open an Explorer window listing all the files in that directory and then allow the user to simply click on the desired file and the program would then process the selected file? Is that do-able?

    Thanks for your help.

    - Paul

  7. #7
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Paul,
    The Browse function had been added to my edited version above. This addresses the Rows issues as well.
    Regards
    MD
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  8. #8
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Another slick solution Malcolm.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  9. #9
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Thanks Steve.
    Greatly simplified by Paul's nice form, and my eventual recollection of FileDialog!
    [vba]
    Option Compare Text
    Option Explicit

    Sub DataFrom()

    'Remember: this code requires a reference to the Word object model

    Dim wdApp As New Word.Application
    Dim wdDoc As Word.Document
    Dim fName As String
    Dim i As Long, Rw As Long

    ChDir ActiveWorkbook.Path

    With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = False
    .Filters.Add "Word", "*.doc", 1
    .Show
    On Error GoTo Exits
    fName = .SelectedItems(1)
    End With

    Set wdDoc = wdApp.Documents.Open(fName)

    Rw = Cells(Rows.Count, 1).End(xlUp).Row + 1
    Cells(Rw, 1) = Cells(Rw - 1, 1) + 1
    i = 1
    For Each f In wdDoc.FormFields
    i = i + 1
    On Error Resume Next
    Cells(Rw, i) = f.Result
    Next
    Exits:
    End Sub

    [/vba]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  10. #10
    VBAX Newbie
    Joined
    Aug 2006
    Posts
    4
    Location
    WOW!!! This is SO ELEGANT!!!

    It does precisely what was needed, including intelligently making sure other data does not get overwritten and providing the browser access so that we only need to "point & click" to process a file!

    THANK YOU VERY MUCH !!!

    - Paul

  11. #11
    VBAX Regular
    Joined
    Jul 2010
    Posts
    25
    Location

    help please

    Hello, I would word template that are saved with multiple file name in a folder, and I need to export only the data fields I need into an excel spreadsheet. Is this possible? sorry i am not very good with programming so can some one please help me with this? I can attach the template and excel file where i need this information and folder location if needed?

  12. #12
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    You can attach both files by zipping them together. See Manage Attachments in the Go Advanced reply section.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  13. #13
    VBAX Regular
    Joined
    Jul 2010
    Posts
    25
    Location
    Thanks mdmackillo. i have attached the 2 files i need to combine. the template is same but it has been saved under multiple file name with different details and these file names keep increasing depending on the invoice. These files are saved in z:\NSW
    Last edited by yogin; 07-05-2010 at 05:40 AM.

  14. #14
    VBAX Newbie
    Joined
    Dec 2011
    Posts
    1
    Location

    How to use extraction from Word forms data to Excel on Mac Office 2011?

    Hi,
    I'm a novice in VBA, and I am not sure how to make this work.

    I have to extract data from about 1000+ Word forms and place in Excel for analysis.

    I use Office 2011 for Mac which supports VBA.

    I would be extremely grateful for step-by-step guidance on how to implement this code.


    Quote Originally Posted by mdmackillop
    Thanks Steve.
    Greatly simplified by Paul's nice form, and my eventual recollection of FileDialog!
    [vba]
    Option Compare Text
    Option Explicit

    Sub DataFrom()

    'Remember: this code requires a reference to the Word object model

    Dim wdApp As New Word.Application
    Dim wdDoc As Word.Document
    Dim fName As String
    Dim i As Long, Rw As Long

    ChDir ActiveWorkbook.Path

    With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = False
    .Filters.Add "Word", "*.doc", 1
    .Show
    On Error GoTo Exits
    fName = .SelectedItems(1)
    End With

    Set wdDoc = wdApp.Documents.Open(fName)

    Rw = Cells(Rows.Count, 1).End(xlUp).Row + 1
    Cells(Rw, 1) = Cells(Rw - 1, 1) + 1
    i = 1
    For Each f In wdDoc.FormFields
    i = i + 1
    On Error Resume Next
    Cells(Rw, i) = f.Result
    Next
    Exits:
    End Sub

    [/vba]

  15. #15
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,894
    Location
    Hi Jens,

    Have you tried using the code? If so, with what result?

    If you don't know how to install the code (which goes into an Excel workbook) see, for example:
    http://dmcritchie.mvps.org/excel/install.htm
    http://www.teachexcel.com/exceltips/...adsheet_3.html
    Cheers
    Paul Edstein
    [MS MVP - Word]

  16. #16
    VBAX Newbie
    Joined
    Jan 2012
    Posts
    3
    Location
    Another option that works for me;

    Add VBA below; (Open VBA editor by Alt & F11)
    [vba]
    Sub CollateForms()
    Dim myPath As String
    Dim myWord As New Word.Application
    Dim myDoc As Word.Document
    Dim myField As Word.FormField
    Dim n As Long, m As Long
    Dim fs, f, f1, fc
    Range("A2").Select
    myPath = InputBox("Path?")
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(myPath)
    Set fc = f.Files
    m = 0
    For Each f1 In fc
    n = 0
    Set myDoc = myWord.Documents.Open(myPath & "\" & f1.Name)
    For Each myField In myDoc.FormFields
    ActiveCell.Offset(m, n).Value = myField.Result
    n = n + 1
    Next
    myDoc.Close wdDoNotSaveChanges
    m = m + 1
    Next
    Set myField = Nothing
    Set myDoc = Nothing
    Set myWord = Nothing
    End Sub[/vba]


    In VBA click on Tools > References > Click for Microsoft Word Object Library.

    Close VBA

    Now run the macro

    Click on cell A2 > add path to either file or folder with collection of same files (word forms)

    Hopefully the magic will work.

    Make sure your forms are the same format otherwise the rows will contain different fields.

    Hope this helps someone out there searching even though the original post was 6 years ago!

  17. #17
    Quote Originally Posted by stephen_
    Another option that works for me;

    Add VBA below; (Open VBA editor by Alt & F11)
    [vba]
    Sub CollateForms()
    Dim myPath As String
    Dim myWord As New Word.Application
    Dim myDoc As Word.Document
    Dim myField As Word.FormField
    Dim n As Long, m As Long
    Dim fs, f, f1, fc
    Range("A2").Select
    myPath = InputBox("Path?")
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(myPath)
    Set fc = f.Files
    m = 0
    For Each f1 In fc
    n = 0
    Set myDoc = myWord.Documents.Open(myPath & "\" & f1.Name)
    For Each myField In myDoc.FormFields
    ActiveCell.Offset(m, n).Value = myField.Result
    n = n + 1
    Next
    myDoc.Close wdDoNotSaveChanges
    m = m + 1
    Next
    Set myField = Nothing
    Set myDoc = Nothing
    Set myWord = Nothing
    End Sub[/vba]


    In VBA click on Tools > References > Click for Microsoft Word Object Library.

    Close VBA

    Now run the macro

    Click on cell A2 > add path to either file or folder with collection of same files (word forms)

    Hopefully the magic will work.

    Make sure your forms are the same format otherwise the rows will contain different fields.

    Hope this helps someone out there searching even though the original post was 6 years ago!
    Hi stephen,

    I tried with your guide but after inputing path, but it not worked. A "Micorosft Visual Basic" msgbox appeared with 2 option of "OK" and "Help". Can you help me with this. Tks

  18. #18
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,894
    Location
    What you're seeing is an Input box - not a Message box - with a prompt, saying 'Path?

    Simply change:
    myPath = InputBox("Path?")
    to:
    myPath = InputBox("What is the File Path for the Word documents that you want to process?")
    and see if you can work out the rest.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  19. #19
    Quote Originally Posted by macropod
    What you're seeing is an Input box - not a Message box - with a prompt, saying 'Path?

    Simply change:
    myPath = InputBox("Path?")
    to:
    myPath = InputBox("What is the File Path for the Word documents that you want to process?")
    and see if you can work out the rest.
    Yes, the Input box appear first, but after I inputting the path of folder, an erro message box with a promt appear
    Attached Images Attached Images

  20. #20
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,894
    Location
    What error message do you get, what code line is highlighted and, if you move the mouse over the highlighted line, what variable parameters pop up? What document is opened? What kinds of formfields does it have?
    Cheers
    Paul Edstein
    [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
  •