[Heather112]

Hi Everyone,

Thank you for all your wonderful work on these boards. Your advice has helped me time and again, although this is the first time I'm posting myself.

Here is my trouble.

I adapted script written by Rolin_Again written for a poster named DougS when he was attempting to move data from a Word Form to Excel. I too need to have my Form in Word as it's a mass email and most of if not all of the recipients of the form will not have Access (oh how I wish they had Access). I have written my Form and attached it for you and saved an Excel Workbook called "Registration". The sheet is also called Registration. When I first ran the Macro I ran into the same trouble the poster did here with the Form opening when I used the macro, moving to the "processed" folder but no data transfer into Excel. I tried the fixes offered in that post but not successfully it seems. I am now having a new error and I have highlighted the line that the debugger points out as a problem (Run-time error '9'': Script out of range).

Any help anyone could offer would be much appreciated. I have never written code so this is all very foreign to me.

Thank you in advance. I have seen how helpful everyone is on these boards and I'm very grateful for the help.

Best,

Heather


MACRO:

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 Long
Dim x As Integer

Sub AddFormFields()

vLastRow = ActiveSheet.UsedRange.Rows.Count + 1
vColumn = 1

Set fso = New Scripting.FileSystemObject

Set fsDir = fso.GetFolder _
("C:\Documents and Settings\Heather\Desktop\Dance\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("Registration.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\Heather\Desktop\Dance\Processed\" & vFileName

Next

wdApp.Quit


End Sub

[Heather112]

Oh, I should mention too, I haven't distributed the Form yet so if I should be making changes to it )ActiveX Controls?) please feel free to let me know.

[Jimmy the Hand]

Hello Heather, welcome to the forum.

First of all, make sure the registration workbook is called "Registration.xls". Isn't it "Registration.xlsx"? If it's the latter, then correct the erroneous code line, the same one you highlighted, and see if the code works.

If the code still doesn't work, I will rewrite it for you, but first I need to clear things up, because I'm not sure I fully understand what you want. Let me sum up what I have gathered and deducted, and then you say yes or no.

So you have an Excel workbook, called "Registration", and a worksheet inside, also called "Registration"
Also, you have a folder brimming with word docs. Each of the word docs is in fact a registration form, the same one you have attached to your first post, but these forms are filled by customers (or someones).
You need a macro that processes all registration forms, and gathers the data from them into the worksheet called "Registration". All processed forms (word docs) should be moved into a separate folder. The macro should reside in the Excel workbook.

Is that right?

Jimmy

[Heather112]

Hi Jimmy,

Thank you so much for the reply.

Yes you have summed up what I am looking to do perfectly. Each line in the Excel document would represent each Registration form.

Below (if it's at all helpful) is the original code I was using that opened the Word doc, moved it from the Unprocessed to the Processed folder but didn't transfer any of the data into the Excel sheet. My first post has the code I am using currently. Thank you for your suggestion in your post, after reading it I tried .xls .xlsx and .xlm (it appears to be saved as .xlsm) but still had the same error. What's strange to me is that I didn't have that error with the first code I was using and it had the same line (although it didn't transfer the data).

I have updated the Registration Form and it now includes two check boxes so I've attached the updated Form.

Thank you again for your help, it is very much appreciated.

Heather

Old Macro:
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()

vLastRow = ActiveSheet.UsedRange.Rows.Count + 1
vColumn = 1

Set fso = New Scripting.FileSystemObject

Set fsDir = fso.GetFolder _
("C:\Documents and Settings\Duane\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

x = Split(fsFile, Application.PathSeparator)
vFileName = x(UBound(x))

wdApp.ActiveDocument.Close

Name fsFile As _
"C:\Documents and Settings\Duane\Desktop\CIKA 2004\Members\Processed\" & vFileName

Next

wdApp.Quit


End Sub

[Jimmy the Hand]

Hi Heather

For better readability, you may want to put VBA code lines between code tags. During writing your post, just highlight the code and click on the # sign in the editor window.

Thanks for the updated form. I will need the registration workbook as well, so that I can see which data goes where. Strip it of any sensitive data and post it. (As an alternative, you can list the columns in the workshhet for me, but that's too cumbersome.)

Jimmy

[Heather112]

Hi Jimmy,

I haven't labeled my columns yet, but was planning on using the same order as the form (Last Name, First Name, Address, etc...). so that each column represented a field on the Form. I would like to be able to add columns later but nothing that would involve the macro, only manual entries and I don't have to be able to do that if it will interfere with this. Let me know if you'd like me to set up an Excel and post it.

Thank you so much,

Heather

[Jimmy the Hand]

No, in this case I don't need the worksheet. I'll make the macro create the column labels automatically, based on the control bookmarks in the Word form. I'll be back when I have anything to report.
Just one thing. You may want to review the registration form and see that each control has a proper bookmark. (use right click, then select Properties) I looked on a few controls, and they had fine bookmarks, but the checkboxes should be modifed.

Jimmy

[Heather112]

Jimmy.. I figured out my error, should be .xlsm

The only trouble I'm having now is getting the check boxes to be recognized. Would you be able to fix my code from my first post to get the two check boxes at the end of the form to be read?

Thank you so very much!

Heather

[Jimmy the Hand]

What are the symptoms of bad operation? Any error message? Wrong values? Anything?

[Heather112]

Just nothing get's pulled. When I try a test Registration Form and click off the boxes there is nothing in the Excel to indicate they've been ticked. Otherwise everything else is working perfectly.

[Heather112]

Thank you again for looking at this.

I have abandoned the two check boxes I was having trouble with for logistical reasons on this end but would like to add a series of check boxes that will need to be pulled into the same Excel following the Form information. I don't know the code to get the check boxes to read. The check boxes will populate columns in the Excel just as the other fields on the Word Form and will only need to give an indication if the box has been checked off.

Thank you again for all your help. I appreciate it more than I can say.

Heather

[Jimmy the Hand]

I went back to my original conception, and rewrote the code accordingly. I hope you will like it.
Anyway, here is the code.

Sub RegForms()
    Dim WA As Word.Application, WD As Word.Document, WField As Word.FormField
    Dim srcFolder As String, dstFolder As String, FName As String, FieldName As String, Data As String
    Dim ws As Worksheet, rngColumnHead As Range, rngNextRecord As Range
    
    srcFolder = "C:\Documents and Settings\Jimmy\Asztal\Regform\Forms\"
    dstFolder = "C:\Documents and Settings\Jimmy\Asztal\Regform\Processed\"
    Set ws = ThisWorkbook.Worksheets("Registration")
    
    Set WA = New Word.Application
    WA.Visible = True
    Do
        FName = Dir(srcFolder & "*.doc*")
        If FName = "" Then Exit Do
        Set WD = WA.Documents.Open(srcFolder & FName)
        Set rngNextRecord = ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(1)
        rngNextRecord.Value = FName
        For Each WField In WD.FormFields
            FieldName = WField.Name
            Set rngColumnHead = ws.Range("1:1").Find(what:=FieldName, LookIn:=xlValues, lookat:=xlWhole)
            If rngColumnHead Is Nothing Then
                Set rngColumnHead = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Offset(, 1)
                rngColumnHead.Value = FieldName
            End If
            Select Case WField.Type
            Case 71
                If WField.Result = "1" Then
                    Data = "yes"
                Else
                    Data = "no"
                End If
            Case Else
                Data = WField.Result
            End Select
            Intersect(rngNextRecord.EntireRow, rngColumnHead.EntireColumn) = Data
        Next
        WD.Close
        Name srcFolder & FName As dstFolder & FName
    Loop
    WA.Quit
End Sub

Instructions:
1. Copy the code into a code module of the workbook called Registration.xlsm
2. Create a reference from this workbook to Word object library. Go to the VB editor window, select Tools->References from Menu, scroll down until you find Microsoft Word xx.x Object Library, and check it.
3. On the worksheet called "Registration", clear all contents, and put "File name" into cell A1. This will be the first column head. All other column heads will be created automatically, based on the form fields.
4. Save the workbook
5. Very important: review the registration form, in order to give names to your fields. Right click on a form field, then select Properties. Give a unique bookmark name (at least, in my local version, the data you need to fill is called "bookmark", maybe in your version it is called something else) to each of the fields and checkboxes. These names will be used as column heads in the worksheet, so they should be representative names. I mean, if you see a column head like "CheckBox 18", you won't know what it refers to. If you see a column head like "Monday 10", you will know right away that it refers to the lesson starting on Monday, 10 AM.

You would have to do Step 5. anyway, even if I had only modified your original code, because the fields and checkboxes must have unique ID-s. Otherwise, the code will not know where to put the data taken from them.

Jimmy

[Heather112]

Hi Jimmy,

You're the best! Thank you so much for your help. It works perfectly. I wouldn't be able to do this registration electronically without your work.

Thank you so much,

Heather