?
Solved

Reading MS Word

Posted on 2004-11-08
10
Medium Priority
?
810 Views
Last Modified: 2012-05-05
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
'------------------------------------------------------------------------------------------------------------------
0
Comment
Question by:eciabattari
  • 4
  • 3
8 Comments
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 12524269
Explain it slower. How many formfield names have you got and what do they relate to exactly?
0
 

Author Comment

by:eciabattari
ID: 12524373
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
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 12524389
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:fds_fatboy
ID: 12524508
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
 

Author Comment

by:eciabattari
ID: 12524546
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
 

Author Comment

by:eciabattari
ID: 12525296
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
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 12529053
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
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12898689
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question