Option Explicit
Sub transfert()
Dim myfile As String
Dim LRow As Long, sNom As String
sNom = Sheets(1).Range("F12") & " - " & Format(Sheets(1).Range("G6"), "dd mmm yyyy")
myfile = "D:\factures\" & sNom & ".pdf"
LRow = Sheets(2).Range("a" & Rows.Count).End(xlUp).Row + 1
Sheets(2).Range("a" & LRow).Value = Sheets(1).Range("G6").Value
Sheets(2).Range("b" & LRow).Value = Sheets(1).Range("F12").Value
Sheets(2).Range("c" & LRow).Value = Sheets(1).Range("F15").Value
Sheets(2).Range("d" & LRow).Value = Sheets(1).Range("G34").Value
Sheets(2).Hyperlinks.Add anchor:=Sheets(2).Range("e" & LRow), Address:=myfile, TextToDisplay:=myfile
'sauvegarder la facture en pdf
If NomFichierValide(sNom) Then
Sheets(1).ExportAsFixedFormat Type:=xlTypePDF, Filename:=myfile
Else
MsgBox "Nom de fichier invalide !", vbCritical + vbOKOnly, "Nom de fichier"
End If
End Sub
Private Function NomFichierValide(sChaine As String) As Boolean
Dim i As Long
Const CaracInterdits As String = """*/:<>?[\]|"
NomFichierValide = True
If Len(sChaine) = 0 Then
NomFichierValide = False
Exit Function
End If
For i = 1 To Len(CaracInterdits)
If InStr(sChaine, Mid$(CaracInterdits, i, 1)) > 0 Then
NomFichierValide = False
Exit Function
End If
Next i
End Function