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.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Documents and Settings\archith\Desktop\Club Profile\" & ClubNumber & ".pdf", Quality:= _
xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

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, 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.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Documents and Settings\archith\Desktop\Club Profile\" & ClubNumber & ".pdf", Quality:= _
xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

On Friday, August 31, 2012 9:29:54 AM UTC-5, wrote:
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. ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\Documents and Settings\archith\Desktop\Club Profile\" & ClubNumber & ".pdf", Quality:= _ xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, _ OpenAfterPublish:=False

Hi,
Thank you very much for the response.
I tested the code, but getting the user defined Type not defined error message in this line.

Dim pdfc As PDFCreator.clsPDFCreator

Do I need to add any of the library?

Thanks,
Anu

yes, you must add référence to PDFCreator

http://www.pdfforge.org/download


--
isabelle



Le 2012-09-04 08:00, a écrit :

Hi,
Thank you very much for the response.
I tested the code, but getting the user defined Type not defined error message in this line.

Dim pdfc As PDFCreator.clsPDFCreator

Do I need to add any of the library?

Thanks,
Anu