Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-10-2019, 07:52 AM
dlafko1 dlafko1 is offline Take an Excel List and Add to Word Combo Box Windows 10 Take an Excel List and Add to Word Combo Box Office 2016
Advanced Beginner
Take an Excel List and Add to Word Combo Box
 
Join Date: Apr 2019
Posts: 31
dlafko1 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 04-10-2019, 02:53 PM
macropod's Avatar
macropod macropod is offline Take an Excel List and Add to Word Combo Box Windows 7 64bit Take an Excel List and Add to Word Combo Box Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

See, for example: https://www.msofficeforums.com/word-...html#post46287
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 04-10-2019, 05:12 PM
dlafko1 dlafko1 is offline Take an Excel List and Add to Word Combo Box Windows 10 Take an Excel List and Add to Word Combo Box Office 2016
Advanced Beginner
Take an Excel List and Add to Word Combo Box
 
Join Date: Apr 2019
Posts: 31
dlafko1 is on a distinguished road
Default Thanks

Thanks I had viewed that one but when I copied and pasted into word I could not get it to work.
Reply With Quote
  #4  
Old 04-10-2019, 05:23 PM
macropod's Avatar
macropod macropod is offline Take an Excel List and Add to Word Combo Box Windows 7 64bit Take an Excel List and Add to Word Combo Box Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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:
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 04-10-2019, 09:18 PM
gmayor's Avatar
gmayor gmayor is offline Take an Excel List and Add to Word Combo Box Windows 10 Take an Excel List and Add to Word Combo Box Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
The function is called from a macro that provides the variable data e.g.

Code:
Sub Macro1()
xlFillCCList "ContentControl Title", 0, "C:\Path\Datasheet.xlsx", "Worksheet Name", True, True
End Sub
If the dropdown is a userform combobox then see the relevant section at https://www.gmayor.com/Userform_ComboBox.html for code on which the above is loosely based.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #6  
Old 04-11-2019, 11:10 AM
dlafko1 dlafko1 is offline Take an Excel List and Add to Word Combo Box Windows 10 Take an Excel List and Add to Word Combo Box Office 2016
Advanced Beginner
Take an Excel List and Add to Word Combo Box
 
Join Date: Apr 2019
Posts: 31
dlafko1 is on a distinguished road
Default 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:
Originally Posted by macropod View Post
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:

Last edited by macropod; 04-11-2019 at 03:52 PM. Reason: Added code tags
Reply With Quote
  #7  
Old 04-11-2019, 03:54 PM
macropod's Avatar
macropod macropod is offline Take an Excel List and Add to Word Combo Box Windows 7 64bit Take an Excel List and Add to Word Combo Box Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #8  
Old 04-11-2019, 04:41 PM
gmaxey gmaxey is offline Take an Excel List and Add to Word Combo Box Windows 10 Take an Excel List and Add to Word Combo Box Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,422
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

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
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #9  
Old 04-12-2019, 05:32 AM
dlafko1 dlafko1 is offline Take an Excel List and Add to Word Combo Box Windows 10 Take an Excel List and Add to Word Combo Box Office 2016
Advanced Beginner
Take an Excel List and Add to Word Combo Box
 
Join Date: Apr 2019
Posts: 31
dlafko1 is on a distinguished road
Default

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.



Quote:
Originally Posted by macropod View Post
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.
Attached Images
File Type: png Capture.PNG (51.7 KB, 25 views)
Attached Files
File Type: docm Audit Request.docm (25.5 KB, 8 views)
Reply With Quote
  #10  
Old 04-12-2019, 06:24 AM
gmayor's Avatar
gmayor gmayor is offline Take an Excel List and Add to Word Combo Box Windows 10 Take an Excel List and Add to Word Combo Box Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Take an Excel List and Add to Word Combo Box 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

Other Forums: Access Forums

All times are GMT -7. The time now is 07:05 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft