MyTetra Share
Делитесь знаниями!
рекордсет из закрытой книги
16.03.2019
23:43
Текстовые метки: Подключение к Excel, рекордсет из закрытой книги, рекордсет, Recordset
Раздел: !Закладки - VBA - Array - Recordset

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

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

'

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

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 = "D:\au11101\Downloads\WindowsApplication3_1\ILU-2017-05-11-TTS_CIRE-A.xls"

sFile = "I:\0ru_di\Peinture\TPM\07_QUALITE_PEINT\01_STATISTICS\07_SSAR.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

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

Прикрепленные файлы:
 
MyTetra Share v.0.52
Яндекс индекс цитирования