You can pull data from an Excel workbook using ADODB:
Option Explicit
Const strWorkbook As String = "D:\Data Stores\Simple Data Sheet.xlsx"
Const strSheet As String = "Sheet1"
Sub Macro1()
Dim varData
varData = xlFillArray(strWorkbook, strSheet)
lbl_Exit:
Exit Sub
End Sub
Private Function xlFillArray(strWorkbook As String, _
strRange As String) As Variant
Dim RS As Object
Dim CN As Object
Dim lngRecs As Long
strRange = strRange & "$]"
Set CN = CreateObject("ADODB.Connection")
CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Set RS = CreateObject("ADODB.Recordset")
RS.Open "SELECT * FROM [" & strRange, CN, 2, 1
With RS
.MoveLast
lngRecs = .RecordCount
.MoveFirst
End With
xlFillArray = RS.GetRows(lngRecs)
If RS.State = 1 Then RS.Close
Set RS = Nothing
If CN.State = 1 Then CN.Close
Set CN = Nothing
lbl_Exit:
Exit Function
End Function