Code:
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