Exporting data from Content Control Fields in Word to Excel 2010

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

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!
 

Attachments

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!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top