Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 03-17-2013, 12:15 AM
ahw ahw is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Mar 2013
Posts: 1
ahw is on a distinguished road
Default How to import list from Excel into drop-down list into word

I am making a form template for my work.


I want to add drop-down list that import the data from excel cells.
The excel sheet contains about 600 rows, in each row (name) + (Number).
So, when I edit the form I just need to press the list which will import the list and I choose one record.
Thanks a lot in advance
Reply With Quote
  #2  
Old 03-17-2013, 04:19 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,326
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Hi ahw,

IMHO, populating a dropdown with 600 entries is somewhat excessive. That said, you would need to use a userform or a dropdown content control for this. The following macro populates a dropdown content control with whatever is in column A in "Sheet1" in the nominated workbook. You can change both the workbook and worksheet references via the StrWkBkNm and StrWkShtNm variables, respectively. The column and row references are managed in the 'For i = 1 To LRow ... Next' loop. Note that the code shows how you can populate both the content control's 'Display name' and 'Value'.
Code:
Sub Document_Open()
Application.ScreenUpdating = False
'Note: A VBA Reference to the Excel Object Model is required, via Tools|References
Dim xlApp As New Excel.Application, xlWkBk As Excel.Workbook
Dim StrWkBkNm As String, StrWkShtNm As String, LRow As Long, i As Long
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Workbook Name.xls"
StrWkShtNm = "Sheet1"
If Dir(StrWkBkNm) = "" Then
  MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
  Exit Sub
End If
With xlApp
  'Hide our Excel session
  .Visible = False
  ' Open the workbook
  Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm, ReadOnly:=True, AddToMRU:=False)
  ' Process the workbook.
  With xlWkBk
      With .Worksheets(StrWkShtNm)
        ' Find the last-used row in column A.
        LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        ' Populate the content control titled 'ID', with Column A for the 'ID' as the
        ' content control Text and the values from columns B-E as the content control
        ' value, using a "|" separator
        ActiveDocument.SelectContentControlsByTitle("ID")(1).DropdownListEntries.Clear
        For i = 1 To LRow
          ActiveDocument.SelectContentControlsByTitle("ID")(1).DropdownListEntries.Add _
            Text:=Trim(.Range("A" & i))
          'or, for example, to add the contents of column B to the content control's 'value':
          'ActiveDocument.SelectContentControlsByTitle("ID")(1).DropdownListEntries.Add _
            Text:=Trim(.Range("A" & i)), Value:=Trim(.Range("B" & i))
        Next
      End With
    .Close False
  End With
  .Quit
End With
' Release Excel object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
Application.ScreenUpdating = True
End Sub
If you use the code to populate the content control's 'value', you can then supplement the above code with a content control on-exit macro to populate a dependent content control with that 'value'.

Do be aware that the above code will re-populate the dropdown every time you open the document, wiping out whatever you'd previously selected. To prevent that, change the macro's name from 'Document_Open' to 'Document_New' and add it to the document's template. You may need to create a specific template for this document. You can do that just by saving it as a template (once you've made the code change but before you've made any selections or other edits that you don't want to appear in every new version of this document). Alternatively, you could rename the macro (e.g. UpdateDropDown) so that it will only run on demand (e.g. via Alt-F8>UpdateDropDown>OK).

For PC macro installation & usage instructions, see: http://www.gmayor.com/installing_macro.htm
For Mac macro installation & usage instructions, see: http://word.mvps.org/Mac/InstallMacro.html
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 11-21-2014, 09:23 AM
BrerBunny BrerBunny is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Nov 2014
Posts: 2
BrerBunny is on a distinguished road
Unhappy Help!

This code will save me loads of time! I've tried using it in Word 2010 but I get the following error on running the macro:
Run-time error '6189':

This property can only be used with dropdown list or combo box content controls.

I have selected my DropDown List Content Control in Word that I want to put the values into, but it's not having it. Can I call it by code? The title of my dropdown list is Cause if that helps.

---Brerbunny
Reply With Quote
  #4  
Old 11-21-2014, 02:07 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,326
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

As coded, the macro assumes you want to update the first content control in the document having the title 'ID'. Evidently the one you want to update either lacks the title or isn't the first with that title. An alternative approach is to refer to the content control by its relative position in the document, using code like:
ActiveDocument.ContentControls(1)
replacing the '1' with whatever number corresponds with your dropdown content control. Selecting the content control makes no difference.

If you want to populate another content control with data based on your selection from the dropdown, you could adapt the macro in the attachment to post: http://www.msofficeforums.com/word-vba/16498-multiple-entries-dropdown-lists.html. With a little work, that code could even be modified to output different data to each of a plethora of other content controls.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 11-27-2014, 04:54 AM
BrerBunny BrerBunny is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Nov 2014
Posts: 2
BrerBunny is on a distinguished road
Red face

Is there any way I can find out which number my dropdown list is from Word? I thought that I only had dropdown lists as Content Control items, but if I'm getting that error there must be another Content Control item I'm not aware of.
Thanks!
Reply With Quote
  #6  
