hi,
'You must add référence to PDFCreator
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Const maxTime = 10 ' en secondes
Private Const sleepTime = 250 ' en millisecondes
Sub TestPDF() '
http://grenier.self-access.com/
Dim sPath As String, sFileName As String
sPath = "C:\" '--------------------adapt
sFileName = "test " & Now & " .pdf" '--------------------adapt
SaveAsPDF sFileName, sPath
End Sub
Public Sub SaveAsPDF( _
Optional ByVal strPDFName As String = "", _
Optional ByVal strDirectory As String = "")
Dim pdfc As PDFCreator.clsPDFCreator
Dim DefaultPrinter As String
Dim c As Long
Dim OutputFilename As String
Set pdfc = New clsPDFCreator
With pdfc
.cStart "/NoProcessingAtStartup"
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
If strDirectory = "" Then
strDirectory = Environ("USERPROFILE") & "\Mes documents\"
End If
.cOption("AutosaveDirectory") = strDirectory
.cOption("AutosaveFilename") = _
IIf(strPDFName = "", ActiveWorkbook.Name, strPDFName)
.cOption("AutosaveFormat") = 0
DefaultPrinter = .cDefaultPrinter
.cDefaultPrinter = "PDFCreator"
.cClearCache
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
.cPrinterStop = False
End With
c = 0
Do While (pdfc.cOutputFilename = "") And (c < (maxTime * 1000 / sleepTime))
c = c + 1
Sleep 200
Loop
OutputFilename = pdfc.cOutputFilename
With pdfc
.cDefaultPrinter = DefaultPrinter
Sleep 200
.cClose
End With
Sleep 2000
If OutputFilename = "" Then
MsgBox "Création du fichier PDF." & vbCrLf & vbCrLf & _
"Une erreur s'est produite : temps écoulé !", vbExclamation + vbSystemModal
End If
End Sub
--
isabelle
Le 2012-08-31 10:29, (e-mail address removed) a écrit :
I have an excel report that I wanted to PDF through the VBA code. But the file size is around 230KB
if I PDF the report through the VBA code. If I manually PDF the document, file size is like 120 KB.
Pls help me what do I need to fix in the below VBA code to PDF the report to smallest file size.
