Fonction SHEETS

[johndoeww]

J'ai un petit souci dans la macro ci-dessous :

Dès que je modifie sheets par un autre nom de feuille, il me dit erreur d'exécution 1004 en surlignant : With Selection.QueryTable

Help !


Public ZtNomAS
Public ZtNomDSN
Public ZtAgence
Public ZtDateDeb
Public ZtDateFin
Public ZtCodeClt
Public ZtTemp




Sub Ext2()
' Macro enregistrée le 06/05/2003 par Jacky Colas modifiée par P Guillaumin
'
ZtNomAS = "FROM " & Range("NomAs") & ".EXPEDE EXPEDE"
ZtNomDSN = "ODBC;DSN=" & Range("NomDsn") & ";"
ZtAgence = "(EXPEDE.EXDCARJ=" & Range("CodeAgence") & ")"
ZtDateDeb = Year(Range("DateDeb")) & Right("00" & Month(Range("DateDeb")), 2) _
& Right("00" & Day(Range("DateDeb")), 2)
ZtDateDeb = "(EXPEDE.EXDDTNM>=" & ZtDateDeb & ")"
ZtDateFin = Year(Range("DateFin")) & Right("00" & Month(Range("DateFin")), 2) _
& Right("00" & Day(Range("DateFin")), 2)
ZtDateFin = "(EXPEDE.EXDDTNM<=" & ZtDateFin & ")"
'ZtCodeClt = "(EXPEDE.EXDCTDGDE=" & ("122999") & ")"

Sheets("Expeditions").Select
'Sheets("Hutchinson").Select
Range("$A$2").Select
With Selection.QueryTable
.Connection = ZtNomDSN
.Sql = Array( _
"SELECT EXPEDE.EXDDTNM, EXPEDE.EXDK2DGDE, EXPEDE.EXDCTDGDE, EXPEDE.EXDCYDGDE, EXPEDE.EXDCLDGDE, EXPEDE.EXDEGDGDE, EXPEDE.EXDNUNM, EXPEDE.EXDREDGDE, EXPEDE.EXDK2FM, EXPEDE.EXDCYFM, EXPEDE.EXDCP, EXPEDE.EXDCLFM, EXPEDE.EXDEGFM, EXPEDE.EX" _
, _
"DNBC0NM, EXPEDE.EXDSS, EXPEDE.EXDMHSRQY, EXPEDE.EXDMXSRQY, EXPEDE.EXDMHQZ, EXPEDE.EXDMXQZ, EXPEDE.EXDD6QY, EXPEDE.EXDNFQY, " _
, _
" EXPEDE.EXDNFQZ" & Chr(13) & "" & Chr(10) & ZtNomAS & Chr(13) & "" & Chr(10) & "WHER" _
, _
"E " & ZtAgence & " AND (EXPEDE.EXDJGH5='D') AND " & ZtDateDeb & " AND " & ZtDateFin & " AND " & "(EXPEDE.EXDCTDGDE=" & ("122999") & ")" & " O" _
, _
"R " & ZtAgence & " AND (EXPEDE.EXDJGH5='D') AND " & ZtDateDeb & " AND " & ZtDateFin & " AND " & "(EXPEDE.EXDCTDGDE=" & ("122550") & ")" & " O" _
, _
"R " & ZtAgence & " AND (EXPEDE.EXDJGH5='D') AND " & ZtDateDeb & " AND " & ZtDateFin & " AND " & "(EXPEDE.EXDCTDGDE=" & ("122530") & ")" & " O" _
, _
"R " & ZtAgence & " AND (EXPEDE.EXDJGH5='D') AND " & ZtDateDeb & " AND " & ZtDateFin & " AND " & "(EXPEDE.EXDCTDGDE=" & ("122528") & ")" & " " _
)
.Refresh False


End With


RetourMenu

End Sub

Sub RetourMenu()

'
Sheets("Menu").Select

End Sub