Populate a Dropdown from Excel Values

[Robert_L_Ross]

This post is step 2 of another post...

I am trying to have a Word Fax Cover Sheet populate it's data from an Excel
Spreadsheet that contains contact information (To, Attn, Fax#, Phone#)

I'd like to have the document open, populate a DropDown box with the Attn
info from an Excel Spreadsheet, then when one of those selections are made,
populate the To, Fax# and Phone# from the same Excel spreadsheet.

Through other posts, I've got this much:
Dim CSFaxApp As Excel.Application
Dim CSFaxWorkbook As Excel.Workbook
Dim CSFaxSheet As Excel.Worksheet
Dim CSFaxArray As Excel.Range
Dim ATTNBoxInfo As Variant
Dim ToBoxInfo As Variant
Dim FAXBoxInfo As Variant
Dim PHONEBoxInfo As Variant
Dim ArrFaxInfo As String

Set CSFaxApp = GetObject(, "Excel.Application")
Set CSFaxWorkbook = GetObject("pathname\CSFaxList.xls")

Set CSFaxSheet = CSFaxWorkbook.Worksheets(1)
Set CSFaxArray = CSFaxSheet.Range("a2:e4")

ATTNBoxInfo = ActiveDocument.FormFields("ATTNBox").DropDown.Value
MsgBox (ATTNBoxInfo)

....then at the end:

ActiveDocument.FormFields("ToBox").Result = ToBoxInfo
ActiveDocument.FormFields("FaxNumber").Result = FaxNumberInfo
ActiveDocument.FormFields("TelNumber").Result = TelNumberInfo

At this point, the Sheet has 4 columns:
Entry (the dropdown entry case (1-3, etc.)
Attn - What I want to populate into the DropDown box when the doc opens
To, Fax and Phone - What I want returned and placed into the corresponding
fields on the doc

Can anyone give me any info to populate the dropdown list from Excel? I'm
guessing it should be something along the line of:
ActiveDocument.FormFields("AttnBox").DropDown.ListEntries.Add (something here)

And can anyone give me any info on how to then search the Excel sheet and
return the values it finds based on the dropdown entry?

THX in advance for taking the time to read over this and THX x 2 if you reply!

 

[Doug Robbins]

I would suggest that you use a listbox on a userform rather than formfields.

See the article "Load a ListBox from a Named Range in Excel using DAO" at:

http://word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm

I haven't seen the Step 1 to which you refer, but it is not a good idea to
start separate threads for the same subject.


--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[Robert_L_Ross]

This is a different subject all together...the other post is for using
pre-filled options (has nothing to do with getting data from Excel to
populate the DropDown box). This is step 2 in a process...this question does
not apply to my other post.