Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-30-2018, 01:09 PM
badarlodhi badarlodhi is offline Importing content control data from excel and populate two fields on selected dropdown Windows 10 Importing content control data from excel and populate two fields on selected dropdown Office 2016
Novice
Importing content control data from excel and populate two fields on selected dropdown
 
Join Date: Jul 2018
Posts: 4
badarlodhi is on a distinguished road
Default Importing content control data from excel and populate two fields on selected dropdown

I have an excel sheet which contains ID, Department and IP Phone in three columns. I want to use ID as drop down list in word and when selected need respective department and IP Phone populated in word form. I have created the drop down but now struck with populating the other two fields.



Thanks in advance
Reply With Quote
  #2  
Old 07-30-2018, 03:35 PM
macropod's Avatar
macropod macropod is offline Importing content control data from excel and populate two fields on selected dropdown Windows 7 64bit Importing content control data from excel and populate two fields on selected dropdown Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,493
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

For code to do the Excel data import, see:
https://www.msofficeforums.com/word-...html#post46287
and, for code to output the stored values to another content control:
https://www.msofficeforums.com/word-...html#post46903
or, for different elements from a selected item to be output to different content controls, see:
https://www.msofficeforums.com/word-...tml#post120392
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 07-31-2018, 09:16 AM
gmaxey gmaxey is offline Importing content control data from excel and populate two fields on selected dropdown Windows 7 32bit Importing content control data from excel and populate two fields on selected dropdown Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 1,105
gmaxey has a spectacular aura aboutgmaxey has a spectacular aura aboutgmaxey has a spectacular aura about
Default

You can also do this using an ADODB connection (saves physically opening the Excel file):

I've included a graphic showing some example Excel data. Place all of the code in the ThisDocument module. Rename\Define your CCs as appropriate. Change path of the Excel file to suit.

Document example attached.


Code:
Option Explicit
Dim arrData() As Variant
Private Sub Document_ContentControlOnExit(ByVal oCC As ContentControl, Cancel As Boolean)
Dim lngIndex As Long
  Select Case oCC.Title
    Case "Name"
      If Not oCC.ShowingPlaceholderText Then
        'Determine which dropdown list entry was selected. Note: The object model has no direct way to do this.
        For lngIndex = 1 To oCC.DropdownListEntries.Count
          If oCC.Range.Text = oCC.DropdownListEntries.Item(lngIndex) Then Exit For
        Next lngIndex
        'Use that date fill in the transposed name and fill the dependent fields.
        With oCC
          .Type = wdContentControlText
          .Range.Text = arrData(1, lngIndex - 2)
          .Type = wdContentControlDropdownList
        End With
        With ActiveDocument
          'In the Excel data, "~" is used to define linebreaks in the address column.  Replace with linebreaks.
          .SelectContentControlsByTitle("Address").Item(1).Range.Text = Replace(arrData(2, lngIndex - 2), "~", Chr(11))
          .SelectContentControlsByTitle("Phone Number").Item(1).Range.Text = arrData(3, lngIndex - 2)
          .SelectContentControlsByTitle("Email").Item(1).Range.Text = arrData(4, lngIndex - 2)
        End With
      Else
        With ActiveDocument
          'Reset the dependent CCs.
          .SelectContentControlsByTitle("Address").Item(1).Range.Text = vbNullString
          .SelectContentControlsByTitle("Phone Number").Item(1).Range.Text = vbNullString
          .SelectContentControlsByTitle("Email").Item(1).Range.Text = vbNullString
        End With
      End If
    Case Else
  End Select
lbl_Exit:
  Exit Sub
End Sub
Sub Document_Open()
Dim strWorkbook As String, strColumnData As String
Dim lngIndex As Long, lngRowIndex As Long, lngColIndex As Long
'Dim arrData As Variant
Dim oCC As ContentControl
  Application.ScreenUpdating = False
  strWorkbook = "D:\Data Stores\Fill CC from Excel Data Store.xlsx"
  If Dir(strWorkbook) = "" Then
    MsgBox "Cannot find the designated workbook: " & strWorkbook, vbExclamation
    Exit Sub
  End If
  Set oCC = ActiveDocument.SelectContentControlsByTitle("Name").Item(1)
  arrData = fcnExcelDataToArray(strWorkbook, "Data")
  If oCC.DropdownListEntries.Item(1).Value = vbNullString Then
    For lngIndex = oCC.DropdownListEntries.Count To 2 Step -1
      oCC.DropdownListEntries.Item(lngIndex).Delete
    Next lngIndex
  Else
    oCC.DropdownListEntries.Clear
  End If
  For lngRowIndex = 0 To UBound(arrData, 2)
    oCC.DropdownListEntries.Add arrData(0, lngRowIndex), arrData(1, lngRowIndex)
  Next
lbl_Exit:
  Application.ScreenUpdating = True
  Exit Sub
