MyTetra Share
Делитесь знаниями!
массив через рекордсет из указанного файла EXCEL
27.07.2018
13:11
Текстовые метки: vba,ado, подключение к EXCEL
Раздел: VBA - Array - Recordset

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

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

'

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

Function Fn_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 = "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

Fn_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

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

 
MyTetra Share v.0.52
Яндекс индекс цитирования