Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / March 2005

Tip: Looking for answers? Try searching our database.

Populate a Dropdown from Excel Values

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Robert_L_Ross - 08 Mar 2005 23:23 GMT
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 - 09 Mar 2005 01:10 GMT
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.

Signature

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

> This post is step 2 of another post...
>
[quoted text clipped - 49 lines]
> THX in advance for taking the time to read over this and THX x 2 if you
> reply!
Robert_L_Ross - 09 Mar 2005 16:37 GMT
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.

> I would suggest that you use a listbox on a userform rather than formfields.
>
[quoted text clipped - 58 lines]
> > THX in advance for taking the time to read over this and THX x 2 if you
> > reply!

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.