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.

Exporting data from Content Control Fields in Word to Excel 2010

Discussion in 'Business Applications' started by sandervd, Nov 2, 2011.

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

    sandervd Thread Starter

    Joined:
    Nov 2, 2011
    Messages:
    4
    Hi,
    I've been searching all kinds of fora for this problem and although I find information on how to do this with (legacy) Form Fields, my problem involves Content Control fields and I can't seem to fix this.
    Basically I have Sales Reports that are being filled out on a weekly basis by all Sales Reps. These contains all kinds of different Content Control fields (Drop down, rich text, dates etc...), and the data in there should be copied to an excel sheet. Meaning: copying the contents of the fields from all the different reports into 1 excel report.
    Below is a Macro I found which works for legacy forms (FieldForm) (pre-2007) but I cannot seem to make it work for 2010. I pinned down the problem to being just a wording problem in MS, but then again, it might be a little more complicated ;)

    Many thanks for your help,

    Code:
     
    Dim vField As FormFields
    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()
    vLastRow = ActiveSheet.UsedRange.Rows.Count + 1
    vColumn = 1
    Set fso = New Scripting.FileSystemObject
    Set fsDir = fso.GetFolder _
    ("Q:\Sales Reports\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("Sales.xlsm").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 _
    "Q:\Sales Reports\Processed\" & vFileName
    Next
    wdApp.Quit
        
        
    End Sub
    
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Can you post a sample Word doc?

    Rollin
     
  3. sandervd

    sandervd Thread Starter

    Joined:
    Nov 2, 2011
    Messages:
    4
    Hi Rollin Again,
    Attached the file with dummy data. Didn't always fill out all the fields as (unfortunately...) it happens all the time. Plus, it is not always clear how many items they'll have to fill out, so we provide more space than is actually needed. Ideally there would be a way of stopping the loop when a full range of empty fields is encountered but that would make it way too complicated and customized, so I'll just use an excel template spreadsheet to only copy the useful information in a useful template.

    It would be nice (if that's even possible) to perhaps export all the data of 1 section on 1 row, meaning each doc results in 4 rows? Would I in that case just add Sections in word and the macro would recognize them?

    And thirdly as you notice I haven't added control fields everywhere (some are just tables to fill out), but of course if it is needed for the macro we just make every part an Content Control...

    Thanks a lot for your help!
     

    Attached Files:

  4. sandervd

    sandervd Thread Starter

    Joined:
    Nov 2, 2011
    Messages:
    4
    Hi there,
    is it working any better? just need to know how to export Access Control Fields instead of the 'old' Field Forms... It can't be no one knows hoe to fix this? Or didn't MS foresee this...?
    Cheers!
     
  5. sandervd

    sandervd Thread Starter

    Joined:
    Nov 2, 2011
    Messages:
    4
  6. 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!

Loading...
Thread Status:
Not open for further replies.

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

  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