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 OLEDB😀atabase 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 OLEDB😀on'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 OLEDB😀atabase 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 OLEDB😀on'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