Reading MS Word

Hi All - need some suggestions.  I have the following code that does the following:
1)  Opens MS Word file and returns the names of each FormField
2)  Passes FormField name to TableLength and returns the appropriate values
3)  Sets array to the proper values
4)  Reads the value of each FormField within the file relating to the proper information

This MS Word file has 356 questions and each question has 4 formfields (that's 1,400+ values) that need to be read and added to an MS Access DB.

One way I was thinking of doing this would be create another array with all formfield names (deleting duplicates) and then pass each value.  Any suggestions on how to create an array without adding duplicates?

Any suggestions would be helpful.

'------------------------------------------------------------------------------------------------------------------
Dim appWord As Word.Application
Dim doc As Word.Document
Dim strFormFieldName As String

Function ReadWord()
On Error Resume Next
   
    Dim frm as FormField
     
    Set appWord = CreateObject("Word.Application")
    Set doc = appWord.Documents.Open(strWordPath)
   
    strCounter = 0
   
    'returns the name of the FormFields
    For Each frm In appWord.ActiveDocument.FormFields
        If frm.Type = wdFieldFormCheckBox Then
            frm.Select
            strFormFieldName = Left$(frm.Name, 2)
        ElseIf frm.Type = wdFieldFormTextInput Then
            frm.Select
            strFormFieldName = Left$(frm.Name, 2)
        End If
           
        'sets the form field name, maxrows & table name
        Call TableLength(strFormFieldName)
           
        'array for calculating the formfields results
        ReDim strQA(1 To intMaxRows)
        ReDim strQB(1 To intMaxRows)
        ReDim strQC(1 To intMaxRows)
        ReDim strQT(1 To intMaxRows)

            For i = 1 To intMaxRows
                strQA(i) = doc.FormFields(strFormNameA & i & "a").Result
                strQB(i) = doc.FormFields(strFormNameB & i & "b").Result
                strQC(i) = doc.FormFields(strFormNameC & i & "c").Result
                strQT(i) = doc.FormFields(strFormNameT & i & "t").Result
            Next
    Next

    doc.Close
    appWord.Quit

End Function

Private Sub TableLength(strTable As String)
    Select Case strTable

        Case "GR"
            strFormNameA = "GR"
            strFormNameB = "GR"
            strFormNameC = "GR"
            strFormNameT = "GR"
            intMaxRows = 16
            strTableName = "tblGR"

        Case "FG"
            strFormNameA = "FG"
            strFormNameB = "FG"
            strFormNameC = "FG"
            strFormNameT = "FG"
            intMaxRows = 24
            strTableName = "tblFG"

    End Select
End Sub
'------------------------------------------------------------------------------------------------------------------
eciabattariAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fds_fatboyCommented:
Explain it slower. How many formfield names have you got and what do they relate to exactly?
0
eciabattariAuthor Commented:
There are a total of 15 sections, each section could have 8 questions and as many of 90 questions.  Each question has 4 formfields (3 are checkboxes & 1 is a text box), example below.  

I need to take the results out of the Word file and input them into an MS Access DB.  The Access DB has 15 tables, each table represents a different section.  

Example:
                                          Yes        No        N/A              Comments
1.  Do you know the web?

BTW; I would have never done a survey like this, I would have done a web survey that inputs directly into a DB.  However, the powers to be already sent the survey out and I'm stuck getting all the results input into this DB.

Does this expalin it better?
0
fds_fatboyCommented:
Does strFormNameA wlways equal strFormNameB, strFormNameC and strFormNameT? If so why not ditch strFormNameA, strFormNameB, strFormNameC and strFormNameD and just have strFormName?.

Also, It would be nicer to have local variables for intMaxRows, strTableName and strFormName rather than globals or module level variables.
TableLength could be a function returning the max rows and passing back strTableName and strFormName by reference.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

fds_fatboyCommented:
What I meant when I asked the question was I can't see where the duplicates would be unless the same names are repeated.
0
eciabattariAuthor Commented:
No, strFormNameA, strFormNameB, strFormNameC and strFormNameT do not always equal each other.

Eample:
Section 1 "General Requirements"
Question 1.  Yes = strFormNameA = GR1a, No = strFormNameB = GR1b, N/A = strFormNameC = GR1c, Comments = strFormNameT = GR1t
Question 2.  Yes = strFormNameA = GR2a, No = strFormNameB = GR2b, N/A = strFormNameC = GR2c, Comments = strFormNameT = GR2t

Section 2 "Formal Groups"
Question 1.  Yes = strFormNameA = CA1a, No = strFormNameB = CA1b, N/A = strFormNameC = CA1c, Comments = strFormNameT = CA1t
Question 2.  Yes = strFormNameA = CA2a, No = strFormNameB = CA2b, N/A = strFormNameC = CA2c, Comments = strFormNameT = CA2t

Here's the section that returns intMaxRows & other information:
Private Sub TableLength(strTable As String)
    Select Case strTable

        Case "GR"
            strFormNameA = "GR"
            strFormNameB = "GR"
            strFormNameC = "GR"
            strFormNameT = "GR"
            intMaxRows = 16
            strTableName = "tblGR"

        Case "FG"
            strFormNameA = "FG"
            strFormNameB = "FG"
            strFormNameC = "FG"
            strFormNameT = "FG"
            intMaxRows = 24
            strTableName = "tblFG"

    End Select
End Sub
0
eciabattariAuthor Commented:
I figured it out, here's what I did:
1)  Created an Array with all FormField Names
2)  Removed duplicates from FormField Array
3)  Passes FormField Array new array that would get results.

