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 76 77 78 79 80 81 82 83
| Option Explicit
Private Function GetPrinterWithPort(ByVal sPrinterName As String) As String
Dim Reg As Variant, oReg As Object, Str As Variant
Dim Ar() As Variant, RegValue As Variant
Const HKEY_CURRENT_USER = &H80000001
Set oReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
With oReg
.enumvalues HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Str, Ar
.getstringvalue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Reg, RegValue
.getstringvalue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", sPrinterName, RegValue
End With
GetPrinterWithPort = sPrinterName & " sur " & Mid$(RegValue, InStr(RegValue, ",") + 1)
End Function
Sub TstPdfCreator()
Dim sPrinter As String
Dim JobPDF As Object
Dim sNomPDF As String
Dim sCheminPDF As String
Dim Ar() As String, Cpt As Long, i As Long
sNomPDF = "Essai"
sCheminPDF = ThisWorkbook.Path & "\"
Set JobPDF = CreateObject("PDFCreator.clsPDFCreator")
JobPDF.cStart "/NoProcessingAtStartup"
sPrinter = GetPrinterWithPort("PDFCreator")
With JobPDF
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sCheminPDF
.cOption("AutosaveFilename") = sNomPDF
' 0,9,10 PDF,1 PNG,2 JPEG,3 BMP,4 PCX
' 5 TIFF,6 PS,7 EPS,8 TXT
' 11 PSD,12 PCL,13 RAW,14 SVG
.cOption("AutosaveFormat") = 0
.cOption("PDFGeneralAutorotate") = 0
.cClearCache
End With
' Pour n'imprimer que certaines feuilles du classeur
Cpt = 0
For i = 1 To ThisWorkbook.Sheets.Count
If Left$(ThisWorkbook.Sheets(i).Name, 6) = "Feuil3" Then
ReDim Preserve Ar(Cpt)
Ar(Cpt) = Sheets(i).Name
Cpt = Cpt + 1
End If
Next i
If Cpt = 0 Then
Set JobPDF = Nothing
Exit Sub
End If
Application.ScreenUpdating = False
Sheets(Ar).Select
Sheets(Ar).PrintOut copies:=1, ActivePrinter:=sPrinter
' Fichier dans la file d'attente
Do Until JobPDF.cCountOfPrintjobs = 1
DoEvents
Loop
' Démarrage Imprimante
JobPDF.cPrinterStop = False
' Attendre que la file d'attente soit vide
Do Until JobPDF.cCountOfPrintjobs = 0
DoEvents
Loop
JobPDF.cClose
Set JobPDF = Nothing
Erase Ar
' Resélectionner une feuille seulement
Worksheets(1).Select
Application.ScreenUpdating = True
End Sub |
Partager