Old 11-27-2014, 06:18 AM
gmaxey gmaxey is offline Windows 7 32bit Office 2010 (Version 14.0)
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 741
gmaxey will become famous soon enough
Default

Every content control in the document has a unique id. You can determine the ID by selecting the CC title tab and running

Debug.Print Selection.Range.ContentControls(1).ID
'or
MsgBox Selection.Range.ContentControls(1).ID

You can then act on that CC with code using:

ActiveDocument.ContentControls("XXXXXXXXX").....

where "XXXXXXXX" represents the unique id number
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #7  
Old 07-07-2016, 09:43 PM
celias celias is offline Windows 10 Office 2016
Novice
 
Join Date: Jul 2016
Posts: 5
celias is on a distinguished road
Default

Hi! This is just what I was looking for.
Can you please just explain where and how I should enter this code? I tried the Visual Basic button on the Developer Tab, saved it and then clicked to run the macro. But it didn't work...

Any help is very much appreciated.

Thank you.
Reply With Quote
  #8  
Old 07-08-2016, 05:24 AM
gmaxey gmaxey is offline Windows 7 32bit Office 2010 (Version 14.0)
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 741
gmaxey will become famous soon enough
Default

If you are asking about:

Code:
Debug.Print Selection.Range.ContentControls(1).ID
'or
MsgBox Selection.Range.ContentControls(1).ID
You can run either from the VB Immediate window.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #9  
Old 07-10-2016, 10:46 PM
celias celias is offline Windows 10 Office 2016
Novice
 
Join Date: Jul 2016
Posts: 5
celias is on a distinguished road
Default

Quote:
Originally Posted by gmaxey View Post
I you are asking about:

Code:
Debug.Print Selection.Range.ContentControls(1).ID
'or
MsgBox Selection.Range.ContentControls(1).ID
You can run either from the VB Immediate window.
Hi, @gmaxey,
I was asking about the very first code on this thread (I'm sorry if I didn't place my question correctly).

I have a word template file with just one dropdown list created. I named the dropdown list as "Clients" and it has no options entered yet. I wanted to fill it with a list of clients that I have on column A of Sheet1 on an Excel file called "Clients List".

I created a macro on Word called Dropdownlist and I pasted the suggested code there. I changed the location for the StrWkBkNm file.

When I run the macro I get the error message: "Run-time error '1004': Application-defined or object-defined error". The file does exist and column A of sheet1 has data from A1 down to A106...

Do you have a suggestion about how to fix this?
Would this have to do with something called "declarations" that I saw somewhere?

Thank you in advance for any tip.

Last edited by celias; 07-10-2016 at 11:12 PM. Reason: forgot to say something
Reply With Quote
  #10  
Old 08-21-2016, 04:12 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,326
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Have you done all that the instructions in post #2 say you need to do? Did you
set a reference to Excel?
edit the StrWkBkNm line to point to your workbook-
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Workbook Name.xls"
edit the StrWkShtNm line to point to your worksheet-
StrWkShtNm = "Sheet1"
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #11  
Old 08-21-2016, 11:15 PM
celias celias is offline Windows 10 Office 2016
Novice
 
Join Date: Jul 2016
Posts: 5
celias is on a distinguished road
Default

Thank you, @macropod, for getting back to me. I can't remember now what the specific issue was, but I already got this functioning beautifully. And I also had a button on the document that can be double-clicked every time we need to update the drop-down list. Thank you once more.
Reply With Quote
  #12  
Old 03-17-2017, 07:51 AM
ackerliz ackerliz is offline Windows 10 Office 2010 32bit
Novice
 
Join Date: Mar 2017
Posts: 2
ackerliz is on a distinguished road
Default

Thanks, macropod, your macro works well. I am revamping an old form and trying to make it easier to manage with the list data in an Excel file. I am using the macro with this variation:

ActiveDocument.ContentControls(1)

How could I adapt this so that all my dropdown lists are updated, rather than call them individually, I call them all as a group or collective? I hope that makes sense.

Thanks.

Liz
Reply With Quote
  #13  
Old 03-17-2017, 08:20 AM
ackerliz ackerliz is offline Windows 10 Office 2010 32bit
Novice
 
Join Date: Mar 2017
Posts: 2
ackerliz is on a distinguished road
Default

OK, sorry for the post without doing more research...was able to fix my "typo" which made it so that the drop list were not populating appropriately.

ActiveDocument.SelectContentControlsByTitle("use_y our_own_unique_title")(1) works for all the drop lists.

Cheers!
Reply With Quote
Reply

Tags
lesson plan template, teacher help
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamically changing drop-down list based on selection? (Word Form) laurarem Word 1 02-21-2013 10:17 PM
How to import a list of names into PowerPoint from Excel? CarpetRemnant PowerPoint 4 12-07-2012 11:08 AM
Drop down list, Can it be done??? garethreid Outlook 0 08-09-2012 06:08 AM
drop down list from external data source Excel 2007? Intruder Excel 1 08-03-2012 05:41 AM
Long List for drop down box DLo99 Word 0 02-28-2010 08:07 AM


All times are GMT -7. The time now is 10:08 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft