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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
|
' MAJ le 18 Aout 2014
'
' TEST AVEC PDF
'
' Traitement_Report_on_Critical_Fileds
'
' avec sauvegarde annee_mois_jour
'
'
Sub Macro14()
Dim Wb As Workbook
Dim RonCriFilds As Workbook
Dim LstTech As Workbook
Set Wb = ActiveWorkbook
Dim Derniere As Long
Dim feuille_courante As String
Dim classeur_actif As String
Dim Debutperiode As String
Dim Finperiode As String
Dim Message As String
Dim Pos1deb As Integer
Dim Pos2deb As Integer
Dim Pos3deb As Integer
Dim Pos1fin As Integer
Dim Pos2fin As Integer
Dim Pos3fin As Integer
'
MsgBox " Debut de l'execution Macro14 "
'
' Date du jour ( format US/ENG : aaa mm jj
'
Dim DateJourUS As String
DateJourUS = Format(Now, "yyyy_mm_dd")
'
' Application.ScreenUpdating = False pour empecher de voir defiler les ouvertures des feuilles Excel ( ne pas oublier de le remettre sur TRUE en fin de porgramme
Application.ScreenUpdating = False
'
Set RonCriFilds = Workbooks.Open(Filename:= _
Wb.Path & "\Report on Critical Fields.xlsx")
If Range("B2").Value Like "Report Start Date :*" Then
Pos1deb = InStr(Range("B2").Value, ":")
Pos2deb = InStr(Range("B2").Value, "/")
Pos3deb = InStr(Pos2deb + 1, Range("B2").Value, "/")
moisdeb = Mid(Range("B2").Value, Pos1deb + 1, Pos2deb - Pos1deb - 1)
jourdeb = Mid(Range("B2").Value, Pos2deb + 1, Pos3deb - Pos2deb - 1)
Anneedeb = Mid(Range("B2").Value, Pos3deb + 1, 4)
End If
If Range("D2").Value Like "End Date :*" Then
Pos1fin = InStr(Range("D2").Value, ":")
Pos2fin = InStr(Range("D2").Value, "/")
Pos3fin = InStr(Pos2fin + 1, Range("D2").Value, "/")
moisfin = Mid(Range("D2").Value, Pos1fin + 1, Pos2fin - Pos1fin - 1)
jourfin = Mid(Range("D2").Value, Pos2fin + 1, Pos3fin - Pos2fin - 1)
Anneefin = Mid(Range("D2").Value, Pos3fin + 1, 4)
End If
SaveClasseur RonCriFilds, Wb.Path & "\" & "Report on Critical Fields_" & DateJourUS & "_FRANCE_SPAIN" & " du " & Format(jourdeb, "00") & "-" & Format(moisdeb, "00") & " au " & Format(jourfin, "00") & "-" & Format(moisfin, "00") & ".xlsx"
RonCriFilds.Close False
'
' Export au format PDF
'
'
Active.Workbook(Wb.Path & "Report on Critical Fields.xlsx").Sheets("CI changes").Range("B2:J20").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Wb.Path & "\" & "Report on Critical Fields_" & DateJourUS & "_FRANCE_SPAIN" & " du " & Format(jourdeb, "00") & "-" & Format(moisdeb, "00") & " au " & Format(jourfin, "00") & "-" & Format(moisfin, "00") & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
' |
