Sub Sbor_dannih_1()
Dim Conn As New ADODB.Connection, this_wb As Workbook, Data As String
Set this_wb = ThisWorkbook
Data = this_wb.Sheets("Результат").Cells(1, 3)
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Extended Properties=""Excel 8.0; HDR=Yes;""; Data Source=" & this_wb.FullName
Sheets("Результат").Range("A2").CopyFromRecordset _
Conn.Execute("SELECT [Номер договора], SWITCH (Состояние='Закрыт' AND [Дата закрытия] >= #" & Format(Data, "mm-dd-yyyy") & "#, 'Работает'," _
& "Состояние='Закрыт' AND [Дата закрытия] < #" & Format(Data, "mm-dd-yyyy") & "# AND [№ договора продажи] IS NULL, 'Закрыт'," _
& "Состояние='Закрыт' AND [Дата закрытия] < #" & Format(Data, "mm-dd-yyyy") & "# AND [№ договора продажи] IS NOT NULL, 'Продан'," _
& "Состояние='Работает', 'Работает')" _
& "FROM (SELECT * FROM [Выгрузка$] WHERE Метка= 1)")
Conn.Close
End Sub

[№ договора продажи] IS NOT NULL