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

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

'

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

Sub Подключение_к_Excel()

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

''Для 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

aAr = 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 Sub

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