Option Explicit
Private Declare Function FindWindow& Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, ByVal lpWindowName As String)
Private Declare Function PostMessage& Lib "user32" Alias "PostMessageA" ( _
ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long)
Declare Function ShellExecute& Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long)
Private Const WM_CLOSE = &H10
Sub copiePDF1() 'code copie en PDF et classement
Dim sNomDossier As String
Dim sNomFichierPDF As String
Dim PDFFilename As String
Dim impression As String
Dim x
Dim Hdl&
Dim Rep&
Dim acrobat
sNomDossier = ThisWorkbook.Path
sNomFichierPDF = " Devis N° " & Feuil2.Range("b16") & " du " & Format(Feuil2.Range("b17"), " dd mmmm yyyy") & " " & Feuil2.Range("d4") & " " & Feuil2.Range("d6")
sNomDossier = ThisWorkbook.Path & "\test\" & Format(Feuil1.Range("a2"), "mmmm yyyy") & "\"
If Len(sNomFichierPDF) > 0 Then
If NomFichierValide(sNomFichierPDF) Then
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=sNomDossier & _
sNomFichierPDF & ".pdf" _
, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
impression = MsgBox("Le fichier PDF nommé " & sNomFichierPDF & " à bien été crée dans le répertoire " & sNomDossier & Chr(10) & "Voulez-vous imprimer le PDF ?", vbYesNo)
If impression = vbYes Then
PDFFilename = sNomDossier & sNomFichierPDF & ".pdf"
'Indiquer le chemin exacte pour Adobe Reader
acrobat = Shell("C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe", vbNormalFocus)
ShellExecute x, "print", PDFFilename, "", "", 1
Hdl& = FindWindow(vbNullString, "acrobat")
Rep& = PostMessage(Hdl&, WM_CLOSE, vbNull, vbNull)
Application.OnTime Now + TimeValue("00:00:05"), "ferme"
Else
Exit Sub
End If
Else
Feuil2.Range("b17").Select
MsgBox "Nom de fichier invalide", vbOKOnly + vbInformation, "Nom de Fichier"
End If
End If
End Sub
'suite code copie PDF1
Private Function NomFichierValide(sChaine As String) As Boolean
Dim i As Long
Const CaracInterdits As String = """*/:<>?[\]|"
NomFichierValide = True
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
Sub ferme()
SendKeys "^{q}", True
End Sub