[mchoi]

Running WinXP and Office 2003

Hello. This is similar to http://forums.techguy.org/business-a...ata-excel.html

I tried using and tweaking that example but am having a hard time because there are several different things.

I will be having hundreds of word documents. The first page on each doc is just a form that a user fills out on the computer. I would like some help with the macro that would run and process a folder of these docs.

I would also like to have 3 sheets in the one excel file. The doc file will be filled out generally by 3 departments. Is there a way to have a drop menu in the doc to indicate which dept and also have this transfer to their respective sheets in the excel file? The rest of the doc fields should be the same. Also I need the hyperlink to the file in their respective cell.

Sorry I am new to code in excel and am starting to learn its power. Thanks for your help!

[Rollin_Again]

Open your document and then click VIEW >> TOOLBARS >> FORMS to bring up the forms toolbox. Add your various controls to the document (text fields, checkboxes, etc.) After adding all your needed fields save the document and then attach to your next post and we'll get started on the coding. You should have a separate text form field for each place the user will type in a value in addition to your 3 checkboxes at the top of the document.

Regards,
Rollin

[mchoi]

Ok i think i did it.
How does a user fill it out? its a little hard to try to click inside the field.

[mchoi]

how do i get the fields so that the user can open the word doc and just fill it in rather than arrow into the field. When i click on the field it would pick the field and delete it if i start typing. The user will open this doc and fill out the fields and then type a body of text after the cover page.

[mchoi]

ok nevermind i found out you have to lock it... but the thing is, i want the user to be able to fill out the other items and append bodies of text after this cover page. also this additional stuff shouldnt be exported to excel.

thanks so much

[Rollin_Again]

You can lock certain portions of your document while leaving others unlocked by adding a section break between the pages. Goto the last line of the cover page and then click INSERT >> BREAK and choose to add a continuous section break. After adding the section break you can protect the document by clicking on TOOLS >> PROTECT DOCUMENT and then on the right hand side protection menu choose to allow Filling of forms and choose the section you want to apply the protection to which should be section 1. Does that make sense?

Regards,
Rollin

[mchoi]

youre a lifesaver.

does it matter that the rest of the pages are not a form. its just text like in a word doc.

and what about the code for excel info extraction? i have this so far but dont know what to do about the three tabs for the sheet. I want the info to extract to their respective sheet depending on the pulldown menu


Sub CollateForms()
Dim myPath As String
Dim myWord As New Word.Application
Dim myDoc As Word.Document
Dim myField As Word.FormField
Dim n As Long, m As Long
Dim fs, f, f1, fc
Range("A2").Select
myPath = "C:\Documents and Settings\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(myPath)
Set fc = f.Files
m = 0
For Each f1 In fc
n = 0
Set myDoc = myWord.Documents.Open(myPath & "\" & f1.Name)
For Each myField In myDoc.FormFields
ActiveCell.Offset(m, n).Value = myField.Result
n = n + 1
Next
myDoc.Close wdDoNotSaveChanges
m = m + 1
Next
Set myField = Nothing
Set myDoc = Nothing
Set myWord = Nothing
End Sub

[mchoi]

btw i tried the break-continuous... doesnt work.

when i click on the lock icon in the form menubar, the whole document turns unediting except the fields... i want pgs 2 and up to be pages where the user can type up more document stuff. is this not possible? i only wanted the first page as a form.

[mchoi]

wow sorry for my typing without thinking.
i did what you said and it worked perfectly... you are so helpful

now i just need help with the tabbing of excel depending on the pulldown menu choice.

[Rollin_Again]

Post or email me your completed "template" file and I'll write the code for you. Instead of using a loop to read each field I would suggest hard coding the field names instead.

Regards,
Rollin

[Rollin_Again]

Please also include a sample Excel document showing exactly how you would like the transferred data to appear.

Regards,
Rollin

[mchoi]

I attached the excel and word template.
I would like the data to be extracted to their respective sheets according to which option from the pulldown the user selects.

sorry it wouldnt let me upload the template dot file.

what do you mean by hard coding the fields rather than looping?

[mchoi]

is there also a way to have the pulldown menu user-noticeable when the doc is locked?

[Rollin_Again]

Why not just use 3 checkboxes at the top instead of a drop down menu? There are ways to set focus to the dropdown using VBA code but unfortunately this would require that the end user have macros enabled in Word. Another option would be to change the color of the font in the drop down or add an autoshape such as an arrow next to the drop down menu.

As far as hard-coding the fields this would mean that instead of looping through the fields and pasting their values somewhere you would actually reference the field name like in the example below. You can get the actual field name by right clicking the field in design mode (unlocked) and choosing properties. I prefer giving the fields a meaningful name and referencing them by name since it gives a bit more flexibility and allows you to add/remove fields easily without having to figure out which field is being referenced at any particular point in the FOR loop.

Range("A1").Value = myDoc.FormFields("Text3").Result


Regards,
Rollin

[mchoi]

check boxes are fine but i just need the 3 choices to export to the 3 respective tabs in the excel file.

can you help me with the code for the excel?