Where to go from here?

gmaxey · #12 03-29-2016, 04:14 AM

Why are you physically opening the Excel file in the first place?

I have already shown you once how to populate a userform listbox without opening Excel and excluding the column headings (optional) which you ignored.

If you want to put the Excel column A content as the CC dropdown text and the column B content as the CC.Dropdown value then:

Code:

Sub AutoOpen()
Dim varData As Variant
Dim lngIndex As Long
Dim oCC As ContentControl
  varData = LoadFromExcel_ADODB("D:\Book1.xlsx", "Sheet1")
  Set oCC = ActiveDocument.ContentControls(4) 'A very poor method.
  'Use instead
  'Set oCC = ActiveDocument.ContentControls("#########") 'ID
  'or
  'Set oCC = ActiveDocument.SelectContentControlsByTitle("Title").Item(#)
  'Set oCC = ActiveDocument.SelectContentControlsByTag("Tag").Item(#)
  With oCC
    For lngIndex = .DropdownListEntries.Count To 2 Step -1
      .DropdownListEntries(lngIndex).Delete
    Next lngIndex
    For lngIndex = 0 To UBound(varData, 2)
      .DropdownListEntries.Add varData(0, lngIndex), varData(1, lngIndex)
    Next lngIndex
  End With
lbl_Exit:
  Exit Sub
End Sub
Function LoadFromExcel_ADODB(ByRef strSource As String, _
                              strRange As String, Optional bIsSheet As Boolean = True, _
                              Optional bSuppressHeadingRow As Boolean = False)
Dim oConn As Object
Dim oRecSet As Object
Dim strConnection As String
Dim lngCount As Long
  If bIsSheet Then
    strRange = strRange & "$]"
  Else
    strRange = strRange & "]"
  End If
  Set oConn = CreateObject("ADODB.Connection")
  If bSuppressHeadingRow Then
    'Suppress first row.
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & strSource & ";" & _
                  "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
  Else
    'No suppression.
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                   "Data Source=" & strSource & ";" & _
                   "Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
  End If
  oConn.Open ConnectionString:=strConnection
  Set oRecSet = CreateObject("ADODB.Recordset")
  'Read the data from the worksheet/range.
  oRecSet.Open "SELECT * FROM [" & strRange, oConn, 2, 1
  With oRecSet
    .MoveLast
    'Get count.
    lngCount = .RecordCount
    .MoveFirst
  End With
  LoadFromExcel_ADODB = oRecSet.GetRows(lngCount)
  'Cleanup
  If oRecSet.State = 1 Then oRecSet.Close
  Set oRecSet = Nothing
  If oConn.State = 1 Then oConn.Close
  Set oConn = Nothing
lbl_Exit:
  Exit Function
End Function