End Sub
Private Function fcnExcelDataToArray(strWorkbook As String, _
                                     Optional strRange As String = "Sheet1", _
                                     Optional bIsSheet As Boolean = True, _
                                     Optional bHeaderRow As Boolean = True) As Variant
Dim oRS As Object, oConn As Object
Dim lngRows As Long
Dim strHeaderYES_NO As String
  strHeaderYES_NO = "YES"
  If Not bHeaderRow Then strHeaderYES_NO = "NO"
  If bIsSheet Then strRange = strRange & "$]" Else strRange = strRange & "]"
  Set oConn = CreateObject("ADODB.Connection")
  oConn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & strWorkbook & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=" & strHeaderYES_NO & """;"
  Set oRS = CreateObject("ADODB.Recordset")
  oRS.Open "SELECT * FROM [" & strRange, oConn, 2, 1
  With oRS
    .MoveLast
    lngRows = .RecordCount
    .MoveFirst
  End With
  fcnExcelDataToArray = oRS.GetRows(lngRows)
lbl_Exit:
  If oRS.State = 1 Then oRS.Close
  Set oRS = Nothing
  If oConn.State = 1 Then oConn.Close
  Set oConn = Nothing
  Exit Function
End Function
Attached Images
File Type: png Data Example.png (33.5 KB, 33 views)
Attached Files
File Type: docm Fill Array from Excel.docm (41.2 KB, 18 views)
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #4  
Old 07-31-2018, 10:21 AM
badarlodhi badarlodhi is offline Importing content control data from excel and populate two fields on selected dropdown Windows 10 Importing content control data from excel and populate two fields on selected dropdown Office 2016
Novice
Importing content control data from excel and populate two fields on selected dropdown
 
Join Date: Jul 2018
Posts: 4
badarlodhi is on a distinguished road
Default

Hi Paul,

I am unable to populate the cc value as there is error coming for duplicate values. My data contains name, department and Phone and many persons belongs to one department.
Reply With Quote
  #5  
Old 07-31-2018, 10:26 AM
badarlodhi badarlodhi is offline Importing content control data from excel and populate two fields on selected dropdown Windows 10 Importing content control data from excel and populate two fields on selected dropdown Office 2016
Novice
Importing content control data from excel and populate two fields on selected dropdown
 
Join Date: Jul 2018
Posts: 4
badarlodhi is on a distinguished road
Default

Hi Greg,

Thanks for your interest to help, when I am try with your file I am getting attached error

Attached Images
File Type: png error.PNG (35.8 KB, 30 views)
Reply With Quote
  #6  
Old 07-31-2018, 02:40 PM
macropod's Avatar
macropod macropod is offline Importing content control data from excel and populate two fields on selected dropdown Windows 7 64bit Importing content control data from excel and populate two fields on selected dropdown Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,493
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Quote:
Originally Posted by badarlodhi View Post
I am unable to populate the cc value as there is error coming for duplicate values.
That's not because of an error in the code; a dropdown content control cannot contain duplicates. You'll need to change the source data or the way the dropdown is populated so they can be differentiated.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 08-01-2018, 03:39 AM
badarlodhi badarlodhi is offline Importing content control data from excel and populate two fields on selected dropdown Windows 10 Importing content control data from excel and populate two fields on selected dropdown Office 2016
Novice
Importing content control data from excel and populate two fields on selected dropdown
 
Join Date: Jul 2018
Posts: 4
badarlodhi is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
You'll need to change the source data or the way the dropdown is populated so they can be differentiated.
Thanks Paul

Kindly clarify the other way to Populate the dropdown, my excel sheet contains more than 300 names for dropdown, ecah has his department and phone number my requirement is when I select a name in dropdown it should should populate the 2 text CC.

Thanks in advance
Reply With Quote
  #8  
Old 08-01-2018, 05:55 AM
macropod's Avatar
macropod macropod is offline Importing content control data from excel and populate two fields on selected dropdown Windows 7 64bit Importing content control data from excel and populate two fields on selected dropdown Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,493
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

You might, for instance, include the Dept. name as part of the text to be imported for both of the 'duplicates' (or even for all records); otherwise, even if duplicates were allowed, how would the user differentiate them?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing content control data from excel and populate two fields on selected dropdown Dropdown content control and tables hrzagi Word 1 12-17-2017 09:38 PM
Importing content control data from excel and populate two fields on selected dropdown Populate Content Control Dropdowns from Excel Deirdre Kelly Word VBA 23 09-07-2017 02:51 PM
Importing content control data from excel and populate two fields on selected dropdown Content Control Dropdown Dependencies asteinroeder Word VBA 5 10-28-2015 03:56 PM
Importing content control data from excel and populate two fields on selected dropdown Clicking the selected Content Control checkbox returns wrong control in vba event DougsGraphics Word VBA 2 06-24-2015 07:31 AM
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Faldinio Word VBA 7 10-19-2014 06:03 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 08:04 AM.


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