Collecting Data from Formfields

[Greg]

I posted the following code today as an example of how you might
collect data from common formfields from a batch of files.

Basically it gets the names and a count of the files from the common
directory, builds a table in the active document to display the data,
opens each file and puts the data elements from three fields ( 1. Name
2. Age 3. Registration) into arrays, then outputs the data in the
arrays to the table. As I told the recipient of my earlier post, I am
not sure if the method that i proposed is best or even sound and would
appreciate any comments for improvement.

Spefic questions.
1) Should I attempt to use some sort of multi-dimensional array for
the data elements?
2) Is an array the best way or should I consider collections or even
worse classes. Both fill me with dread as I barely understand the
concepts.

Thanks.




Sub TallyData()
Dim oPath As String
Dim FileArray() As String
Dim oFileName As String
Dim DataArray() As String
Dim AgeArray() As String
Dim RegArray() As String
Dim i As Long
Dim j As Long
Dim oTbl As Word.Table
Dim myDoc As Word.Document

oPath = GetPathToUse
If oPath = "" Then
MsgBox "A folder was not selected"
Exit Sub
End If
'Identify and count files
oFileName = Dir$(oPath & "*.doc")
ReDim FileArray(1 To 1000) 'A number larger the expected number of
replies
'Add file name to the array
Do While oFileName <> ""
i = i + 1
FileArray(i) = oFileName
'Get the next file name
oFileName = Dir$
Loop
'Resize and preserve the array
ReDim Preserve FileArray(1 To i)
Application.ScreenUpdating = False
'Add the data table with headings
ActiveDocument.Tables.Add Selection.Range, i + 1, 3
Set oTbl = ActiveDocument.Tables(1)
With oTbl
.Cell(1, 1).Range.Text = "Name"
.Cell(1, 2).Range.Text = "Age"
.Cell(1, 3).Range.Text = "Registration"
End With
'Resize the data arrays to specific sizes
ReDim DataArray(1 To i)
ReDim AgeArray(1 To i)
ReDim RegArray(1 To i)
For i = 1 To UBound(FileArray)
Set myDoc = Documents.Open(FileName:=oPath & FileArray(i), _
Visible:=False)
With myDoc
DataArray(i) = .FormFields("Text1").Result
AgeArray(i) = .FormFields("Text2").Result
RegArray(i) = .FormFields("Text3").Result
.Close
End With
Next i
i = 1
For i = 1 To UBound(DataArray)
j = i + 1
oTbl.Cell(j, 1).Range.Text = DataArray(i)
oTbl.Cell(j, 2).Range.Text = AgeArray(i)
oTbl.Cell(j, 3).Range.Text = RegArray(i)
Next i
Application.ScreenUpdating = True
End Sub
Private Function GetPathToUse() As Variant
'Get the folder containing the files
'Note uses the "Copy Dialog" which enables the "open" option
With Dialogs(wdDialogCopyFile)
If .Display <> 0 Then
GetPathToUse = .Directory
Else
GetPathToUse = ""
Exit Function
End If
End With
If Left(GetPathToUse, 1) = Chr(34) Then
GetPathToUse = Mid(GetPathToUse, 2, Len(GetPathToUse) - 2)
End If
End Function

 

[Cindy M -WordMVP-]

Hi Greg,

I'm not an expert on this, either, but...

1. Read my article on MSDN about how to efficiently create tables.
Creating the table, then populating it, is relatively slow.

2. In this light, what you ultimately want to do is build a delimited
string of the data, plug it into a range, then convert the range to a
table

3. Whether or not you need an array as a between-step depends on any
number of circumstances. You can certainly build a string from an array,
but you have to determine whether this is necessary.

4. If, for some reason, you had to build the table as you propose, and
the data came from a shared database or over a network connection, then
it could make a lot of sense to build an array. Since creating the table
can be significantly slower than populating an array (then building the
table from that), you can save system resources / locking records by
picking up the data as efficiently as possible, then releasing the data
source before continuing to work.

5. Since you wouldn't be accessing the data randomly, a bit here and a
bit there, no need to set up a collection.

I posted the following code today as an example of how you might
collect data from common formfields from a batch of files.

Basically it gets the names and a count of the files from the common
directory, builds a table in the active document to display the data,
opens each file and puts the data elements from three fields ( 1. Name
2. Age 3. Registration) into arrays, then outputs the data in the
arrays to the table. As I told the recipient of my earlier post, I am
not sure if the method that i proposed is best or even sound and would
appreciate any comments for improvement.

Spefic questions.
1) Should I attempt to use some sort of multi-dimensional array for
the data elements?
2) Is an array the best way or should I consider collections or even
worse classes. Both fill me with dread as I barely understand the
concepts.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Word Heretic]

G'day "Greg" <[email protected]>,

I find the fastest way to sort large arrays is to insert all the data
as fields separated by tabs, records separated by para marks, then
convert the mess to a table after all records inserted, then sort, if
the WordBasic.Sort cheat isnt going to do it for you

Steve Hudson - Word Heretic

steve from wordheretic.com (Email replies require payment)
Without prejudice


Greg reckoned:

 

[Greg Maxey]

OK, Steve. Thanks.