Hello. We have a form in Word that is required to open a new file. This
form contains a great number of fields.

We would like to create an automatic spreadsheet of just certain fields on
this form when it has been submitted (easy text fields such as name and date
opened). Is there any way to do that without having to recreate the form in
a different program?

Thanks in advance for any suggestions.
Elf

I would also be interested in a solution as you have proposed...

Hi ?B?ZWxmbWFqZXN0eQ==?=,

We have a form in Word that is required to open a new file. This
form contains a great number of fields.

We would like to create an automatic spreadsheet of just certain fields on
this form when it has been submitted (easy text fields such as name and date
opened). Is there any way to do that without having to recreate the form in
a different program?

You can certainly use a macro to extract content from specific form fields and
write it into a spreadsheet. Information on how to automate a spreadsheet from
within Word can be found at word.mvps.org

To get data from a textbox form field, the basic syntax is
Dim doc as Word.document

Set doc = ActiveDocument
sData = doc.Formfields("NameOfField").Result

No need to unprotect the document, or anything like that.

Please note that this is an end-user group. If you need more assistance, it
would probably be better to follow-up in word.programming. Be sure to mention
the version of Office you're targeting.

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 :-)

Cindy,

Thank you for your reply.

After some extensive research yesterday, I found this:

To save form data for use in a database or spreadsheet:
1. Open the document that was filled in as a form in Word.
2. On the Tools menu, click Options, and then click the Save tab.
3. Select the Save Data Only for Forms check box.
4. Click OK.
5. On the File menu, click Save Copy As.
6. Type in a file name in the File Name box.
7. In the Save as Type box, select Plain Text, and then click Save.
8. Select a text encoding option, and then click OK.
Word saves the form field data into a comma-delimited text file.

This all worked for me until I got to step number 8. After clicking Save in
step 7, my file was simply in the csv text file format. Never figured out
where to select a text encoding option.

I was able to then import that data into an excel spreadsheet. Great, in a
way. But here's the difficulty. I would have to do that with every single
form I receive on an individual basis.

In Word's help feature under forms "About Printed Forms and Forms for Use in
Word," I found this:

"You can create a form that users view and complete in Word. This form is
distributed and collected by using e-mail or by posting it on a network
location. You can use text fields, check boxes, and drop-down lists. You can
compile data from this form and then analyze it in Microsoft Access or
Microsoft Excel.

Some advantages to using forms that users complete in Word are that Word can
automatically verify user input (such as an employee number), update other
fields based on the input in an associated field (such as the city and region
associated with a particular postal code), and offer Help messages to make
the form easier to fill out."

THIS IS EXACTLY WHAT I NEED TO DO - post a form on a network location and
compile the data in Excel. What I can't find in Word's Help feature is
somewhere telling me how to do this!

Any further assistance would be fantastic and very much appreciated. : )

Cheers,
Elf






"Cindy M -WordMVP-" wrote:

Hi ?B?ZWxmbWFqZXN0eQ==?=,

We have a form in Word that is required to open a new file. This
form contains a great number of fields.

We would like to create an automatic spreadsheet of just certain fields on
this form when it has been submitted (easy text fields such as name and date
opened). Is there any way to do that without having to recreate the form in
a different program?

You can certainly use a macro to extract content from specific form fields and
write it into a spreadsheet. Information on how to automate a spreadsheet from
within Word can be found at word.mvps.org

To get data from a textbox form field, the basic syntax is
Dim doc as Word.document

Set doc = ActiveDocument
sData = doc.Formfields("NameOfField").Result

No need to unprotect the document, or anything like that.

Please note that this is an end-user group. If you need more assistance, it
would probably be better to follow-up in word.programming. Be sure to mention
the version of Office you're targeting.

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 :-)

Hi ?B?ZWxmbWFqZXN0eQ==?=,

I was able to then import that data into an excel spreadsheet. Great, in
a
way. But here's the difficulty. I would have to do that with every
single
form I receive on an individual basis.

