Option Explicit

Dim WithEvents mrstMain As ADODB.Recordset
Dim WithEvents mcnnMain As ADODB.Connection

'Модуль класса для работы с ini-файлами
Dim clsReadINI As clsINIDLL.clsINI

'Переменные для хранения значений свойств
Private sUDLFileName As String, sTable As String, sSQLFileName As String, sSQLFilePath As String

Const sSQLFileNameDefault = "{Имя файла}.sql", sTableDefault = "{Имя таблицы}"

Private Sub OpenRecordset()
On Error GoTo opRsErr
    If mcnnMain Is Nothing Then
        Set mcnnMain = New ADODB.Connection
        mcnnMain.CursorLocation = adUseClient
        mcnnMain.ConnectionString = sUDLFileName
        mcnnMain.Open
    End If
    If mrstMain Is Nothing Then
        Set mrstMain = New ADODB.Recordset
        mrstMain.Open Table, mcnnMain, adOpenKeyset, _
                        adLockOptimistic, adCmdTable
        If Not (mrstMain.BOF And mrstMain.EOF) Then mrstMain.MoveFirst
    End If
    Exit Sub
opRsErr:
    MsgBox Err.Description, vbOKOnly + vbExclamation, "Îøèáêà âûïîëíåíèÿ"
End Sub

Private Sub CloseRecordset()
On Error GoTo clsRsErr
    If mrstMain.State <> adStateClosed Then mrstMain.Close
    Set mrstMain = Nothing
    If mcnnMain.State <> adStateClosed Then mcnnMain.Close
    Set mcnnMain = Nothing
    Exit Sub
clsRsErr:
    MsgBox Err.Description, vbOKOnly + vbExclamation, "Îøèáêà âûïîëíåíèÿ"
End Sub

'Импорт данных в Excel
Private Sub cmdImpExcel_Click()
On Error GoTo err_import
Dim ImportExcel As impExcelDLL.impExcelClass
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim myQuery As String
    
    Set rs = New ADODB.Recordset 
    modOpenSQLFile.openSQLFile sSQLFilePath, sSQLFileName 'чтение данных (sql-запрос) из файла
    myQuery = sSQLFileContentFull
    
    rs.Open myQuery, mcnnMain

    'непосредственно импорт данных в Excel
    Set ImportExcel = New impExcelDLL.impExcelClass
    ImportExcel.Recordset = rs
    ImportExcel.ImportExcel

    Set rs = Nothing    
    Set cn = Nothing    
    Exit Sub
err_import:
    MsgBox Err.Description, vbOKOnly + vbExclamation, "Îøèáêà âûïîëíåíèÿ"
End Sub

Private Sub UserControl_GetDataMember(DataMember As String, Data As Object)
    If DataMember = "Main" Then
        Set Data = mrstMain
    Else
        MsgBox "DataMember" & DataMember & "не найден"
    End If
End Sub

    ''инициализация объекта
Private Sub UserControl_Initialize()
On Error GoTo err_ini
    InitProperties
    OpenRecordset
    DataMembers.Add "Main"
    Exit Sub
err_ini:
    Exit Sub
End Sub

    ''его уничтожение
Private Sub UserControl_Terminate()
    DataMembers.Clear
    CloseRecordset
End Sub

'Установка свойств объекта
    ''чтение
Public Property Get ConnectionString() As String
    ConnectionString = sUDLFileName
End Property
    ''запись
Public Property Let ConnectionString(ByVal s_NewFileName As String)
    sUDLFileName = s_NewFileName
    PropertyChanged ConnectionString
End Property

Public Property Get Table() As String
    Table = sTable
End Property

Public Property Let Table(ByVal s_NewTable As String)
    sTable = s_NewTable
    PropertyChanged Table
End Property

Public Property Get SQLFileName() As String
    SQLFileName = sSQLFileName
End Property

Public Property Let SQLFileName(ByVal s_NewSQLFileName As String)
    sSQLFileName = s_NewSQLFileName
    PropertyChanged SQLFileName
End Property

Public Property Get SQLFilePath() As String
    SQLFilePath = sSQLFilePath
End Property

Public Property Let SQLFilePath(ByVal s_NewSQLFilePath As String)
    sSQLFilePath = s_NewSQLFilePath
    PropertyChanged SQLFilePath
End Property

'Используем объект PropertyBag для:
    ''чтения свойств
Private Sub UserControl_ReadProperties(PropBag As PropertyBag)
    SQLFileName = PropBag.ReadProperty("SQLFileName", sSQLFileNameDefault)
    Table = PropBag.ReadProperty("Table", sTableDefault)
End Sub

    ''записи свойств
Private Sub UserControl_WriteProperties(PropBag As PropertyBag)
    PropBag.WriteProperty "SQLFileName", sSQLFileName, sSQLFileNameDefault
    PropBag.WriteProperty "Table", sTable, sTableDefault
End Sub

'Инициализация значений свойств
Private Sub InitProperties()
    Set clsReadINI = New clsINIDLL.clsINI
    clsReadINI.INIFileName = "C:\InLabControl\Program\InLabControl.ini"
    
    ConnectionString = clsReadINI.GetPrivateINIString("Options", "ConnectionString")
    SQLFilePath = clsReadINI.GetPrivateINIString("Options", "SQLFilePath")
End Sub

'"Первичная" инициализация свойств
Private Sub UserControl_InitProperties()
    SQLFileName = sSQLFileNameDefault
    Table = sTableDefault
End Sub