X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Combo Box To Be Filled With Excel List, Any Version    
post Apr 15 2019, 11:41 AM

Posts: 217
Joined: 7-May 14

Hello all I tried to ask this on another forum and should have known better. This forum is always so helpful with explanation.

I have an excel workbook called Agency and Sheet 1 is a list of all the agencies.

I have a word Document called Audit Request with a combo box. I want to take the list of 160 Agencies and put that in the Combo Box on the word document. I have seen some VBA Code but not really understanding the code or where to put it I can not get it to work.

I felt like the other forum people were just so nasty about it. I gave up.

I have attached the word document here

Attached File(s)
Attached File  Audit_Request__.zip ( 20.37K )Number of downloads: 6
Go to the top of the page
post Apr 16 2019, 04:00 AM

Posts: 1,309
Joined: 25-January 16
From: The Great Land

Could you provide the link for the code you saw?

Could give macro procedures meaningful names.

How are these macro procedures executed?

Got combobox code working with an ActiveX combobox, not the Form combobox you installed. Had to go into ThisDocument Open event under the section called Project.
Sub Document_Open()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Users\June\LL\Umpires.accdb'"
rs.Open "SELECT * FROM Umpires", cn, adOpenDynamic, adLockPessimistic
Do While Not rs.EOF
   cboAgency.AddItem rs!UmpID
End Sub

Replace the Access recordset code with whatever you do to pull data from Excel.

This post has been edited by June7: Apr 16 2019, 04:27 AM

Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
post Apr 16 2019, 06:59 AM

Posts: 217
Joined: 7-May 14

For your code if I have the document open where do I go next?

This is the code I tried to use in VBA Code section of word
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
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))
End With
.Close False
End With
End With
' Release Excel object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
Application.ScreenUpdating = True
End Sub
Go to the top of the page
post Apr 16 2019, 07:07 AM

Posts: 217
Joined: 7-May 14

Ok I copied your code into the VBA section of the Active X Combo Box and I get this error. Image attached.
Attached File(s)
Attached File  Capture.PNG ( 57.75K )Number of downloads: 2
Go to the top of the page
post Apr 16 2019, 08:12 AM

Posts: 2,833
Joined: 4-February 07
From: USA, Florida, Delray Beach

  1. I created a Demo for you which:
    1. Replaces the Combo Box Content Control with a Combo Box ActiveX Control.
    2. When the Document Opens (Audit Request_Revised.docm) populates the Combo Box (ComboBox1) with all Agency Names in Column 'A' in 'Sheet1' of C:\Test\Agency.xlsx. Agency Names must be contiguous throughout all Rows in Column A.
    3. You can change the PATH to Agency.xlsx in the USER DEFINED SECTION Section of the Code.
    4. This is accomplished via Early Binding, Automation Code, and the AddItem Method of a Combo Box.
    5. This Code resides in the Open() Event of the Document.
  2. Code Definition:
    Private Sub Document_Open()
    Dim appExcel As Excel.Application
    Dim Wkb As Excel.Workbook
    Dim wks As Excel.Worksheet
    Dim intRow As Integer

    '*************** USER DEFINED SECTION ***************
    Const conPATH_TO_AGENCIES = "C:\Test\Agency.xlsx"

    intRow = 1      '1st Row with Data in Column A, Data must be contiguous

    Set appExcel = New Excel.Application
    Set Wkb = appExcel.Workbooks.Open(conPATH_TO_AGENCIES)
    Set wks = Wkb.Worksheets("Sheet1")

    With wks
      Do While .Cells(intRow, "A") <> ""
        Me.ComboBox1.AddItem .Cells(intRow, "A").Value
          intRow = intRow + 1
    End With

    Wkb.Close True

    Set appExcel = Nothing
    Set Wkb = Nothing
    Set wks = Nothing
    End Sub
  3. To see the Demo in operation simply Download the Revision and Agency.xlsx. You can either Copy Agency.xlsx to a C:\Test\ Folder or Copy it to another location and change the Value of the Constant conPATH_TO_AGENCIES.

This post has been edited by ADezii: Apr 16 2019, 08:47 AM
Attached File(s)
Attached File  Agency_Files.zip ( 30.94K )Number of downloads: 5
Go to the top of the page
post Apr 16 2019, 11:22 AM

Posts: 217
Joined: 7-May 14

Thanks so much that worked perfectly. One question When I save it as a template and re-open the code is gone because it is a new document is there a way to save it as a template or another way to force the user to save a new version rather than overwriting the text that was selected before?
Go to the top of the page
post Apr 16 2019, 04:11 PM

Posts: 2,833
Joined: 4-February 07
From: USA, Florida, Delray Beach

You can make Audit Request_Revised.docm a Read Only Macro Enabled Document (*.docm) forcing the User to Save it under another Filename. The downside, of course, is that anyone can change the Read Only Status of the File.
Go to the top of the page
post Apr 18 2019, 06:44 AM

Posts: 217
Joined: 7-May 14

Thanks you gave me the idea and so I password protected the modify so in order to save it they are forced to make a new one. Seems to work good.

Thanks for all the help.
Go to the top of the page
post Apr 18 2019, 07:32 AM

Posts: 2,833
Joined: 4-February 07
From: USA, Florida, Delray Beach

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    4th April 2020 - 02:40 AM