Yes, that's why I didn't even present the "Save as Text file 'solution'". Of
course, you could do that, and have macro code in Excel to run through all
the *.csv files in a folder, importing them one-by-one. If that would work
better for you (because people would be returning the text files), that's
fine - but you need to ask then in the excel.programming newsgroup.

THIS IS EXACTLY WHAT I NEED TO DO - post a form on a network location and
compile the data in Excel. What I can't find in Word's Help feature is
somewhere telling me how to do this!

Word/Office doesn't provide any way to actually bring the data into another
application for analysis. At some point, if you don't want to process each
form result manually, you have to delve into programming the solution (or
hire someone to do it for you).

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 :-)

Cindy,
Not quite the answer I wanted to hear, but thanks for the reply. : )

Why do you suppose Microsoft would even post this in the help files written
like this if there's no easy way to do it?
"You can create a form that users view and complete in Word. This form is
distributed and collected by using e-mail or by posting it on a network
location. You can use text fields, check boxes, and drop-down lists. You can
compile data from this form and then analyze it in Microsoft Access or
Microsoft Excel."

Maybe I should post in the suggestions section.

Cheers,
Elf


"elfmajesty" wrote:

Cindy,

Thank you for your reply.

After some extensive research yesterday, I found this:

To save form data for use in a database or spreadsheet:
1. Open the document that was filled in as a form in Word.
2. On the Tools menu, click Options, and then click the Save tab.
3. Select the Save Data Only for Forms check box.
4. Click OK.
5. On the File menu, click Save Copy As.
6. Type in a file name in the File Name box.
7. In the Save as Type box, select Plain Text, and then click Save.
8. Select a text encoding option, and then click OK.
Word saves the form field data into a comma-delimited text file.

This all worked for me until I got to step number 8. After clicking Save in
step 7, my file was simply in the csv text file format. Never figured out
where to select a text encoding option.

I was able to then import that data into an excel spreadsheet. Great, in a
way. But here's the difficulty. I would have to do that with every single
form I receive on an individual basis.

In Word's help feature under forms "About Printed Forms and Forms for Use in
Word," I found this:

"You can create a form that users view and complete in Word. This form is
distributed and collected by using e-mail or by posting it on a network
location. You can use text fields, check boxes, and drop-down lists. You can
compile data from this form and then analyze it in Microsoft Access or
Microsoft Excel.

Some advantages to using forms that users complete in Word are that Word can
automatically verify user input (such as an employee number), update other
fields based on the input in an associated field (such as the city and region
associated with a particular postal code), and offer Help messages to make
the form easier to fill out."

THIS IS EXACTLY WHAT I NEED TO DO - post a form on a network location and
compile the data in Excel. What I can't find in Word's Help feature is
somewhere telling me how to do this!

Any further assistance would be fantastic and very much appreciated. : )

Cheers,
Elf






"Cindy M -WordMVP-" wrote:

Hi ?B?ZWxmbWFqZXN0eQ==?=,

We have a form in Word that is required to open a new file. This
form contains a great number of fields.

We would like to create an automatic spreadsheet of just certain fields on
this form when it has been submitted (easy text fields such as name and date
opened). Is there any way to do that without having to recreate the form in
a different program?

You can certainly use a macro to extract content from specific form fields and
write it into a spreadsheet. Information on how to automate a spreadsheet from
within Word can be found at word.mvps.org

To get data from a textbox form field, the basic syntax is
Dim doc as Word.document

Set doc = ActiveDocument
sData = doc.Formfields("NameOfField").Result

No need to unprotect the document, or anything like that.

Please note that this is an end-user group. If you need more assistance, it
would probably be better to follow-up in word.programming. Be sure to mention
the version of Office you're targeting.

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 :-)

Elf,

I know a little about Word and practically nothing about Excel. Maybe
we can help each other out.

