How to export Sheet Object / Chart "CH01" into PDF file? Very Urgent..

[anilkumarmishra]

Dear All ,

I have following macro code through which I send emails with .xls attachment but recipients of the emails want to get attachments in .PDF at the place of .XLS files.

I have installed Bullzip PDF Printer & also aome other PDF Printers but unable to understand that where I have to change in the following macro codes.

I request you to kindly provide your support & suggest that what & where I have to change?

Chart (Object ID) is "CH01"

[neelamsaroha157]

Do you have QV publisher?

[Frank_Hartmann]

Did you set Bullzip Printer as your  Standardprinter?

Try to use the following code to generate the PDF with Bullzip and afterwards use the Outputfilepath of the generated PDF in your Emailmacro as attachment:

sub Export
 set docprop = ActiveDocument.GetProperties
 set ps = docprop.PrintSheetSettings
 ps.SheetPrintDrawBackGround = true
 ActiveDocument.SetProperties docprop

 tempFolder="C:\Users\Admin\Desktop\" 'EDIT Outputpath here
 reportName="TestDashboard"
 reportFile = tempFolder & reportName & day(date) & monthname(month(date))&".pdf"
 'msgbox reportFile
 PrintPDFWithBullZip reportFile, mergeFlag
 ActiveDocument.GetApplication.Sleep 5000
  ActiveDocument.GetSheetObject("TB01").print 'EDIT your Object ID here
 ActiveDocument.GetApplication.Sleep 5000
 msgbox "saved."
 end sub
 '===========================================================================
 Function printReportPDF(pdfOutputFile)
 Set WSHShell = CreateObject("WScript.Shell")
 WSHShell.RegWrite "HKCU\Software\QlikviewPDF\OutputFile", pdfOutputFile, "REG_SZ"
 WSHShell.RegWrite "HKCU\Software\QlikviewPDF\BypassSaveAs", "1", "REG_SZ"
 Set WSHShell = nothing
 End function 




FUNCTION PrintPDFWithBullZip (pdfOutputFile, mergeFlag)
    set obj = CreateObject("Bullzip.PDFSettings")
    obj.SetValue "Output" , pdfOutputFile
    obj.SetValue "ConfirmOverwrite", "no"
    obj.SetValue "ShowSaveAS", "never"
    obj.SetValue "ShowSettings", "never"
    obj.SetValue "ShowPDF", "no"
    obj.SetValue "RememberLastFileName", "no"
    obj.SetValue "RememberLastFolderName", "no"
    obj.SetValue "ShowProgressFinished", "no"
    obj.SetValue "ShowProgress", "no"
    if mergeFlag = true then 'merge multiple pages into 1 file
        obj.SetValue "mergefile", pdfOutputFile
    end if

    obj.WriteSettings True
END FUNCTION

hope this helps

[anilkumarmishra]

Dear Sir,

Good Morning.

Special thanks for immediate response.

this code is working fine but some columns of the object sheet "CH01" are printing in the next pages .

I want print all columns in a page &  if data are more that one page then it should print with all columns in next page.

Please help & suggest me that what code & where it should be added to print all columns with maximize on a single page.

Please also provide code to set the page size of PDF file in LANDSCAPE & PORTRAIT

With thanks & regards,

AKM

[Frank_Hartmann]

I guess you will need to decrease the fontsize of your chart unless all columns fit to one page.

If the rows exceed one page they will be progressed on the next page with all columns automatically.

I dont have the code to set Landscape or Portrait mode but you can go to bullzip printersettings and there will be the option to change into landscape mode.

hope this helps

[Frank_Hartmann]

Just figured out that you can set the Orientation by adding following line to the  FUNCTION PrintPDFWithBullZip (pdfOutputFile, mergeFlag):

obj.SetValue "Orientation", "landscape"

Also have a look here:

PDF Writer - Settings

hope this helps

[anilkumarmishra]

Special thanks for reply but please tell me that how I can print page no on every pages.

With regards,

AKM

[Frank_Hartmann]

Please make your question more precise.

I have no clue what you actually need.

If your table doesn´t fit to one page then decrease the fontsize of your QV Table untill the table fits to one page!

Perhaps attach a sample picture with messed up output

and a picture how the print should look like