Option Explicit
Sub AutoOpen()
Dim oFrm As UserForm1
  Set oFrm = New UserForm1
  LoadFromExcel_ADODB oFrm.ListBox1, "D:\Book1.xlsx", "Sheet1"
  oFrm.Show
End Sub
Sub LoadFromExcel_ADODB(ByRef oListPassed As Object, 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
  With oListPassed
    'Load the records into the columns of the named list/combo box.
    .ColumnCount = oRecSet.Fields.Count
    .Column = oRecSet.GetRows(lngCount)
  End With
  'Cleanup
  If oRecSet.State = 1 Then oRecSet.Close
  Set oRecSet = Nothing
  If oConn.State = 1 Then oConn.Close
  Set oConn = Nothing
lbl_Exit:
  Exit Sub
End Sub