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
| Public Sub Contrôle()
Const ch_f = "C:\facture" ' chemin du répertoire facture
Const ch_t = "C:\transport" ' chemin du répertoire transport
Dim cel As Range, der As Long, fic As String
der = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For Each cel In ActiveSheet.Range("A2:A" & der).Cells
fic = ch_f & "\" & cel.Offset(0, 1) & ".pdf" ' facture
If Dir(fic) <> "" Then
ActiveSheet.Hyperlinks.Add Anchor:=cel.Offset(0, 6), _
Address:=fic, TextToDisplay:="voir_facture"
Else
With Sheets("erreurs")
der = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Cells(der, 1).Value = cel.Value
.Cells(der, 2).Value = "facture absente"
End With
End If
' idem transport
fic = ch_t & "\" & cel.Offset(0, 2) & ".pdf"
If Dir(fic) <> "" Then
ActiveSheet.Hyperlinks.Add Anchor:=cel.Offset(0, 7), _
Address:=fic, TextToDisplay:="voir_transport"
Else
With Sheets("erreurs")
der = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Cells(der, 1).Value = cel.Value
.Cells(der, 2).Value = "transport absent"
End With
End If
Next cel
Sheets("erreurs").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\erreurs.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True _
, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub |
Partager