Here is a bit of code that I cobbled together that extracts data from a
couple of form fields, stores it in a dataobject (I don't know if it is
Excel or not) then reports the compiled data in a Word table. All you
have to do is put all of your forms in a common directory.


Sub TallyData3()
Const adVarChar = 200
Const MaxCharacters = 255
Dim DataList As Object
Dim oPath As String
Dim FileArray() As String
Dim oFileName As String
Dim i 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 = "Favorite Food"
.Cell(1, 3).Range.Text = "Favorite Color"
End With
'Prepare the database
Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "Name", adVarChar, MaxCharacters
DataList.Fields.Append "FavFood", adVarChar, MaxCharacters
DataList.Fields.Append "FavColor", adVarChar, MaxCharacters
DataList.Open
'Retrieve the data
For i = 1 To UBound(FileArray)
Set myDoc = Documents.Open(FileName:=oPath & FileArray(i), _
Visible:=False)

DataList.AddNew
With myDoc
DataList("Name") = .FormFields("Text1").Result
DataList("FavFood") = .FormFields("Text2").Result
DataList("FavColor") = .FormFields("Text3").Result
.Close
End With
DataList.Update
Next i
'Display the data
i = 1
DataList.MoveFirst
Do Until DataList.EOF
i = i + 1
oTbl.Cell(i, 1).Range.Text = DataList.Fields.Item("Name")
oTbl.Cell(i, 2).Range.Text = DataList.Fields.Item("FavFood")
oTbl.Cell(i, 3).Range.Text = DataList.Fields.Item("FavColor")
DataList.MoveNext
Loop
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

If you are able to adapt this so that it would simply record the
compiled data in a permant Access database or Excel spreadsheet then I
would appreciate seeing your results. Thanks.

Greg,

"Cobbled together"??? I'd love to see what you come up with when you're
really concentrating! ; )

I'll give this a go, certainly. I really appreciate your time and effort.
Will let you know how I fare.

Cheers,
Elf

"Greg" wrote:

Elf,

I know a little about Word and practically nothing about Excel. Maybe
we can help each other out.

Here is a bit of code that I cobbled together that extracts data from a
couple of form fields, stores it in a dataobject (I don't know if it is
Excel or not) then reports the compiled data in a Word table. All you
have to do is put all of your forms in a common directory.


Sub TallyData3()
Const adVarChar = 200
Const MaxCharacters = 255
Dim DataList As Object
Dim oPath As String
Dim FileArray() As String
Dim oFileName As String
Dim i 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 = "Favorite Food"
.Cell(1, 3).Range.Text = "Favorite Color"
End With
'Prepare the database
Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "Name", adVarChar, MaxCharacters
DataList.Fields.Append "FavFood", adVarChar, MaxCharacters
DataList.Fields.Append "FavColor", adVarChar, MaxCharacters
DataList.Open
'Retrieve the data
For i = 1 To UBound(FileArray)
Set myDoc = Documents.Open(FileName:=oPath & FileArray(i), _
Visible:=False)

DataList.AddNew
With myDoc
DataList("Name") = .FormFields("Text1").Result
DataList("FavFood") = .FormFields("Text2").Result
DataList("FavColor") = .FormFields("Text3").Result
.Close
End With
DataList.Update
Next i
'Display the data
i = 1
DataList.MoveFirst
Do Until DataList.EOF
i = i + 1
oTbl.Cell(i, 1).Range.Text = DataList.Fields.Item("Name")
oTbl.Cell(i, 2).Range.Text = DataList.Fields.Item("FavFood")
oTbl.Cell(i, 3).Range.Text = DataList.Fields.Item("FavColor")
DataList.MoveNext
Loop
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

If you are able to adapt this so that it would simply record the
compiled data in a permant Access database or Excel spreadsheet then I
would appreciate seeing your results. Thanks.

Well by cobbled, I meant put together from bits and pieces of similiar
processes that others have taught me, hair pulling, and luck.

What I would like to do is simply open each document and extract the
data to a permanent Access data base.

