#1
|
|||
|
|||
Take an Excel List and Add to Word Combo Box
I have a list of agencies in an excel sheet called Agency, the agencies are listed on tab Agency and are in Colum A. Is there a way for me to get that data to populate in a combo box in word with out having to manually enter each one? Thanks I am not very good with VBA code I tired a couple of solutions I found but could not get them to work, probably because I don't know what to edit. Thanks for the help |
#2
|
||||
|
||||
See, for example: https://www.msofficeforums.com/word-...html#post46287
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thanks
Thanks I had viewed that one but when I copied and pasted into word I could not get it to work.
|
#4
|
||||
|
||||
I'd have thought it pretty obvious that you'd need to make some adjustments to the code for it to suit your particular needs - few of which you've specified. At the very least, you'd need to change the worksheet name in the code - and the accompanying discussion even draws attention to the kinds of changes you might need to make:
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
||||
|
||||
If this is a content control dropdown then the following code will fill such a content control with the items from a column in an Excel Worksheet.
Code:
Option Explicit Public Function xlFillCCList(strBoxTitle As String, _ iColumn As Long, _ strWorkbook As String, _ strRange As String, _ RangeIsWorksheet As Boolean, _ RangeIncludesHeaderRow As Boolean) 'Graham Mayor - http://www.gmayor.com - Last updated - 11 April 2019 'strBoxTitle is the name of the list or combo box content control to be filled 'iColumn is the column in the sheet (or the range) to be displayed in the list/combo box 'Columns are numbered starting from 0 i.e. Column A would be iColumn = 0 'strWorkbook is the full path of the workbook containing the data 'strRange is the name of the worksheet or named range containing the data 'RangeIsWorksheet - set to True if the range 'strRange' is a worksheet ' or False if it is a named range 'RangeIncludesHeaderRow - Set to True is the Worksheet or named range contains a header row Dim RS As Object Dim CN As Object Dim oCC As ContentControl Dim NumRecs As Long, lngRec As Long Dim strWidth As String Dim arr() As Variant If RangeIsWorksheet = True Then strRange = strRange & "$]" Else strRange = strRange & "]" End If Set CN = CreateObject("ADODB.Connection") If RangeIncludesHeaderRow Then CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strWorkbook & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=YES"";" Else CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strWorkbook & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=NO"";" End If Set RS = CreateObject("ADODB.Recordset") RS.CursorLocation = 3 RS.Open "SELECT * FROM [" & strRange, CN, 2, 1 'read the data from the worksheet With RS .MoveLast NumRecs = .RecordCount .MoveFirst End With arr = RS.getrows(NumRecs) For Each oCC In ActiveDocument.ContentControls If oCC.TITLE = strBoxTitle Then With oCC .DropdownListEntries.Clear For lngRec = 0 To UBound(arr, 2) .DropdownListEntries.Add arr(iColumn, lngRec) Next lngRec End With Exit For End If Next oCC lbl_Exit: If RS.State = 1 Then RS.Close Set RS = Nothing If CN.State = 1 Then CN.Close Set CN = Nothing Set oCC = Nothing Exit Function End Function Code:
Sub Macro1() xlFillCCList "ContentControl Title", 0, "C:\Path\Datasheet.xlsx", "Worksheet Name", True, True End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#6
|
|||
|
|||
Sorry Marcropod
Sorry Marcropod,
I did make what I thought were the correct edits. I put the code in my Word VBA Code section and here is what I had done. Code:
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\dlafko\Desktop\Job Titles ONly.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 Quote:
Last edited by macropod; 04-11-2019 at 03:52 PM. Reason: Added code tags |
#7
|
||||
|
||||
So what error message, if any, does the macro report?
I see, too, that you haven't changed the sheet name in: StrWkShtNm = "Sheet1" without that change, it isn't going to work.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
|||
|
|||
I don't really have to have a macro at all. My Content Control Insert Utility lets you import a list from Excel to Combo and List type content controls:
https://gregmaxey.com/word_tip_pages...rt_Dialog.html |
#9
|
|||
|
|||
I uploaded the image of the error and the word document. I left the excel sheet name as sheet1 (went back to it) because of the error, I figured the more alike they are the less chance of error. I passed this in the VBA Section and Under the macro section, just to try and make sure it was not me.
|
#10
|
||||
|
||||
Did you read the line above the highlighted one?
There is no macro code in your attachment.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Changing Drop Down List to Combo Box | workingspade | Word | 6 | 09-09-2022 06:12 AM |
Letter Template /w combo box list | etruz | Word | 4 | 10-31-2018 05:16 AM |
Word 2010 Content Control help - Combo Boxes vs Drop Down List | proghy | Word | 1 | 09-16-2014 02:01 PM |
How can I add hyperlink to open a word file from an excel combo box selection? | mahmoudramadan | Excel | 0 | 10-20-2012 10:41 AM |
Bold list items in a combo box | DrewB | Word VBA | 0 | 07-17-2009 11:32 AM |