However, please note that Excel for Microsoft 365 may have different versions or updates across different users, which could potentially result in slight variations in setup of the print settings and margin settings are correct. The updated script should work in most versions of Excel, including Excel for Microsoft 365. If totalPages <= 1 And selectedRange.Width <= Then TotalPages = WorksheetFunction.Ceiling(selectedRange.Height /, 1) Here is an updated version of the script that should work with Excel for Microsoft 365: Sub PrintWorkbookToPDF() I hope this clarifies the explanation and this info and code helps Excel for Microsoft 365, you can try using the PageSetup property of the Worksheet object instead. If you have specific print settings, such as different paper sizes or margins, you may need to modify the script accordingly. Please note that the script assumes the default printer settings and page setup for the active sheet. Once the necessary adjustments are made, the script proceeds with saving the workbook as a PDF file. This helps in fitting the selected range onto a single page when it exceeds the page boundaries. This ensures that the content is scaled proportionally to fit within the page boundaries horizontally.īy adjusting the zoom level, the content will be scaled down to fit within the width of the page, and the height will adjust accordingly to maintain the aspect ratio. PageSetup.PaperWidth / selectedRange.Width. In such cases, the content's scale is adjusted to fit horizontally on one page by setting. If the totalPages is greater than 1, it means that the selected range cannot fit on a single page. In this updated version of the script, the key modification is calculating the totalPages required to fit the selected range. PageSetup.PaperWidth / selectedRange.Width If totalPages <= 1 And selectedRange.Width <=. ![]() TotalPages = WorksheetFunction.Ceiling(selectedRange.Height /. ' Calculate the number of pages required to fit the selected range Here is an updated version of the script: Sub PrintWorkbookToPDF() ![]() However, there are a few modifications maybe you can make to achieve the desired functionality of adjusting the content's scale to fit on one page if it exceeds the page boundaries. PDF file not saved."Įnd VBA script you provided seems to have the basic structure in place. With Application.FileDialog(msoFileDialogFolderPicker)įilename = InputBox("Enter the File Name", "Save PDF")įilePath = folderPath & "\" & Filename & ".pdf"ĪctiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath, Quality:=xlQualityStandard ' Prompt the user to select the folder and enter the file name FitToPagesTall = False ' Remove this line to let the content scale vertically as necessary ' If the selected range doesn't fit on one page, adjust the content's scale to fit on one page ' If the selected range fits on one page, use the default settings PageSetup.PaperHeight And selectedRange.Width <=. ' Check if the selected range fits on one page ' Set the print area to the selected range FooterMargin = Application.InchesToPoints(0.5) HeaderMargin = Application.InchesToPoints(0.5) RightMargin = Application.InchesToPoints(0.75) LeftMargin = Application.InchesToPoints(0.75) BottomMargin = Application.InchesToPoints(0.75) TopMargin = Application.InchesToPoints(0.75) Orientation = xlLandscape ' Set orientation to landscape CenterVertically = True ' Center vertically on page CenterHorizontally = True ' Center horizontally on page ![]() PrintQuality = 2400 ' Set print quality to 2400 dpi PaperSize = xlPaperLetter ' Set paper size to 8.5x11 inches Thanks for your time and support in advance. BTW, I really don't care about how big or small the contents will be, one requirement is the output paper size shall be always as 11.0x8.5 (letter). I tried a while debugging but still couldn't find out the best solution. If it does, I want all selected contents adjust their scale/size automatically and print onto one page it it doesn't, all contents shall be output and fit onto one page as well. What I am expecting the script does is detecting if user selected area beyond one page. However, I am always having bugs in the if statement. I am currently set up a VBA script to print out user selected area and save it in pdf format.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |