VBA Excel and SQL


Sub Get_Data_from_SQL()
Dim sPath As String, strSQL As String, objCon As Object, objRS As Object
sPath = ThisWorkbook.FullName
Set objCon = CreateObject("ADODB.Connection")
objCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sPath & ";" & _
        "Extended Properties=""Excel 8.0;HDR=No"";"
  'tab - именованный диапазон откуда берутся данные
    strSQL = "SELECT tab.[F1] as [Контрагенты], tab.[F2] as [DD1], tab.[F3] as [DD2]  FROM tab WHERE tab.
[F2]>0  "

' или так:
' strSQL = "SELECT [A2:C15].[F1] as [Контрагенты], [A2:C15].[F2] as [DD1], [A2:C15].[F3] as [DD2]  FROM [A2:C15] WHERE [A2:C15].[F3]>0  "
    Set objRS = objCon.Execute(strSQL)
      
    Range("A20").CopyFromRecordset objRS
  
objCon.Close: Set objCon = Nothing
End Sub

Добавить комментарий

Ваш адрес email не будет опубликован.