In layman's terms something like:
Create and open new database
Create the fields
For Each File
Write data to database fields
Next
Save database as: C:\myResults.mdb
Close database

I have no idea where or how to start.

That's exactly what I'd like to be able to do as well (well, to an Excel db,
but same difference). Hopefully, with some research and minimal effort,
we'll be able to get that result somehow.

"Greg" wrote:

Well by cobbled, I meant put together from bits and pieces of similiar
processes that others have taught me, hair pulling, and luck.

What I would like to do is simply open each document and extract the
data to a permanent Access data base.

In layman's terms something like:
Create and open new database
Create the fields
For Each File
Write data to database fields
Next
Save database as: C:\myResults.mdb
Close database

I have no idea where or how to start.

Elf,

A found and some code post by the reliable Doug Robbins that I was able
to adapt and plug into my existing macro. I am sorry to say that I
don't know how this would be adapted to Excel, but perhaps you can
figure it out. I wish that there was a more straight forward approach
to opening a database from Word and transfering data to the database.
Without Doug's code I would have never figured out how to create the
connection. If any pros are reading I would appreciate it if you could
point me to some online references for reading up on establish
connections between Word and other Office applications.


Sub TallyData4()
Dim oPath As String
Dim FileArray() As String
Dim oFileName As String
Dim i As Long
'Requires reference to MS ActiveX Data Objects 2.8 Library
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim myDoc As Word.Document

oPath = GetPathToUse
If oPath = "" Then
MsgBox "A folder was not selected"
Exit Sub
End If
'Identify files names
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
'Provide connection string for data using Jet Provider for Access
database
vConnection.ConnectionString = "data source=C:\TestDataBase.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset, adLockOptimistic
'Retrieve the data
vConnection.Execute "DELETE * FROM MyTable"
For i = 1 To UBound(FileArray)
Set myDoc = Documents.Open(FileName:=oPath & FileArray(i), _
Visible:=False)
vRecordSet.AddNew
With myDoc
If .FormFields("Text1").Result "" Then _
vRecordSet!Name = .FormFields("Text1").Result
If .FormFields("Text2").Result "" Then _
vRecordSet("Favorite Food") = .FormFields("Text2").Result
If .FormFields("Text3").Result "" Then _
vRecordSet("Favorite Color") = .FormFields("Text3").Result
.Close
End With
Next i
vRecordSet.Update
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
Application.ScreenUpdating = True
End Sub

.com


om


om
Newsgroups: microsoft.public.word.docmanagement
NNTP-Posting-Host: 159.205.76.83.cust.bluewin.ch 83.76.205.159
Path: TK2MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl
Lines: 1
Xref: TK2MSFTNGP01.phx.gbl microsoft.public.word.docmanagement:217784

Hi Greg,

If any pros are reading I would appreciate it if you could
point me to some online references for reading up on establish
connections between Word and other Office applications.

It depends on the application :-) ADO, as in the sample code you
post, depends on an OLE DB connection; the application must
supply an "OLE DB provider". In Office, only Access does this;
the Access provider also works with Excel. For any other
application, you have to use automation.

The other possibility is a DAO connection. DAO is the native
Access/Jet engine "language". The syntax is similar to ADO, but
not exactly the same. DAO tends to be slightly more reliable
with Excel than ADO, IMO. And you can certainly accomplish tasks
that manipulate an Access database structure (as opposed to just
the data) more easily with it than with ADO. (Of course, since
it's "native".) DAO also provides an interface for connecting to
other data sources using ODBC, via that database's ODBC driver.

Information on DAO can be found in the Access Help files. Most
(older) advanced Access books (such as those by Ken Getz) also
cover using DAO.

There's a data.ado newsgroup if you want to delve into ADO.
Basic connection information (strings) can be found he
http://www.carlprothman.net/

If you're really interested, and can get hold of it, "ADO
Examples and Best Practices" from a!press, by Vaughn, is a very
useful resource.

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 :-)