1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Exporting Repeated Forms In One Word doc To Excel

Discussion in 'Business Applications' started by Phillipe, Aug 17, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Phillipe

    Phillipe Thread Starter

    Joined:
    Aug 17, 2012
    Messages:
    14
    Hello,

    Relating to a previous post:
    "Exporting Word Form Data to Excel"
    http://forums.techguy.org/business-applications/257309-exporting-word-form-data-excel.html
    regarding the same form in many word docs esported into an excel sheet

    My question is whether its possible to export repeated forms in ONE word doc so they collate into 5 columns.
    (Would like the first 2 columns and 2 rows left empty :rolleyes:)

    My form look in word is thus:

    Story:
    Image: 01
    Display-image:
    Courtesy:
    Caption:

    Story:
    Image: 02
    Display-image:
    Courtesy:
    Caption:

    Story:
    Image: 03
    Display-image:
    Courtesy:
    Caption:

    etc

    Sample.doc is below [​IMG]

    This macro obtained from:
    "Solved: Export Word Form to Excel"
    http://forums.techguy.org/business-applications/1062655-solved-export-word-form-excel.html
    works well (y) for the same form in many word docs exported to excel:

    Code:
    Dim vField As FormField
    Dim fso As Scripting.FileSystemObject
    Dim fsDir As Scripting.Folder
    Dim fsFile As Scripting.File
    Dim wdApp As Word.Application
    Dim myDoc As Word.Document
    Dim vColumn As Integer
    Dim vLastRow As Integer
    Dim x As Integer
     
    Sub AddFormFields()
     
    If ActiveSheet.UsedRange.Count = 1 Then
    vLastRow = 1
    Else
    vLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    End If
    vColumn = 1
     
    Set fso = New Scripting.FileSystemObject
     
    Set fsDir = fso.GetFolder _
    ("C:\Documents and Settings\User\Desktop\CIKA 2004\Members\UnProcessed")
     
    Set wdApp = New Word.Application
    wdApp.Visible = True
     
    For Each fsFile In fsDir.Files
     
    wdApp.Documents.Open (fsFile)
     
    Set myDoc = wdApp.ActiveDocument
     
    For Each vField In wdApp.Documents(myDoc).FormFields
     
    vField.Select
     
    vValue = vField.Result
     
    Workbooks("Members.xls").Activate
    Cells(vLastRow, vColumn).Select
     
    If vField.Type = 71 Then
     
    Select Case vField.Name
     
    Case "Check1"
    vColumn = vColumn - 1
    If vField.Result = "1" Then
    ActiveCell.Value = "YES"
    End If
     
    Case "Check2"
    If vField.Result = "1" Then
    ActiveCell.Value = "NO"
    End If
     
    End Select
     
    Else
     
    ActiveCell.Value = vValue
    End If
     
    vColumn = vColumn + 1
     
    Next
     
    vColumn = 1
    vLastRow = vLastRow + 1
     
    vFileName = wdApp.ActiveDocument.Name
     
    wdApp.ActiveDocument.Close
     
    Name fsFile As _
    "C:\Documents and Settings\User\Desktop\CIKA 2004\Members\Processed\" & vFileName
     
    Next
     
    wdApp.Quit
    End Sub
    
    Is it possible? :eek:.
    .
     

    Attached Files:

  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Can you also post a sample Excel workbook showing how you want the data arranged?

    Rollin
     
  3. Phillipe

    Phillipe Thread Starter

    Joined:
    Aug 17, 2012
    Messages:
    14
    Hi Rollin,
    Thanks for your reply.
    See Sample.xls
     

    Attached Files:

  4. Phillipe

    Phillipe Thread Starter

    Joined:
    Aug 17, 2012
    Messages:
    14
    Just thought that you probably need a doc without protection.
    Cheers
     

    Attached Files:

  5. Phillipe

    Phillipe Thread Starter

    Joined:
    Aug 17, 2012
    Messages:
    14
    Sorry, something is wrong with the form in my sample.doc I think..
    I'll get back.
     
  6. Phillipe

    Phillipe Thread Starter

    Joined:
    Aug 17, 2012
    Messages:
    14
    .
    Hi Rollin (if still there),
    Gee, just once I'd like to get it right first time!
    I now think I have a good form in:
    <sample.doc>
    Also below is:
    <the_look_preferred.xls>
    and
    <macro_to_be_changed.xls>
    Can you just work with those?
    Cheers
    .
     

    Attached Files:

  7. Phillipe

    Phillipe Thread Starter

    Joined:
    Aug 17, 2012
    Messages:
    14
    .
    May I add, if it helps, that the macro does not have to pass a second word form to add to the list already in the spreadsheet from a first pass. The intended action is merely a one off - one word form to a blank work sheet.
    Thanks.
    .
     
  8. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Is the data in the word form being produced automatically or is it something that is created manually? I am just trying to understand the process for the macro logic.

    Rollin
     
  9. Phillipe

    Phillipe Thread Starter

    Joined:
    Aug 17, 2012
    Messages:
    14
    .
    Hi Rollin,
    Thanks for your thought, time and response.
    I think you might be sorry you asked the question.

    The word doc has some general text and then has a form. Let's say that within the form area we have a 'formlet' that is repeated (unchanged) down the page any number of times. Each formlet has the same 5 input boxes. I 'word protect' the document so that the structure cannot be altered.

    The recipient of the document can then enter an item number (any item number) into one of the designated boxes of a formlet. The recipient can answer Yes, No or Undecided to a question in another box of the formlet. The recipient can then enter any amount of text into any of the other three boxes of the formlet. Typing into any of the boxes is optional. There may be some, if not all of the boxes in a formlet left unanswered or untouched. The recipient repeats the process in other formlets. There will probably be some formlets left untouched.

    The recipient returns the document to me. (See <sample.doc> below). I unprotect the document and pass it through a spreadsheet macro. Once it passes through the macro, it is hoped that the answers to each formlet appear on a separate row down the spreadsheet (that is, contained within 5 columns). There may be some cells appear with no data, indeed some rows appear with no data as well. (See <sample.xls> below).

    The macro does not have to pass a second word form to add to the list already in the spreadsheet from a first pass. The intended action is merely a one off - one word form to an empty work book.

    Then what I have done is created a function copy of the data on sheet 1 into sheet 2 with cell and row spacing of my choosing. I can simply do a 'delete empty rows' macro there too.

    Is there a setting in the macro that can save the data in a sheet other than sheet 1 by say the name given to the sheet?

    Thanks for your patience. I now realize that you are not a mind reader!
    .
     

    Attached Files:

  10. Phillipe

    Phillipe Thread Starter

    Joined:
    Aug 17, 2012
    Messages:
    14
    I'm still interested in a solution to this if possible.

    Reading around this amazing forum, I notice new members would say 'hello' which I didn't. So a mighty big hello to everyone. I love your work. Maybe one day I can help but I am very much the 'student' in here.

    Cheers for now.
    .
     
  11. Phillipe

    Phillipe Thread Starter

    Joined:
    Aug 17, 2012
    Messages:
    14
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1065471

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice