1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
|
Sub test()
Dim Requete As String, Conn As String, Sh As Worksheet
Set Sh = Sheets("Feuil1")
'La chaîne de connexion
Conn = "ODBC;"
Conn = Conn & "DSN=maDSN;"
Conn = Conn & "DATABASE=bdd;"
Conn = Conn & "SERVER=monServeur;"
Conn = Conn & "SRVR=monSrvr;"
'la chaîne de la requête
Requete = "SELECT fzaf.cdsoc, fzaf.noaff, fzre.dtecr, fzre.cdpha, fzre.nosec, fzre.nocpg, fzre.cdnat, fzre.cdmvt, fzre.vlqte, fzre.vlmnt, fzre.cdori, fzea.cdeta" & _
"FROM fzre, f_no, fzaf, fzea" & _
"WHERE fzno.noneu = fzre.noneu" & _
"AND fzaf.sraff = fzre.sraff" & _
"AND fzea .cdsoc = fzaf.cdsoc" & _
"AND fzea .cdeti = fzno .cdeta" & _
"AND fzaf.cdsoc='maSoc'" & _
"AND dtecr >= '20100301'" & _
"AND dtecr <= '20100331'" & _
"AND nosec='1000'" & _
"AND cdnat<>'NP' ORDER BY 1, 5, 6, 2; "
With Sh.QueryTables.Add(Connection:=Array(Conn), Destination:=Sh.Range("A1"))
Sh.Range("A1").CurrentRegion.ClearContents
.CommandText = Array(Requete)
.Name = "xxxx"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub |
Partager