bjr j'aimerai que ma macro s'execute une fois par semaine automatiquement jai vu certaint forum parler de tache palnifie mais comment faire
je ne voeux pas que cette macro s'execute automatiquement apres chaques ouverture mais qu'il s'execute une fois par semaine merci
voici mon script
Macro1 Macro
' Macro enregistrée le 28/05/2009 par RESA3
'
'
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=D:\stat\stat05.mdb;Mode=Share Deny Write;Extended Propert" _
, _
"ies="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:" _
, _
"Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password" _
, _
"="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLE" _
, "DB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination _
:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("inscription")
.Name = "stat05"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "D:\stat\stat05.mdb"
.Refresh BackgroundQuery:=False
End With
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("K:K").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("N:N").Select
Selection.Delete Shift:=xlToLeft
Columns("O:O").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
Columns("Q:Q").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
Columns("P").Select
Selection.Delete Shift:=xlToLeft
Columns("O:O").Select
Selection.Delete Shift:=xlToLeft
Columns("N:N").Select
Selection.NumberFormat = "#,##0 $"
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Rows("1:1").Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
Selection.AutoFilter Field:=13, Criteria1:="A"
ActiveWindow.SmallScroll Down:=243
ActiveWindow.ScrollRow = 8161
ActiveWindow.ScrollRow = 8177
ActiveWindow.ScrollRow = 8224
ActiveWindow.ScrollRow = 8255
ActiveWindow.ScrollRow = 8318
ActiveWindow.ScrollRow = 8412
ActiveWindow.ScrollRow = 8490
ActiveWindow.ScrollRow = 8584
ActiveWindow.ScrollRow = 8678
ActiveWindow.ScrollRow = 8741
ActiveWindow.ScrollRow = 8788
ActiveWindow.ScrollRow = 8835
ActiveWindow.ScrollRow = 8851
ActiveWindow.ScrollRow = 8882
ActiveWindow.ScrollRow = 8929
ActiveWindow.ScrollRow = 9007
ActiveWindow.ScrollRow = 9070
ActiveWindow.ScrollRow = 9117
ActiveWindow.ScrollRow = 9195
ActiveWindow.ScrollRow = 9289
ActiveWindow.ScrollRow = 9336
ActiveWindow.ScrollRow = 9430
ActiveWindow.ScrollRow = 9508
ActiveWindow.ScrollRow = 9571
ActiveWindow.ScrollRow = 9649
ActiveWindow.ScrollRow = 9712
ActiveWindow.ScrollRow = 9759
ActiveWindow.ScrollRow = 9806
ActiveWindow.ScrollRow = 9853
ActiveWindow.ScrollRow = 9884
ActiveWindow.ScrollRow = 9931
ActiveWindow.ScrollRow = 9978
ActiveWindow.ScrollRow = 10010
ActiveWindow.ScrollRow = 10025
ActiveWindow.ScrollRow = 10072
ActiveWindow.ScrollRow = 10104
ActiveWindow.ScrollRow = 10135
ActiveWindow.ScrollRow = 10151
ActiveWindow.ScrollRow = 10166
ActiveWindow.ScrollRow = 10198
ActiveWindow.ScrollRow = 10213
ActiveWindow.ScrollRow = 10245
ActiveWindow.ScrollRow = 10292
ActiveWindow.ScrollRow = 12077
ActiveWindow.ScrollRow = 12108
ActiveWindow.ScrollRow = 12124
ActiveWindow.ScrollRow = 12140
ActiveWindow.ScrollRow = 12155
ActiveWindow.ScrollRow = 12171
ActiveWindow.ScrollRow = 12187
ActiveWindow.ScrollRow = 12218
ActiveWindow.ScrollRow = 12249
ActiveWindow.ScrollRow = 12265
ActiveWindow.ScrollRow = 12281
ActiveWindow.ScrollRow = 12296
ActiveWindow.ScrollRow = 12312
ActiveWindow.ScrollRow = 12328
ActiveWindow.ScrollRow = 12343
ActiveWindow.ScrollRow = 12359
ActiveWindow.ScrollRow = 12390
ActiveWindow.ScrollRow = 12406
ActiveWindow.SmallScroll Down:=36
Range("N12499").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-12497]C:R[-1]C)"
Range("N12499").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub
je ne voeux pas que cette macro s'execute automatiquement apres chaques ouverture mais qu'il s'execute une fois par semaine merci
voici mon script
Macro1 Macro
' Macro enregistrée le 28/05/2009 par RESA3
'
'
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=D:\stat\stat05.mdb;Mode=Share Deny Write;Extended Propert" _
, _
"ies="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:" _
, _
"Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password" _
, _
"="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLE" _
, "DB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination _
:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("inscription")
.Name = "stat05"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "D:\stat\stat05.mdb"
.Refresh BackgroundQuery:=False
End With
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("K:K").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("N:N").Select
Selection.Delete Shift:=xlToLeft
Columns("O:O").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
Columns("Q:Q").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
Columns("P").Select
Selection.Delete Shift:=xlToLeft
Columns("O:O").Select
Selection.Delete Shift:=xlToLeft
Columns("N:N").Select
Selection.NumberFormat = "#,##0 $"
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Rows("1:1").Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
Selection.AutoFilter Field:=13, Criteria1:="A"
ActiveWindow.SmallScroll Down:=243
ActiveWindow.ScrollRow = 8161
ActiveWindow.ScrollRow = 8177
ActiveWindow.ScrollRow = 8224
ActiveWindow.ScrollRow = 8255
ActiveWindow.ScrollRow = 8318
ActiveWindow.ScrollRow = 8412
ActiveWindow.ScrollRow = 8490
ActiveWindow.ScrollRow = 8584
ActiveWindow.ScrollRow = 8678
ActiveWindow.ScrollRow = 8741
ActiveWindow.ScrollRow = 8788
ActiveWindow.ScrollRow = 8835
ActiveWindow.ScrollRow = 8851
ActiveWindow.ScrollRow = 8882
ActiveWindow.ScrollRow = 8929
ActiveWindow.ScrollRow = 9007
ActiveWindow.ScrollRow = 9070
ActiveWindow.ScrollRow = 9117
ActiveWindow.ScrollRow = 9195
ActiveWindow.ScrollRow = 9289
ActiveWindow.ScrollRow = 9336
ActiveWindow.ScrollRow = 9430
ActiveWindow.ScrollRow = 9508
ActiveWindow.ScrollRow = 9571
ActiveWindow.ScrollRow = 9649
ActiveWindow.ScrollRow = 9712
ActiveWindow.ScrollRow = 9759
ActiveWindow.ScrollRow = 9806
ActiveWindow.ScrollRow = 9853
ActiveWindow.ScrollRow = 9884
ActiveWindow.ScrollRow = 9931
ActiveWindow.ScrollRow = 9978
ActiveWindow.ScrollRow = 10010
ActiveWindow.ScrollRow = 10025
ActiveWindow.ScrollRow = 10072
ActiveWindow.ScrollRow = 10104
ActiveWindow.ScrollRow = 10135
ActiveWindow.ScrollRow = 10151
ActiveWindow.ScrollRow = 10166
ActiveWindow.ScrollRow = 10198
ActiveWindow.ScrollRow = 10213
ActiveWindow.ScrollRow = 10245
ActiveWindow.ScrollRow = 10292
ActiveWindow.ScrollRow = 12077
ActiveWindow.ScrollRow = 12108
ActiveWindow.ScrollRow = 12124
ActiveWindow.ScrollRow = 12140
ActiveWindow.ScrollRow = 12155
ActiveWindow.ScrollRow = 12171
ActiveWindow.ScrollRow = 12187
ActiveWindow.ScrollRow = 12218
ActiveWindow.ScrollRow = 12249
ActiveWindow.ScrollRow = 12265
ActiveWindow.ScrollRow = 12281
ActiveWindow.ScrollRow = 12296
ActiveWindow.ScrollRow = 12312
ActiveWindow.ScrollRow = 12328
ActiveWindow.ScrollRow = 12343
ActiveWindow.ScrollRow = 12359
ActiveWindow.ScrollRow = 12390
ActiveWindow.ScrollRow = 12406
ActiveWindow.SmallScroll Down:=36
Range("N12499").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-12497]C:R[-1]C)"
Range("N12499").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub