[EXCEL] Afficher dans Excel le résultat d'une requête construite dynamiquement

  • Imprimer

Sub MaRequete()

Dim Date_Debut As Date

Dim dateprev As Date

Dim Date_Fin As Date

Dim PorteFeuille As String

Dim WK As Worksheet

   Set WK = Worksheets("Parametres")

   Date_Debut = CDate(WK.Range("A2"))

   Date_Fin = CDate(WK.Range("B2"))

  

  

   With ActiveWorkbook.Connections("MaRequete"). _

       ODBCConnection

       .BackgroundQuery = True

       .CommandText = Array("select * from facture where " _

       "date_fact>='" & Date_Debut & "' and date_fact<='" & Date_Fin & "'")

       .CommandType = xlCmdSql

       .Connection = _

       "ODBC;DSN=MonServeur;UID=user_1;Trusted_Connection=Yes;APP=Microsoft Office 2013;WSID=motDePasse;DATABASE=MaBase"

       .RefreshOnFileOpen = False

       .SavePassword = False

       .SourceConnectionFile = ""

       .SourceDataFile = ""

       .ServerCredentialsMethod = xlCredentialsMethodIntegrated

       .AlwaysUseConnectionFile = False

   End With

   With ActiveWorkbook.Connections("MaRequete")

       .Name = "MaRequete"

       .Description = ""

   End With

   ActiveWorkbook.Connections("MaRequete").Refresh

End Sub