Install Fit to Visible in Pdf from excel vba

Question

I am trying to export Excel spreadsheets to pdf. Is there any way by which I can set the pdf property to fit to visible using VBA. Thanks

Below is the code snippet that I am using

 With wksSalesAndQuotaScoreCard '.PageSetup.LeftMargin=Application.InchesToPoints(0.7) .PageSetup.LeftMargin = Application.InchesToPoints(0.7) .PageSetup.RightMargin = Application.InchesToPoints(0.7) .PageSetup.TopMargin = Application.InchesToPoints(0.75) .PageSetup.BottomMargin = Application.InchesToPoints(0.75) .PageSetup.HeaderMargin = Application.InchesToPoints(0.3) .PageSetup.Orientation = xlLandscape .PageSetup.PrintTitleRows = "_SalesandQuotaScoreCardView" .PageSetup.CenterHorizontally = True .PageSetup.Order = xlDownThenOver .PageSetup.FitToPagesWide = 1 ''.PageSetup.Zoom = 50 Set rngSalesAndQuotaView = Range(.Shapes("_SalesandQuotaViewFrame").TopLeftCell.Offset(0, -1), .Shapes("_SalesandQuotaViewFrame").BottomRightCell.Offset(1, 0)) rngSalesAndQuotaView.Select End With wksScoreCardPayoutView.Select wksSalesAndQuotaScoreCard.Select False Selection.ExportAsFixedFormat xlTypePDF, IncludeDocProperties:=True, openafterpublish:=True wksCustomizeScoreCard.Activate 

Answer 1

Adobe has a set of codes that you can use when opening a document:

http://partners.adobe.com/public/developer/en/acrobat/PDFOpenParameters.pdf

Please note that they only work with an approved Adobe Reader and not all PDF readers will accept these commands.

You may need to have Acrobat to manually set the conditions for this answer in the Adobe help forums :

Regarding the OP: "How to set the default value for opening a whole PDF to a 100% increase in magnification?"

Using Acrobat Pro, run the batch process (sequence) / action to launch the Open Options sequence. This sequence allows you to set values ​​for all items in the Preview tab of the PDF document properties.

Regarding the properties of a PDF document> Initial View: Visit the open PDF Document Properties (Ctrl + D, Cmd + D). Click on the "Initial View" tab. The top panel (Layout and Zoom) allows you to configure:

Navigation Tab: Page Only | Bookmarks Bar and Page | Page Panel and Page | Application Panel and Page | Layers panel and page layout: Default | Single Page | Single Page Continuous | Two-level (facing) | Two-Up (Cover Page) | Two-sided continuous (cover page) Magnification: Default | Actual size | Suitable Page | Fit Width | Height height | Fit Visible | Percentage increase (25% to 6400%)

Non-default configuration options determine how the PDF is viewed first.

Settings for all values ​​in all panels in the Initial View can be set for one or more PDF files using a sequence of sequences / actions (Acrobat Pro required).

This functionality is used in a corporate environment where large collections of documents are deployed on an eLibrary LAN / WAN to provide a consistent initial presentation to end users.

It also provides a content author with a mechanism to create an initial look for their expanded content (for example, a PDF document collection distributed on optical media).

Regarding a typical initial default view for created PDF: when a PDF is created through Acrobat, the default PDF document. Initial view for Layout and magnification: - | Navigation: only page - | Page Layout: Default - | Increase: default - | Open on page: 1

Regarding the Open Parameters document - As long as this document contains a few command line parameters, the primary focus of the document is to use the URL to open the starting URL for the URL.

It should be noted that the document is specific to URLs, and not with respect to UNC links (links from LAN / WAN).

Answer 2

If I understand your request, you need your print area (because exporting to a PDF file is like printing) is configured both in width and in height. You have the operator .PageSetup.FitToPagesWide = 1 , now you also need .FitToPagesTall = 1 , so your code will look like this:

  With wksSalesAndQuotaScoreCard '.PageSetup.LeftMargin=Application.InchesToPoints(0.7) .PageSetup.LeftMargin = Application.InchesToPoints(0.7) .PageSetup.RightMargin = Application.InchesToPoints(0.7) .PageSetup.TopMargin = Application.InchesToPoints(0.75) .PageSetup.BottomMargin = Application.InchesToPoints(0.75) .PageSetup.HeaderMargin = Application.InchesToPoints(0.3) .PageSetup.Orientation = xlLandscape .PageSetup.PrintTitleRows = "_SalesandQuotaScoreCardView" .PageSetup.CenterHorizontally = True .PageSetup.Order = xlDownThenOver .PageSetup.FitToPagesWide = 1 .FitToPagesTall = 1 ''.PageSetup.Zoom = 50 Set rngSalesAndQuotaView = Range(.Shapes("_SalesandQuotaViewFrame").TopLeftCell.Offset(0, -1), .Shapes("_SalesandQuotaViewFrame").BottomRightCell.Offset(1, 0)) rngSalesAndQuotaView.Select End With 

If this is not a solution, explain what fittovisible for you.

Answer 3

The “zoom level” is not a document property for PDF files, but an application property. In most cases, what we can do is open the PDF file directly from the excel macro and set the desired zoom level using "SendKeys".

Check out the code snippet that works for me:

 Function PDFOpenFile(inPath As String, inPageNo As Long, inZoom As Variant) 'This function opens the PDF document with the specified 'Page number and zoom level 'The Page Number and Zoom Level are simulated through SendKeys method ' ************ Important ******************* ' When the macro runs, do not use the keyboard ' else the SenKeys may not function as desired ThisWorkbook.FollowHyperlink inPath, NewWindow:=True SendKeys ("^+N" & inPageNo & "~^" & inZoom), True End Function Sub MyDocument() ' After file name, the firt parameter is for page no, the second parameter is for zoom level PDFOpenFile "D:\MyFile.pdf", 3, 3 ' Zoom Level Details '0: Full Page '1: Zoom to 100% '2: Page Width '3: Fit visible '4: visible width / Reflow ' These zoom level are visible in acrobat reader. Go to "View > Zoom" and see the shortcut mentioned ' If the shortcuts are different for your case, you may modify the parameter passed as argument End Sub 

Answer 4

Printing options depend on the computer, because only the printer determines by default what the "print area" is. This is the same in Excel, Word, etc.

Thus, you cannot define it for all computers, unless all the computers that you are accessing the document are set to the same printer by default.

Answer 5

I printed a sheet using PDFCreator, with "no scaling" and with "a suitable sheet on one page." The difference between them was

 .Zoom = 100 

and

 .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 

I think this will be "printer dependent". When you simply save the PDF file, you do not get these options, but the results are also good.

 Range("A1:H34").Select Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\myfile.pdf", Quality:= _ xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _ OpenAfterPublish:=False 

I think a lot depends on how you create your PDF.

Answer 6

I think this is as a parameter of the print area, which must first be set to excel before exporting to PDF. Do this in the page layout first or inside the “C” block of your code above, and then run the code that is exported to PDF.