[mchoi]

and the reason why i initially chose the dropdown is because the dropdown choice is part of the title.. like "blahblahblah" Department.

[Rollin_Again]

Add the checkboxes and then use an IF statement to evaluate which box is select and then choose the appropriate sheet to use.

        If myDoc.FormFields("Check1").Result = 1 Then
        Sheets("Type 1").Select
        ElseIf myDoc.FormFields("Check2").Result = 1 Then
        Sheets("Type 2").Select
        ElseIf myDoc.FormFields("Check3").Result = 1 Then
        Sheets("Type 3").Select
        End If

Regards,
Rollin

[mchoi]

is there a way to make it so only one box is able to be checked?

also do i loop the cell reading code you typed before?

thanks so much for your help

[Rollin_Again]

is there a way to make it so only one box is able to be checked?

Not without using macro code that is attached to the Word doc. In order for this to work macros would need to be enabled by the end user. You may be better off using a drop down control as your planned on before and then using a SELECT statement instead of the IF statement I provided previously.

Select Case myDoc.FormFields("Dropdown1").Result
       
        Case "Type1"
        Sheets("Type 1").Select
       
        Case "Type2"
        Sheets("Type 2").Select
       
        Case "Type3"
        Sheets("Type 3").Select
       
        End Select

Regards,
Rollin

[mchoi]

so i am guessing i should nest that loop in front of the entire loop?

am i able to use the first code i pasted and replace it with Range("A1").Value = myDoc.FormFields("Text3").Result
or do i need a whole different loop?

thanks and have a great weekend!

[Rollin_Again]

Here is the final code that you can play around with an alter as you please. It assumes that you are using a drop down menu for the sheet choice as previously discussed. Since you are only transferring 6 fields to the workbook I would just hard code the field names as I've done below. Using a loop can be helpful if you have lots of fields to transfer over but in this case I think it's overkill.

Sub CollateForms()

    Dim myPath As String
    Dim myWord As New Word.Application
    Dim myDoc As Word.Document
    Dim fs, f, f1, fc
    myPath = "C:\Documents and Settings\Admin\Desktop\Test"
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(myPath)
    Set fc = f.Files
    m = 0
    For Each f1 In fc

        Set myDoc = myWord.Documents.Open(myPath & "\" & f1.Name)
        
        Select Case myDoc.FormFields("Dropdown1").Result
       
        Case "TYPE1"
        Sheets("Type 1").Select
       
        Case "TYPE2"
        Sheets("Type 2").Select
       
        Case "TYPE3"
        Sheets("Type 3").Select
       
        End Select
       
     
Range("A" & Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row).Value = _
myDoc.FormFields("Text1").Result

Range("B" & Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Row).Value = _
myDoc.FormFields("Text2").Result

Range("C" & Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Row).Value = _
myDoc.FormFields("Text3").Result

Range("D" & Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Row).Value = _
myDoc.FormFields("Text4").Result

Range("E" & Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row).Value = _
myDoc.FormFields("Text5").Result

Range("F" & Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Row).Value = _
myDoc.FormFields("Text6").Result
        
        myDoc.Close wdDoNotSaveChanges

    Next

    Set myDoc = Nothing
    Set myWord = Nothing
End Sub

Regards,
Rollin

[mchoi]

hey rollin.. sorry one more question.
how would i add a hyperlink to the actual file also? do i just insert this into one of range result code?
Address:=.FoundFiles(i), TextToDisplay:=.FoundFiles(i)

[mchoi]

also i tried running the macro and an error pops up.

Compile error:
user-defined type not defined

and it highlights line 4 of the code:
myWord As New Word.Application

[mchoi]

i figured out the compile error... i didnt activate the microsoft word object library...

hyperlinks?

[Rollin_Again]

Just add the following line to add the hyperlink. Just change the portion in red to reflect what text to display in the hyperlink. If you want to display the full path just change "Test Link" to f1 (without the quotes)

Range("G" & Cells(Rows.Count, "G").End(xlUp).Offset(1, 0).Row).Hyperlinks.Add _
Anchor:=Cells(Cells(Rows.Count, "G").End(xlUp).Offset(1, 0).Row, 7), Address:=f1, TextToDisplay:="TEST LINK"

Regards,
Rollin

[mchoi]

thanks so much for your help!!!