Bonjour,
Voila j'ai déclaré une macro vba en Sub auto_open() dans un classeur xls.
Cette macro marche nickel ! lorsque je lance manuellement mon fichier, en double cliquant sur celui-ci moi même.
Par contre quand j'ouvre ce fichier à partir de mon script vbs ci-dessous, mon fichier s'ouvre, mais ma macro ne se lance pas en automatique.
Voila ce qui m'amène à vous poser la question suivante :
Que dois-je faire pour faire appel à ma macro à partir de mon vbs, pour que celle-ci s'execute en automatique à l'ouverture de mon fichier ?
Si vous aviez une autre idée (modification script VBA ..), merci de m'en faire part.
Voici mon petit bout de code VBS :
Set objExcel = CreateObject('Excel.Application')
Set objWorkbook = objExcel.Workbooks.Open('C:\\Fichier.xls')
objExcel.Visible = TRUE
et voici mon code VBA : (un peu à ralonge
)
Sub Macro1()
Sub auto_open()
Dim MyDate
Dim MyTime
Dim Sujet
MyTime = Time
MyDate = Date - 1
Nsemaine = Format(Date, 'ww', vbUseSystemDayOfWeek, vbFirstFourDays)
Dim NumeroJour As Integer
NumeroJour = Weekday(Now, vbMonday) - 1
Sujet = 'Envoi Automatique : Taux : ' & MyDate
If NumeroJour = 0 Then
NumeroJour = 7
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal' & Nsemaine - 1 & '.xls'
Sheets('samedi').Select
Range('D100').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Range('A3').Select
ActiveSheet.PasteSpecial
Range('A2').Select
ActiveCell.FormulaR1C1 = 'Taux'
Range('B1').Select
ActiveCell.FormulaR1C1 = MyDate
Range('A1').Select
ActiveCell.FormulaR1C1 = 'Journée du :'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal S' & Nsemaine - 1 & '.xls'
Sheets('samedi').Select
Range('D100').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('B3').Select
ActiveSheet.PasteSpecial
Range('B2').Select
ActiveCell.FormulaR1C1 = 'Taux S'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal OPPO' & Nsemaine & '.xls'
Sheets('dimanche').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('C3').Select
ActiveSheet.PasteSpecial
Range('C2').Select
ActiveCell.FormulaR1C1 = 'Taux OPPO'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal Assistance' & Nsemaine - 1 & '.xls'
Sheets('samedi').Select
Range('D99').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('D3').Select
ActiveSheet.PasteSpecial
Range('D2').Select
ActiveCell.FormulaR1C1 = 'Taux ATT'
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
If NumeroJour = 1 Then
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal ED V2_S' & Nsemaine & '.xls'
Sheets('lundi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Range('A3').Select
ActiveSheet.PasteSpecial
Range('A2').Select
ActiveCell.FormulaR1C1 = 'Taux ED'
Range('B1').Select
ActiveCell.FormulaR1C1 = MyDate
Range('A1').Select
ActiveCell.FormulaR1C1 = 'Journée du :'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal SAN V2_S' & Nsemaine & '.xls'
Sheets('lundi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('B3').Select
ActiveSheet.PasteSpecial
Range('B2').Select
ActiveCell.FormulaR1C1 = 'Taux SAN'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal OPPO V2_S' & Nsemaine & '.xls'
Sheets('lundi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('C3').Select
ActiveSheet.PasteSpecial
Range('C2').Select
ActiveCell.FormulaR1C1 = 'Taux OPPO'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal Assistance CE V2_S' & Nsemaine & '.xls'
Sheets('lundi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('D3').Select
ActiveSheet.PasteSpecial
Range('D2').Select
ActiveCell.FormulaR1C1 = 'Taux ATT'
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
If NumeroJour = 2 Then
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal ED V2_S' & Nsemaine & '.xls'
Sheets('mardi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Range('A3').Select
ActiveSheet.PasteSpecial
Range('A2').Select
ActiveCell.FormulaR1C1 = 'Taux ED'
Range('B1').Select
ActiveCell.FormulaR1C1 = MyDate
Range('A1').Select
ActiveCell.FormulaR1C1 = 'Journée du :'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal SAN V2_S' & Nsemaine & '.xls'
Sheets('mardi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('B3').Select
ActiveSheet.PasteSpecial
Range('B2').Select
ActiveCell.FormulaR1C1 = 'Taux SAN'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal OPPO V2_S' & Nsemaine & '.xls'
Sheets('mardi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('C3').Select
ActiveSheet.PasteSpecial
Range('C2').Select
ActiveCell.FormulaR1C1 = 'Taux OPPO'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal Assistance CE V2_S' & Nsemaine & '.xls'
Sheets('mardi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('D3').Select
ActiveSheet.PasteSpecial
Range('D2').Select
ActiveCell.FormulaR1C1 = 'Taux ATT'
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
If NumeroJour = 3 Then
ChDir 'C:\\Program Files\\StatsExcel2004\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal ED V2_S' & Nsemaine & '.xls'
Sheets('mercredi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Range('A3').Select
ActiveSheet.PasteSpecial
Range('A2').Select
ActiveCell.FormulaR1C1 = 'Taux ED'
Range('B1').Select
ActiveCell.FormulaR1C1 = MyDate
Range('A1').Select
ActiveCell.FormulaR1C1 = 'Journée du :'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal SAN V2_S' & Nsemaine & '.xls'
Sheets('mercredi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('B3').Select
ActiveSheet.PasteSpecial
Range('B2').Select
ActiveCell.FormulaR1C1 = 'Taux SAN'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal OPPO V2_S' & Nsemaine & '.xls'
Sheets('mercredi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('C3').Select
ActiveSheet.PasteSpecial
Range('C2').Select
ActiveCell.FormulaR1C1 = 'Taux OPPO'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal Assistance CE V2_S' & Nsemaine & '.xls'
Sheets('mercredi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('D3').Select
ActiveSheet.PasteSpecial
Range('D2').Select
ActiveCell.FormulaR1C1 = 'Taux ATT'
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
If NumeroJour = 4 Then
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal ED V2_S' & Nsemaine & '.xls'
Sheets('jeudi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Range('A3').Select
ActiveSheet.PasteSpecial
Range('A2').Select
ActiveCell.FormulaR1C1 = 'Taux EDC'
Range('B1').Select
ActiveCell.FormulaR1C1 = MyDate
Range('A1').Select
ActiveCell.FormulaR1C1 = 'Journée du :'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal SAN V2_S' & Nsemaine & '.xls'
Sheets('jeudi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('B3').Select
ActiveSheet.PasteSpecial
Range('B2').Select
ActiveCell.FormulaR1C1 = 'Taux SAN'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal OPPO V2_S' & Nsemaine & '.xls'
Sheets('jeudi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('C3').Select
ActiveSheet.PasteSpecial
Range('C2').Select
ActiveCell.FormulaR1C1 = 'Taux OPPO'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal Assistance CE V2_S' & Nsemaine & '.xls'
Sheets('jeudi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('D3').Select
ActiveSheet.PasteSpecial
Range('D2').Select
ActiveCell.FormulaR1C1 = 'Taux ATT'
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
If NumeroJour = 5 Then
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal ED V2_S' & Nsemaine & '.xls'
Sheets('vendredi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Range('A3').Select
ActiveSheet.PasteSpecial
Range('A2').Select
ActiveCell.FormulaR1C1 = 'Taux ED'
Range('B1').Select
ActiveCell.FormulaR1C1 = MyDate
Range('A1').Select
ActiveCell.FormulaR1C1 = 'Journée du :'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal SAN V2_S' & Nsemaine & '.xls'
Sheets('vendredi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('B3').Select
ActiveSheet.PasteSpecial
Range('B2').Select
ActiveCell.FormulaR1C1 = 'Taux SAN'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal OPPO V2_S' & Nsemaine & '.xls'
Sheets('vendredi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('C3').Select
ActiveSheet.PasteSpecial
Range('C2').Select
ActiveCell.FormulaR1C1 = 'Taux OPPO'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal Assistance CE V2_S' & Nsemaine & '.xls'
Sheets('vendredi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('D3').Select
ActiveSheet.PasteSpecial
Range('D2').Select
ActiveCell.FormulaR1C1 = 'Taux ATT GAB'
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
If NumeroJour = 6 Then
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal ED V2_S' & Nsemaine & '.xls'
Sheets('samedi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Range('A3').Select
ActiveSheet.PasteSpecial
Range('A2').Select
ActiveCell.FormulaR1C1 = 'Taux EDC'
Range('B1').Select
ActiveCell.FormulaR1C1 = MyDate
Range('A1').Select
ActiveCell.FormulaR1C1 = 'Journée du :'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal SAN V2_S' & Nsemaine & '.xls'
Sheets('samedi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('B3').Select
ActiveSheet.PasteSpecial
Range('B2').Select
ActiveCell.FormulaR1C1 = 'Taux SAN'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal OPPO V2_S' & Nsemaine & '.xls'
Sheets('samedi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
ChDir 'C:\\'
Workbooks.Open Filename:= _
'C:\\script\\TauxAcc.xls'
Cells(NumeroJour, 1).Select
Range('C3').Select
ActiveSheet.PasteSpecial
Range('C2').Select
ActiveCell.FormulaR1C1 = 'Taux OPPO'
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir 'C:\\classeurs'
Workbooks.Open Filename:= _
'C:\\Program Files\\Journal Assistance CE' & Nsemaine & '.xls'
Sheets('samedi').Select
Range('D21').Select
Selection.Copy
ActiveWorkbook.Close
End If
Application.Quit
End Sub
Merci d'avance pour votre aide.
NikoZozo