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.

Capturing Word Form data into Excel... Again, Again

Discussion in 'Business Applications' started by Heather112, Jun 21, 2012.

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

    Heather112 Thread Starter

    Joined:
    Jun 21, 2012
    Messages:
    16
    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
     
  2. Heather112

    Heather112 Thread Starter

    Joined:
    Jun 21, 2012
    Messages:
    16
    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.
     
  3. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    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
     
  4. Heather112

    Heather112 Thread Starter

    Joined:
    Jun 21, 2012
    Messages:
    16
    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
     
  5. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    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
     
  6. Heather112

    Heather112 Thread Starter

    Joined:
    Jun 21, 2012
    Messages:
    16
    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
     
  7. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    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
     
  8. Heather112

    Heather112 Thread Starter

    Joined:
    Jun 21, 2012
    Messages:
    16
    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
     
  9. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    What are the symptoms of bad operation? Any error message? Wrong values? Anything?
     
  10. Heather112

    Heather112 Thread Starter

    Joined:
    Jun 21, 2012
    Messages:
    16
    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.
     
  11. Heather112

    Heather112 Thread Starter

    Joined:
    Jun 21, 2012
    Messages:
    16
    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
     

    Attached Files:

  12. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    I went back to my original conception, and rewrote the code accordingly. I hope you will like it.
    Anyway, here is the code.
    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
     
  13. Heather112

    Heather112 Thread Starter

    Joined:
    Jun 21, 2012
    Messages:
    16
    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
     
  14. 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/1058022