Thanks for the help.

Here's the code:
'----------------------------------------------------------------------------------------------------------------------------
Function ReadWord()
On Error Resume Next
   
    'form
    Dim frm  As FormField

    ReDim strArray(1500)
   
    frmMain.txtFilePath.Visible = False
   
    Set appWord = CreateObject("Word.Application")
    Set doc = appWord.Documents.Open(strWordPath)

    strCounter = 0
    c = 1
   
    'returns the name of the FormFields
    For Each frm In appWord.ActiveDocument.FormFields
       
        If frm.Type = wdFieldFormCheckBox Then
            frm.Select
            strFormFieldName = Left$(frm.Name, 2)
            strArray(c) = strFormFieldName
        ElseIf frm.Type = wdFieldFormTextInput Then
            frm.Select
            strFormFieldName = Left$(frm.Name, 2)
            strArray(c) = strFormFieldName
        End If
        Call ProgressBar(1)
        c = c + 1
    Next
   
    Call RemoveDuplicates
    'Call Import2Access
   
    doc.Close
    appWord.Quit
   
    frmMain.cmdSaveWorkbook.Visible = True

End Function

Private Sub RemoveDuplicates()

    Dim tmpNew() As String, newIndex As Integer
    Dim t As Integer
   
    newIndex = 0
   
    For i = 0 To UBound(strArray) - 1
        On Error Resume Next
        isfound = False
        If getArrLength(tmpNew) > -1 Then
            For j = 0 To UBound(tmpNew)
                If strArray(i) = tmpNew(j) Then
                    isfound = True
                End If
            Next j
        End If
        If isfound = False Then
            ReDim Preserve tmpNew(newIndex)
            tmpNew(newIndex) = strArray(i)
            newIndex = newIndex + 1
        End If
    Next i
   
    'Display New Array
    For i = 0 To UBound(tmpNew)
        Call TableLength(tmpNew(i))
        Call ProgressBar(2)
        'array for calculating the formfields results
        ReDim strQA(1 To intMaxRows)
        ReDim strQB(1 To intMaxRows)
        ReDim strQC(1 To intMaxRows)
        ReDim strQT(1 To intMaxRows)
           
            For t = 1 To intMaxRows
                Call ProgressBar(2)
                strQA(t) = doc.FormFields(strFormNameA & t & "a").Result
                strQB(t) = doc.FormFields(strFormNameB & t & "b").Result
                strQC(t) = doc.FormFields(strFormNameC & t & "c").Result
                strQT(t) = doc.FormFields(strFormNameT & t & "t").Result
                'Debug.Print tmpNew(i) & "  " & intMaxRows & " " & strTableName & "  " & strQA(t) _
                     & "  " & strQB(t) & "  " & strQC(t)
            Next
    Next i
   
End Sub

Private Function getArrLength(v) As Integer
On Error GoTo EH
    getArrLength = UBound(v)
    Exit Function

EH:
    getArrLength = -1
End Function
'----------------------------------------------------------------------------------------------------------------------------


0
fds_fatboyCommented:
I still don't understand.
You seemed to have the names in the first place Why bother going to the trouble of putting them into an array. Why not just put them straight into the database?

A small tip - set Option Explicit and explicitly declare your variables.

To speed things up - the line after
    isfound = true
could/should read
    Exit For
0
moduloCommented:
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.