'===================================================================================================

'##### массив через рекордсет из указанного файла EXCEL

Sub test_Fn_UnloadDataConnect()

aTemp = fun_UnloadDataConnect(sFile:="C:\Users\au11101\Downloads\DSTAR.xlsx", _

sShName:="Date D-STAR")

' aTemp = fun_UnloadDataConnect(sFile:="C:\Users\au11101\Downloads\DSTAR.xlsx", _

sShName:="Date D-STAR")

Stop

End Sub

'---------------------------------------------------------------------------------------------------

Function fun_UnloadDataConnect(ByVal sFile As String, _

ByVal sShName As String) As Variant

''-----------------------------------

''Для Excel 12.0

'connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + FileName + _

"; Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

''Для более ранних версий

'connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + _

"; Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

''-----------------------------------

Dim sCon As String ', sFile As String, sShName As String

Dim i, c, r

Dim rs As New ADODB.Recordset

Dim cn As New ADODB.Connection

Dim m As Variant

'sFile = "<path_to_folder_or_file>.xls"

'sFile = "<path_to_folder_or_file>.xlsx"

'sShName = "SuiviAr"

'sShName = "Prod_TCM"

m = Split(sFile, ".", -1, vbTextCompare)

sExt = m(UBound(m)): Erase m

t = Timer

With cn

Select Case sExt

Case "xls"

.Provider = "Microsoft.Jet.OLEDB.4.0"

.ConnectionString = "Data Source=" & sFile & ";" & "Extended Properties=Excel 8.0;"

Case "xlsx"

.Provider = "Microsoft.ACE.OLEDB.12.0"

.ConnectionString = "Data Source=" & sFile & ";" & "Extended Properties=Excel 12.0;"

End Select

.Open

End With

sCon = "select * from [" & sShName & "$]"

rs.Open sCon, cn, 3, 3

fun_UnloadDataConnect = rs.GetRows ': Stop

' Debug.Print UBound(aAr) & " - " & UBound(aAr, 2): Erase aAr

cn.Close

' rs.Close

Set rs = Nothing

Set cn = Nothing

Debug.Print Timer - t

End Function

'===================================================================================================