Private Sub CommandButton1_Click()
   With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=VFPOLEDB.1;Data Source=F:\SBT\TTN\TTN.DBC;Mode=Share Deny None;Extended Properties="""";User ID="""";Mask Password=False;Ca" _
        , _
        "che Authentication=False;Encrypt Password=False;Collating Sequence=RUSSIAN;DSN="""";DELETED=True;CODEPAGE=1251;MVCOUNT=16384;ENGIN" _
        , _
        "EBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5" _
        ), Destination:=Range("$I$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT Sp_prd.kp, Sp_prd.namp, Sp_prd.ed_izm, SUM(Ttnd.kol*(2*ttn.op-1)) kol, SUM(Ttnd.suman*(2*ttn.op-1)) suman, S" _
        , _
        "UM(Ttnd.snds*(2*ttn.op-1)) snds,SUM(Ttnd.suma*(2*ttn.op-1)) suma, NVL(kurs.kurs,1.00000) kurs, Ttnd.id_prd FROM  tt" _
        , _
        "n!ttn LEFT JOIN TTN!KURS ON  DTOS(Kurs.data)+Kurs.val = DTOS(Ttn.dato)+Ttn.val INNER JOIN ttn!ttnd  ON Ttnd.id_ttn " _
        , _
        "= ttn.id_ttn INNER JOIN ttn!sp_prd  ON  Sp_prd.id_prd = Ttnd.id_prd WHERE Ttn.dato BETWEEN {^2017-06-01} AND {^2017" _
        , _
        "-06-30} AND Sp_prd.prp = '1' AND ' ' $ UPPER(Sp_prd.namp) AND  Sp_prd.kp BETWEEN 0 AND 99999999 GROUP BY Sp_prd.kp," _
        , " Ttnd.id_prd,Sp_prd.namp, Sp_prd.ed_izm,8")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "C:\Documents and Settings\PETR1\Мои документы\Мои источники данных\OTGR_PR.odc"
        .ListObject.DisplayName = "Таблица_OTGR_PR"
        .Refresh BackgroundQuery:=False
    End With
    
End Sub

.CommandText = "... WHERE Ttn.dato BETWEEN ? AND ?"
Set param1 = .Parameters.Add("From_date", xlParamTypeDate)
param1.SetParam xlConstant, #1/1/2015#
Set param2 = .Parameters.Add("To_date", xlParamTypeDate)
param2.SetParam xlConstant, #1/1/2016#
...

       "= ttn.id_ttn INNER JOIN ttn!sp_prd  ON  Sp_prd.id_prd = Ttnd.id_prd WHERE Ttn.dato BETWEEN ? AND ?" _
        , _
        " AND Sp_prd.prp = '1' AND ' ' $ UPPER(Sp_prd.namp) AND  Sp_prd.kp BETWEEN 0 AND 99999999 GROUP BY Sp_prd.kp," _
        , " Ttnd.id_prd,Sp_prd.namp, Sp_prd.ed_izm,8")
         Set param1 = .Parameters.Add("From_date", xlParamTypeDate)
         param1.SetParam xlConstant, #1/1/2017#
         Set param2 = .Parameters.Add("To_date", xlParamTypeDate)
         param2.SetParam xlConstant, #31/1/2017#

param2.SetParam xlConstant, #1/31/2017#

        .CommandText = Array( _
        "SELECT Sp_prd.kp, Sp_prd.namp from ttn!sp_prd  where kp in (?,?) ")
         Set param1 = .Parameters.Add("kp1", xlParamTypeDecimal)
         param1.SetParam xlConstant, 7410
         Set param2 = .Parameters.Add("kp2", xlParamTypeDecimal)
         param2.SetParam xlConstant, 8230

        .CommandText = Array( _
        "SELECT Sp_prd.kp, Sp_prd.namp from ttn!sp_prd  where kp in (7410,8230) ")
         Set param1 = .Parameters.Add("kp1", xlParamTypeDecimal)
         param1.SetParam xlConstant, 7410
         Set param2 = .Parameters.Add("kp2", xlParamTypeDecimal)
         param2.SetParam xlConstant, 8230

Sub test()
Dim oCon, oCmd, oPar1, oPar2, oRS As Object
Set oCon = CreateObject("ADODB.Connection")
Set oCmd = CreateObject("ADODB.Command")

oCon.Open "Provider=VFPOLEDB.1;Data Source=C:\test.dbc;Collating Sequence=RUSSIAN;DELETED=True;CODEPAGE=1251;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5"

oCmd.ActiveConnection = oCon
oCmd.CommandText = "Select * From test1 WHERE some_date BETWEEN ? AND ?"
Set oPar1 = oCmd.CreateParameter("from", 133)
oPar1.Value = #1/15/2017#
oCmd.Parameters.Append (oPar1)
Set oPar2 = oCmd.CreateParameter("to", 133)
oPar2.Value = #2/15/2017#
oCmd.Parameters.Append (oPar2)

Set oRS = oCmd.Execute

ActiveSheet.ListObjects.Add(SourceType:=xlSrcQuery, Source:=oRS, _
Destination:=Range("$A$1")).QueryTable.Refresh

oRS.Close
oCon.Close
End Sub

Private Sub CommandButton1_Click()
  With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=VFPOLEDB.1;Data Source=F:\SBT\TTN\TTN.DBC;Mode=Share Deny None;Extended Properties="""";User ID="""";Mask Password=False;Ca" _
        , _
        "che Authentication=False;Encrypt Password=False;Collating Sequence=RUSSIAN;DSN="""";DELETED=True;CODEPAGE=1251;MVCOUNT=16384;ENGIN" _
        , _
        "EBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5" _
        ), Destination:=Range("$I$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array(Range("$A$2").Value)
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "C:\Documents and Settings\PETR1\Мои документы\Мои источники данных\OTGR_PR.odc"
        .ListObject.DisplayName = "Таблица_OTGR_PR"
        .Refresh BackgroundQuery:=False
    End With

End Sub

Private Sub CommandButton1_Click()
   Dim s0 As String
   s0 = "SELECT Ttn.dato, Ttn.ser, Ttn.ttn, Sp_part.kodkl,RTRIM(Sp_part.nam)+' '+RTRIM(Rn.nam)+' '+RTRIM(IIF(Obl.kod=7000,SPACE(LEN(Obl.nam)),Obl.nam))  nam,SP_PRD.NAMP,CPR.KOL,CPR.SUMA, S_val.nam val,SP_PRD.KP, Ttn.n_dow, Ttn.wyd,Ttn.val kodv, Ttn.id_ttn, Sp_part.k_ter,NVL(Kurs.kurs,1.00)  kurs, Ttn.op, Ttn.osn_otp,Sp_part.k_fond FROM (SELECT TTND.ID_TTN,ID_PRD,SUM(Ttnd.kol*(2*Sttn.op-1)) kol,SUM(Ttnd.suma*(2*Sttn.op-1)) suma FROM TTND INNER JOIN (SELECT ID_TTN,OP FROM TTN WHERE DATO BETWEEN CTOD('?') AND CTOD('?')) STTN ON STTN.ID_TTN=TTND.ID_TTN GROUP BY 1,2) CPR INNER JOIN TTN ON TTN.ID_TTN = CPR.ID_TTN INNER JOIN SP_PRD ON SP_PRD.ID_PRD = CPR.ID_PRD LEFT JOIN TTN!KURS ON  DTOS(Kurs.data)+Kurs.val = DTOS(Ttn.dato)+Ttn.val, ttn!s_val,ttn!sp_part,ttn!sp_ter Rn,ttn!sp_ter Obl WHERE  S_val.kod = Ttn.val AND  Ttn.id_pol = Sp_part.id_part  AND  Rn.kod = Sp_part.k_ter AND  Obl.kod = ROUND(Rn.kod,-3) ORDER BY 1,2,3"
   Dim s As String
   s = Replace(s0, "?", RTrim(Range("$C$7").Value), 1, 1)
   s = Replace(s, "?", RTrim(Range("$C$9").Value), 1, 1)
Rem   MsgBox s
    With ActiveSheet.ListObjects("Таблица_OTGR_PR")
        .Delete
    End With
  
  
 With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=VFPOLEDB.1;Data Source=F:\SBT\TTN\TTN.DBC;Mode=Share Deny None;Extended Properties="""";User ID="""";Mask Password=False;Ca" _
        , _
        "che Authentication=False;Encrypt Password=False;Collating Sequence=RUSSIAN;DSN="""";DELETED=True;CODEPAGE=1251;MVCOUNT=16384;ENGIN" _
        , _
        "EBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5" _
        ), Destination:=Range("$I$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = s
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Таблица_OTGR_PR"
        .Refresh BackgroundQuery:=False
    End With
  With ActiveSheet
    .Columns("I:I").NumberFormat = "dd/mm/yyyy;@"
    .Columns("L:L").NumberFormat = "@"
  End With


End Sub