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
| Option Compare Text
Sub recuperer()
Dim Wb_Source As Workbook, Wb_Dest As Workbook
Dim i As Integer
Dim Rep_source As String, Rep_dest As String
Dim ArrWd, ArrWd2
Dim Date_Jour1 As String, Date_Jour2 As String
Dim j1 As Byte, j2 As Byte
If Weekday(Now) = 2 Then 'lundi
j1 = 3
j2 = 4
End If
If Weekday(Now) = 3 Then 'mardi
j1 = 1
j2 = 4
End If
If Weekday(Now) = 4 Or Weekday(Now) = 5 Or Weekday(Now) = 6 Then 'mercredi, jeudi, vendredi
j1 = 1
j2 = 2
End If
Date_Jour1 = Format(Now - j1, "yyyymmdd")
Date_Jour2 = Format(Now - j2, "yyyymmdd")
Rep_source = "C:\murex_ftp\reports\eod\accounting_reports\" & Date_Jour1 & "\"
Rep_dest = "G:\Back Office\EIB\Accounting reports\EFSF\"
ArrWd = Split(("efsf_books_bal;efsf_cashflow;EFSF_Security_Position;efsf_situation_report"), ";")
ArrWd2 = Split(("CASH Position;efsf cashflow;SECURITIES Position;efsf situation report"), ";")
Set Wb_Dest = Workbooks.Open(Rep_dest & "EFSF Accounting report_" & Date_Jour2 & ".xls") 'ouvre le classeur destination
Wb_Dest.SaveAs Rep_dest & "EFSF Accounting report_" & Date_Jour1 & ".xls" 'renomme le classeur destination
For i = 0 To UBound(ArrWd)
Set Wb_Source = Workbooks.Open(Rep_source & ArrWd(i) & "_" & Date_Jour1 & ".xls", , True) 'ouverture en lecture seule du classeur source
Wb_Source.Sheets(1).Cells.Copy Wb_Dest.Sheets(ArrWd2(i)).Range("a1") 'copier/coller
Wb_Source.Close False 'ferme le classeur source
Next i
Wb_Dest.Close True 'ferme le classeur destination avec enregistrement
End Sub |
Partager