Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Excel to PDF or XPS boilerplate routines
I just finished writing 2 boilerplate routines (PrintTo_FixedFormat,
SaveAs_FixedFormat) for output to PDF or XPS fixed formats. I'm cross-posting them to 'misc' & 'programming' because that's where requests for how to do this task crop up quite often. Both routines do the same task in the following various ways: - print/export selected sheets, 1 file per sheet; - OR print/export a From/To range of sheets to 1 file; - OR print/export selected sheets (random grouping) to 1 file; - OR print/export all sheets to 1 file. - optionally specify a different path; - optionally time stamp the output file. Both routines have 'test' routines that demo all the details of how to use the boilerplate subs, and are constructed in such a way as to allow you to step through, step over, or comment out unwanted output configs. The PrintTo_FixedFormat routine works in all versions of Excel, and the SaveAs_FixedFormat checks version =12 before it runs. [General info about output files] I ran all the tests in XL2010 using the same file with the same sheet groupings (1,3), so output was always 2 sheets. (From/To setting was '1,2') All the XPS files and the PDF file created by PrintTo_FixedFormat were between 50kb and 53kb in size. The PDF created by SaveAs_FixedFormat was only 10kb. As I stated, all files were identical. That concludes, then, that the XPS Document Writer will only create XPS docs regardless of what file extension you give the file. Note also that ther are some PDF readers that don't/can't accept XPS format. If you truly want PDF PrintOuts then modify the code to use a PDF printer (if you have one) instead of the XPS Document Writer. Here's the code... Sub SaveAs_FixedFormat(FileType&, Filename$, Settings, _ Optional IsGroup As Boolean = False, _ Optional FromToRng, Optional StampIt As Boolean = True) ' Saves the following via ExportAsFixedFormat: ' Selected sheets, 1 file per sheet; ' Or a specified From/To group of sheets to 1 file, ' Or selected sheets (random grouping) to 1 file; ' Or an entire workbook to 1 file. ' ' ArgsIn: ' FileType& xlTypePDF (0) or xlTypeXSP (1) ' Filename$ Contains "<path\<wkbName" to which each wks.Name is appended ' Settings Array containing the settings for the export params ' IsGroup ! ' Dim wks As Worksheet, sExt$, sFile$, sTS$ If Application.VERSION < 12 Then Exit Sub sExt = IIf(FileType = 0, ".pdf", ".xps") '//always sTS = "_" & Format(Now(), "_dd-mm-yyyy_hh-mm_AMPM") '//always If Not IsGroup Then '//1 file per sheet For Each wks In ActiveWindow.SelectedSheets sFile = Filename & "_" & wks.name & IIf(StampIt, sTS & sExt, sExt) wks.ExportAsFixedFormat Type:=FileType, Filename:=sFile, _ Quality:=Settings(0), IncludeDocProperties:=Settings(1), _ IgnorePrintAreas:=Settings(2), OpenAfterPublish:=Settings(3) Next 'wks Else '//multiple sheets per file sFile = Filename & IIf(StampIt, sTS & sExt, sExt) If Not IsMissing(FromToRng) Then '//it's a group If Not LBound(FromToRng) = UBound(FromToRng) Then '//it's From/To ActiveWorkbook.ExportAsFixedFormat Type:=FileType, Filename:=sFile, _ Quality:=Settings(0), IncludeDocProperties:=Settings(1), _ IgnorePrintAreas:=Settings(2), OpenAfterPublish:=Settings(3), _ From:=FromToRng(0), To:=FromToRng(1) Else '//it's selected sheets (random grouping) 'ExportAsFixedFormat only works with workbooks/worksheets, 'so copy selected sheets to a new (temp) workbook, 'export it, then discard it. Application.ScreenUpdating = False '//hide activity ActiveWindow.SelectedSheets.Copy With ActiveWorkbook .ExportAsFixedFormat Type:=FileType, Filename:=sFile, _ Quality:=Settings(0), IncludeDocProperties:=Settings(1), _ IgnorePrintAreas:=Settings(2), OpenAfterPublish:=Settings(3) .Close SaveChanges:=False End With 'ActiveWorkbook Application.ScreenUpdating = True End If 'Not LBound(FromToRng) = UBound(FromToRng) Else '//all sheets ActiveWorkbook.ExportAsFixedFormat Type:=FileType, Filename:=sFile, _ Quality:=Settings(0), IncludeDocProperties:=Settings(1), _ IgnorePrintAreas:=Settings(2), OpenAfterPublish:=Settings(3) End If 'Not IsMissing(FromToRng) End If 'Not IsGroup End Sub 'SaveAs_FixedFormat Sub Test_SaveAs_FixedFormat() ' Shows the various ways to use the SaveAs_FixedFormat routine. ' How the values passed to it are assembled is up to you! ' This example's focus is on how to prep the args only. Dim sFile$, rng, vSettings Const lTypePDF& = 0: Const lTypeXPS& = 1 'ExportAsFixedFormat accepts the following ArgsIn: ' Quality: Standard=0, Minimum=1 (file size) ' IncludeDocProperties: False=0, True=1 ' IgnorePrintAreas: False=0, True=1 ' OpenAfterPublish: False=0, True=1 'We pass our preferences for these to SaveAs_FixedFormat as a variant array. vSettings = Split("0,0,0,0", ",") '//edit to suit '[Construct the Filename according to output path] 'NOTE: Do not include the filename extension 'when using the ExportAsFixedFormat feature. 'If output to ActiveWorkbook.Path, use '..................................... sFile = Split(ActiveWorkbook.FullName, ".")(0) 'Edit workbook ref to suit 'If output to a different path, use '.................................. 'Build sFile in logical steps sFile = "C:\Users\Garry\Documents\VBA_Stuff\" '//path 'Append the filename as per your requirements sFile = sFile & Split(ActiveWorkbook.name, ".")(0) 'Edit workbook ref to suit '[Specifying a range of sheets, or a selected sheets grouping] '................................................. ............ 'To Export StartWith/EndWith range of sheets, use rng = Split("1,2", ",") '//From=rng(0),To=rng(1) 'OR 'To Export a random grouping as selected while pressing 'Ctrl', use rng = Split("1", ",") '//makes LBound=UBound '[Exporting scenarios] 'To Export 1 file per selected sheet (random grouping) SaveAs_FixedFormat FileType:=lTypeXPS, Filename:=sFile, Settings:=vSettings 'OR SaveAs_FixedFormat FileType:=lTypePDF, Filename:=sFile, Settings:=vSettings 'To Export a From/To range of sheets to 1 file rng = Split("1,2", ",") SaveAs_FixedFormat FileType:=lTypeXPS, Filename:=sFile, _ Settings:=vSettings, IsGroup:=True, FromToRng:=rng 'OR SaveAs_FixedFormat FileType:=lTypePDF, Filename:=sFile, _ Settings:=vSettings, IsGroup:=True, FromToRng:=rng 'To Export selected sheets to 1 file (random grouping) rng = Split("1", ",") SaveAs_FixedFormat FileType:=lTypeXPS, Filename:=sFile, _ Settings:=vSettings, IsGroup:=True, FromToRng:=rng 'OR SaveAs_FixedFormat FileType:=lTypePDF, Filename:=sFile, _ Settings:=vSettings, IsGroup:=True, FromToRng:=rng 'To Export all sheets to 1 file SaveAs_FixedFormat FileType:=lTypeXPS, Filename:=sFile, _ Settings:=vSettings, IsGroup:=True 'OR SaveAs_FixedFormat FileType:=lTypePDF, Filename:=sFile, _ Settings:=vSettings, IsGroup:=True End Sub 'Test_SaveAs_FixedFormat Sub PrintTo_FixedFormat(FileType&, Filename$, Optional NumCopies& = 1, _ Optional IsGroup As Boolean = False, Optional FromToRng) ' Prints the following choices via the XPS Document Writer: ' Selected sheets, 1 file per sheet; ' Or a specified From/To range of sheets to 1 file, ' Or selected sheets (random grouping) to 1 file; ' Or an entire workbook to 1 file. ' ' ArgsIn: ' FileType& lTypePDF=0; lTypeXPS=1 ' Filename Contains "<path\<wkbName" ' NumCopies ! ' IsGroup ! ' Dim wks As Worksheet, sExt$, sFile$ 'Edit to use your 'actual' port address. Const sPrinter$ = "Microsoft XPS Document Writer on NE00:" 'To quickly find the port your XPS Document Writer uses, ' - change the printer in the Print dialog and close without printing; ' - in the VBE Immediate Window type the following, then press 'Enter'; ' ?activeprinter ' - reset the printer in the Print dialog to your default! sExt = IIf(FileType = 0, ".pdf", ".xps") If Not IsGroup Then '//1 file per sheet For Each wks In ActiveWindow.SelectedSheets sFile = Filename & "_" & wks.name & Format(Now(), "_dd-mm-yyyy_hh-mm_AMPM") & sExt wks.PrintOut ActivePrinter:=sPrinter, Copies:=NumCopies, _ PrintToFile:=True, PrToFileName:=sFile Next 'wks Else sFile = Filename & Format(Now(), "_dd-mm-yyyy_hh-mm_AMPM") & sExt If Not IsMissing(FromToRng) Then '//it's a range If Not LBound(FromToRng) = UBound(FromToRng) Then ActiveWorkbook.PrintOut From:=CLng(FromToRng(0)), To:=CLng(FromToRng(1)), _ ActivePrinter:=sPrinter, Copies:=NumCopies, PrintToFile:=True, PrToFileName:=sFile Else '//it's a random grouping ActiveWindow.SelectedSheets.PrintOut ActivePrinter:=sPrinter, _ Copies:=NumCopies, PrintToFile:=True, PrToFileName:=sFile End If 'Not LBound(FromToRng) = UBound(FromToRng) Else ActiveWorkbook.PrintOut ActivePrinter:=sPrinter, Copies:=NumCopies, _ PrintToFile:=True, PrToFileName:=sFile End If 'Not IsMissing(FromToRng) End If 'Not IsGroup End Sub 'PrintTo_FixedFormat Sub Test_PrintTo_FixedFormat() ' Shows the various ways to use the PrintTo_FixedFormat routine. ' How the values passed to it are assembled is up to you! ' This example's focus is on how to prep the args only. Dim sFile$, rng Const lTypePDF& = 0: Const lTypeXPS& = 1 '[Construct the Filename according to output path] 'NOTE: Do not include the filename extension 'when using an XPS Document Writer. 'If output to ActiveWorkbook.Path, use '..................................... sFile = Split(ActiveWorkbook.FullName, ".")(0) 'Edit workbook ref to suit 'If output to a different path, use '.................................. 'Build sFile in logical steps sFile = "C:\Users\Garry\Documents\VBA_Stuff\" '//path 'Append the filename as per your requirements sFile = sFile & Split(ActiveWorkbook.name, ".")(0) 'Edit workbook ref to suit '[Specifying a range of sheets, or a selected sheets grouping] '................................................. ............ 'To print StartWith/EndWith range of sheets, use rng = Split("1,2", ",") '//From=rng(0),To=rng(1) 'OR 'To print a random grouping as selected while pressing 'Ctrl', use rng = Split("1", ",") '//makes LBound=UBound '[Printing scenarios] 'To print 1 file per selected sheet (random grouping) PrintTo_FixedFormat FileType:=lTypeXPS, Filename:=sFile 'OR PrintTo_FixedFormat FileType:=lTypePDF, Filename:=sFile 'To print a From/To range of sheets to 1 file rng = Split("1,2", ",") PrintTo_FixedFormat FileType:=lTypeXPS, Filename:=sFile, IsGroup:=True, FromToRng:=rng 'OR PrintTo_FixedFormat FileType:=lTypePDF, Filename:=sFile, IsGroup:=True, FromToRng:=rng 'To print selected sheets to 1 file (random grouping) rng = Split("1", ",") PrintTo_FixedFormat FileType:=lTypeXPS, Filename:=sFile, IsGroup:=True, FromToRng:=rng 'OR PrintTo_FixedFormat FileType:=lTypePDF, Filename:=sFile, IsGroup:=True, FromToRng:=rng 'To print all sheets to 1 file PrintTo_FixedFormat FileType:=lTypeXPS, Filename:=sFile, IsGroup:=True 'OR PrintTo_FixedFormat FileType:=lTypePDF, Filename:=sFile, IsGroup:=True End Sub 'Test_PrintTo_FixedFormat -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Excel to PDF or XPS boilerplate routines
Oops! Looks like I forgot to copy over the PrintTo_FixedFormat routine
to my archive file... Sub PrintTo_FixedFormat(FileType&, Filename$, _ Optional NumCopies& = 1, _ Optional IsGroup As Boolean = False, _ Optional FromToRng, Optional StampIt As Boolean = True) ' Prints the following choices via the XPS Document Writer: ' Selected sheets, 1 file per sheet; ' Or a specified From/To range of sheets to 1 file, ' Or selected sheets (random grouping) to 1 file; ' Or an entire workbook to 1 file. ' ' ArgsIn: ' FileType& lTypePDF=0; lTypeXPS=1 ' Filename Contains "<path\<wkbName" ' NumCopies ! ' IsGroup ! ' Dim wks As Worksheet, sExt$, sFile$ 'Edit to use your 'actual' port address. Const sPrinter$ = "Microsoft XPS Document Writer on NE00:" 'To quickly find the port your XPS Document Writer uses, ' - change the printer in the Print dialog and close without printing; ' - in the VBE Immediate Window type the following, then press 'Enter'; ' ?activeprinter ' - reset the printer in the Print dialog to your default! sExt = IIf(FileType = 0, ".pdf", ".xps") If Not IsGroup Then '//1 file per sheet For Each wks In ActiveWindow.SelectedSheets sFile = Filename & "_" & wks.name & IIf(StampIt, sTS & sExt, sExt) wks.PrintOut ActivePrinter:=sPrinter, Copies:=NumCopies, _ PrintToFile:=True, PrToFileName:=sFile Next 'wks Else sFile = Filename & IIf(StampIt, sTS & sExt, sExt) If Not IsMissing(FromToRng) Then '//it's a range If Not LBound(FromToRng) = UBound(FromToRng) Then ActiveWorkbook.PrintOut From:=CLng(FromToRng(0)), To:=CLng(FromToRng(1)), _ ActivePrinter:=sPrinter, Copies:=NumCopies, PrintToFile:=True, PrToFileName:=sFile Else '//it's a random grouping ActiveWindow.SelectedSheets.PrintOut ActivePrinter:=sPrinter, _ Copies:=NumCopies, PrintToFile:=True, PrToFileName:=sFile End If 'Not LBound(FromToRng) = UBound(FromToRng) Else ActiveWorkbook.PrintOut ActivePrinter:=sPrinter, Copies:=NumCopies, _ PrintToFile:=True, PrToFileName:=sFile End If 'Not IsMissing(FromToRng) End If 'Not IsGroup End Sub 'PrintTo_FixedFormat -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Excel to PDF/XPS boilerplate routines: tweaked
After some sleep, tweaking, and including switch to PDF printer in
PrintTo_FixedFormat, here's my final versions of both routines... Sub PrintTo_FixedFormat(FileType&, Filename$, _ Optional NumCopies& = 1, Optional FromToRng, _ Optional IsGroup As Boolean = False, _ Optional StampIt As Boolean = True) ' Prints the following choices via XPS Document Writer OR PDF printer, ' as specified by 'FileType': ' Selected sheets, 1 file per sheet; ' Or a specified From/To range of sheets to 1 file, ' Or selected sheets (random grouping) to 1 file; ' Or an entire workbook to 1 file. ' ' ArgsIn: ' FileType& lTypePDF=0; lTypeXPS=1 ' Filename Contains "<path\<wkbName" ' NumCopies ! ' IsGroup ! ' Dim wks As Worksheet, sExt$, sFile$, sStamp$ Dim sPrinter$, sDfltPrn$ Const sPrnXPS$ = "Microsoft XPS Document Writer on NE00:" Const sPrnPDF$ = "deskPDF on DDM:" 'To quickly find the port your 'FileType' printer uses, ' - change the printer in the Print dialog and close without printing; ' - in the VBE Immediate Window type the following, then press 'Enter'; ' ?activeprinter ' - reset the printer in the Print dialog to your default! 'Initialize essential vars sDfltPrn = Application.ActivePrinter '//reset when done sPrinter = IIf(FileType = 0, sPrnPDF, sPrnXPS) sExt = IIf(FileType = 0, ".pdf", ".xps") sStamp = "_" & Format(Now(), "dd-mm-yyyy_hh-mm_AMPM") If Not IsGroup Then '//1 file per sheet For Each wks In ActiveWindow.SelectedSheets sFile = Filename & "_" & wks.Name & IIf(StampIt, sStamp & sExt, sExt) wks.PrintOut ActivePrinter:=sPrinter, Copies:=NumCopies, _ PrintToFile:=True, PrToFileName:=sFile Next 'wks Else sFile = Filename & IIf(StampIt, sStamp & sExt, sExt) If Not IsMissing(FromToRng) Then '//it's a range If Not LBound(FromToRng) = UBound(FromToRng) Then ActiveWorkbook.PrintOut From:=CLng(FromToRng(0)), To:=CLng(FromToRng(1)), _ ActivePrinter:=sPrinter, Copies:=NumCopies, PrintToFile:=True, PrToFileName:=sFile Else '//it's a random grouping ActiveWindow.SelectedSheets.PrintOut ActivePrinter:=sPrinter, _ Copies:=NumCopies, PrintToFile:=True, PrToFileName:=sFile End If 'Not LBound(FromToRng) = UBound(FromToRng) Else ActiveWorkbook.PrintOut ActivePrinter:=sPrinter, Copies:=NumCopies, _ PrintToFile:=True, PrToFileName:=sFile End If 'Not IsMissing(FromToRng) End If 'Not IsGroup Application.ActivePrinter = sDfltPrn End Sub 'PrintTo_FixedFormat Sub SaveAs_FixedFormat(FileType&, Filename$, _ Settings, Optional FromToRng, _ Optional IsGroup As Boolean = False, _ Optional StampIt As Boolean = True) ' Saves the following via ExportAsFixedFormat: ' Selected sheets, 1 file per sheet; ' Or a specified From/To group of sheets to 1 file, ' Or selected sheets (random grouping) to 1 file; ' Or an entire workbook to 1 file. ' ' ArgsIn: ' FileType& xlTypePDF (0) or xlTypeXSP (1) ' Filename$ Contains "<path\<wkbName" to which each wks.Name is appended ' Settings Array containing the settings for the export params ' IsGroup ! ' Dim wks As Worksheet, sExt$, sFile$, sTS$ If Application.VERSION < 12 Then Exit Sub sExt = IIf(FileType = 0, ".pdf", ".xps") '//always sTS = "_" & Format(Now(), "_dd-mm-yyyy_hh-mm_AMPM") '//always If Not IsGroup Then '//1 file per sheet For Each wks In ActiveWindow.SelectedSheets sFile = Filename & "_" & wks.name & IIf(StampIt, sTS & sExt, sExt) wks.ExportAsFixedFormat Type:=FileType, Filename:=sFile, _ Quality:=Settings(0), IncludeDocProperties:=Settings(1), _ IgnorePrintAreas:=Settings(2), OpenAfterPublish:=Settings(3) Next 'wks Else '//multiple sheets per file sFile = Filename & IIf(StampIt, sTS & sExt, sExt) If Not IsMissing(FromToRng) Then '//it's a group If Not LBound(FromToRng) = UBound(FromToRng) Then '//it's From/To ActiveWorkbook.ExportAsFixedFormat Type:=FileType, Filename:=sFile, _ Quality:=Settings(0), IncludeDocProperties:=Settings(1), _ IgnorePrintAreas:=Settings(2), OpenAfterPublish:=Settings(3), _ From:=FromToRng(0), To:=FromToRng(1) Else '//it's selected sheets (random grouping) 'ExportAsFixedFormat only works with workbooks/worksheets, 'so copy selected sheets to a new (temp) workbook, 'export it, then discard it. Application.ScreenUpdating = False '//hide activity ActiveWindow.SelectedSheets.Copy With ActiveWorkbook .ExportAsFixedFormat Type:=FileType, Filename:=sFile, _ Quality:=Settings(0), IncludeDocProperties:=Settings(1), _ IgnorePrintAreas:=Settings(2), OpenAfterPublish:=Settings(3) .Close SaveChanges:=False End With 'ActiveWorkbook Application.ScreenUpdating = True End If 'Not LBound(FromToRng) = UBound(FromToRng) Else '//all sheets ActiveWorkbook.ExportAsFixedFormat Type:=FileType, Filename:=sFile, _ Quality:=Settings(0), IncludeDocProperties:=Settings(1), _ IgnorePrintAreas:=Settings(2), OpenAfterPublish:=Settings(3) End If 'Not IsMissing(FromToRng) End If 'Not IsGroup End Sub 'SaveAs_FixedFormat -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Excel to PDF or XPS boilerplate routines
Similar functions, including a few others can be found he
http://www.EXCELGAARD.dk/Lib/Range2/ CE "GS" wrote in message ... I just finished writing 2 boilerplate routines (PrintTo_FixedFormat, SaveAs_FixedFormat) for output to PDF or XPS fixed formats. I'm cross-posting them to 'misc' & 'programming' because that's where requests for how to do this task crop up quite often. Both routines do the same task in the following various ways: - print/export selected sheets, 1 file per sheet; - OR print/export a From/To range of sheets to 1 file; - OR print/export selected sheets (random grouping) to 1 file; - OR print/export all sheets to 1 file. - optionally specify a different path; - optionally time stamp the output file. Both routines have 'test' routines that demo all the details of how to use the boilerplate subs, and are constructed in such a way as to allow you to step through, step over, or comment out unwanted output configs. The PrintTo_FixedFormat routine works in all versions of Excel, and the SaveAs_FixedFormat checks version =12 before it runs. [General info about output files] I ran all the tests in XL2010 using the same file with the same sheet groupings (1,3), so output was always 2 sheets. (From/To setting was '1,2') All the XPS files and the PDF file created by PrintTo_FixedFormat were between 50kb and 53kb in size. The PDF created by SaveAs_FixedFormat was only 10kb. As I stated, all files were identical. That concludes, then, that the XPS Document Writer will only create XPS docs regardless of what file extension you give the file. Note also that ther are some PDF readers that don't/can't accept XPS format. If you truly want PDF PrintOuts then modify the code to use a PDF printer (if you have one) instead of the XPS Document Writer. Here's the code... Sub SaveAs_FixedFormat(FileType&, Filename$, Settings, _ Optional IsGroup As Boolean = False, _ Optional FromToRng, Optional StampIt As Boolean = True) ' Saves the following via ExportAsFixedFormat: ' Selected sheets, 1 file per sheet; ' Or a specified From/To group of sheets to 1 file, ' Or selected sheets (random grouping) to 1 file; ' Or an entire workbook to 1 file. ' ' ArgsIn: ' FileType& xlTypePDF (0) or xlTypeXSP (1) ' Filename$ Contains "<path\<wkbName" to which each wks.Name is appended ' Settings Array containing the settings for the export params ' IsGroup ! ' Dim wks As Worksheet, sExt$, sFile$, sTS$ If Application.VERSION < 12 Then Exit Sub sExt = IIf(FileType = 0, ".pdf", ".xps") '//always sTS = "_" & Format(Now(), "_dd-mm-yyyy_hh-mm_AMPM") '//always If Not IsGroup Then '//1 file per sheet For Each wks In ActiveWindow.SelectedSheets sFile = Filename & "_" & wks.name & IIf(StampIt, sTS & sExt, sExt) wks.ExportAsFixedFormat Type:=FileType, Filename:=sFile, _ Quality:=Settings(0), IncludeDocProperties:=Settings(1), _ IgnorePrintAreas:=Settings(2), OpenAfterPublish:=Settings(3) Next 'wks Else '//multiple sheets per file sFile = Filename & IIf(StampIt, sTS & sExt, sExt) If Not IsMissing(FromToRng) Then '//it's a group If Not LBound(FromToRng) = UBound(FromToRng) Then '//it's From/To ActiveWorkbook.ExportAsFixedFormat Type:=FileType, Filename:=sFile, _ Quality:=Settings(0), IncludeDocProperties:=Settings(1), _ IgnorePrintAreas:=Settings(2), OpenAfterPublish:=Settings(3), _ From:=FromToRng(0), To:=FromToRng(1) Else '//it's selected sheets (random grouping) 'ExportAsFixedFormat only works with workbooks/worksheets, 'so copy selected sheets to a new (temp) workbook, 'export it, then discard it. Application.ScreenUpdating = False '//hide activity ActiveWindow.SelectedSheets.Copy With ActiveWorkbook .ExportAsFixedFormat Type:=FileType, Filename:=sFile, _ Quality:=Settings(0), IncludeDocProperties:=Settings(1), _ IgnorePrintAreas:=Settings(2), OpenAfterPublish:=Settings(3) .Close SaveChanges:=False End With 'ActiveWorkbook Application.ScreenUpdating = True End If 'Not LBound(FromToRng) = UBound(FromToRng) Else '//all sheets ActiveWorkbook.ExportAsFixedFormat Type:=FileType, Filename:=sFile, _ Quality:=Settings(0), IncludeDocProperties:=Settings(1), _ IgnorePrintAreas:=Settings(2), OpenAfterPublish:=Settings(3) End If 'Not IsMissing(FromToRng) End If 'Not IsGroup End Sub 'SaveAs_FixedFormat Sub Test_SaveAs_FixedFormat() ' Shows the various ways to use the SaveAs_FixedFormat routine. ' How the values passed to it are assembled is up to you! ' This example's focus is on how to prep the args only. Dim sFile$, rng, vSettings Const lTypePDF& = 0: Const lTypeXPS& = 1 'ExportAsFixedFormat accepts the following ArgsIn: ' Quality: Standard=0, Minimum=1 (file size) ' IncludeDocProperties: False=0, True=1 ' IgnorePrintAreas: False=0, True=1 ' OpenAfterPublish: False=0, True=1 'We pass our preferences for these to SaveAs_FixedFormat as a variant array. vSettings = Split("0,0,0,0", ",") '//edit to suit '[Construct the Filename according to output path] 'NOTE: Do not include the filename extension 'when using the ExportAsFixedFormat feature. 'If output to ActiveWorkbook.Path, use '..................................... sFile = Split(ActiveWorkbook.FullName, ".")(0) 'Edit workbook ref to suit 'If output to a different path, use '.................................. 'Build sFile in logical steps sFile = "C:\Users\Garry\Documents\VBA_Stuff\" '//path 'Append the filename as per your requirements sFile = sFile & Split(ActiveWorkbook.name, ".")(0) 'Edit workbook ref to suit '[Specifying a range of sheets, or a selected sheets grouping] '................................................. ............ 'To Export StartWith/EndWith range of sheets, use rng = Split("1,2", ",") '//From=rng(0),To=rng(1) 'OR 'To Export a random grouping as selected while pressing 'Ctrl', use rng = Split("1", ",") '//makes LBound=UBound '[Exporting scenarios] 'To Export 1 file per selected sheet (random grouping) SaveAs_FixedFormat FileType:=lTypeXPS, Filename:=sFile, Settings:=vSettings 'OR SaveAs_FixedFormat FileType:=lTypePDF, Filename:=sFile, Settings:=vSettings 'To Export a From/To range of sheets to 1 file rng = Split("1,2", ",") SaveAs_FixedFormat FileType:=lTypeXPS, Filename:=sFile, _ Settings:=vSettings, IsGroup:=True, FromToRng:=rng 'OR SaveAs_FixedFormat FileType:=lTypePDF, Filename:=sFile, _ Settings:=vSettings, IsGroup:=True, FromToRng:=rng 'To Export selected sheets to 1 file (random grouping) rng = Split("1", ",") SaveAs_FixedFormat FileType:=lTypeXPS, Filename:=sFile, _ Settings:=vSettings, IsGroup:=True, FromToRng:=rng 'OR SaveAs_FixedFormat FileType:=lTypePDF, Filename:=sFile, _ Settings:=vSettings, IsGroup:=True, FromToRng:=rng 'To Export all sheets to 1 file SaveAs_FixedFormat FileType:=lTypeXPS, Filename:=sFile, _ Settings:=vSettings, IsGroup:=True 'OR SaveAs_FixedFormat FileType:=lTypePDF, Filename:=sFile, _ Settings:=vSettings, IsGroup:=True End Sub 'Test_SaveAs_FixedFormat Sub PrintTo_FixedFormat(FileType&, Filename$, Optional NumCopies& = 1, _ Optional IsGroup As Boolean = False, Optional FromToRng) ' Prints the following choices via the XPS Document Writer: ' Selected sheets, 1 file per sheet; ' Or a specified From/To range of sheets to 1 file, ' Or selected sheets (random grouping) to 1 file; ' Or an entire workbook to 1 file. ' ' ArgsIn: ' FileType& lTypePDF=0; lTypeXPS=1 ' Filename Contains "<path\<wkbName" ' NumCopies ! ' IsGroup ! ' Dim wks As Worksheet, sExt$, sFile$ 'Edit to use your 'actual' port address. Const sPrinter$ = "Microsoft XPS Document Writer on NE00:" 'To quickly find the port your XPS Document Writer uses, ' - change the printer in the Print dialog and close without printing; ' - in the VBE Immediate Window type the following, then press 'Enter'; ' ?activeprinter ' - reset the printer in the Print dialog to your default! sExt = IIf(FileType = 0, ".pdf", ".xps") If Not IsGroup Then '//1 file per sheet For Each wks In ActiveWindow.SelectedSheets sFile = Filename & "_" & wks.name & Format(Now(), "_dd-mm-yyyy_hh-mm_AMPM") & sExt wks.PrintOut ActivePrinter:=sPrinter, Copies:=NumCopies, _ PrintToFile:=True, PrToFileName:=sFile Next 'wks Else sFile = Filename & Format(Now(), "_dd-mm-yyyy_hh-mm_AMPM") & sExt If Not IsMissing(FromToRng) Then '//it's a range If Not LBound(FromToRng) = UBound(FromToRng) Then ActiveWorkbook.PrintOut From:=CLng(FromToRng(0)), To:=CLng(FromToRng(1)), _ ActivePrinter:=sPrinter, Copies:=NumCopies, PrintToFile:=True, PrToFileName:=sFile Else '//it's a random grouping ActiveWindow.SelectedSheets.PrintOut ActivePrinter:=sPrinter, _ Copies:=NumCopies, PrintToFile:=True, PrToFileName:=sFile End If 'Not LBound(FromToRng) = UBound(FromToRng) Else ActiveWorkbook.PrintOut ActivePrinter:=sPrinter, Copies:=NumCopies, _ PrintToFile:=True, PrToFileName:=sFile End If 'Not IsMissing(FromToRng) End If 'Not IsGroup End Sub 'PrintTo_FixedFormat Sub Test_PrintTo_FixedFormat() ' Shows the various ways to use the PrintTo_FixedFormat routine. ' How the values passed to it are assembled is up to you! ' This example's focus is on how to prep the args only. Dim sFile$, rng Const lTypePDF& = 0: Const lTypeXPS& = 1 '[Construct the Filename according to output path] 'NOTE: Do not include the filename extension 'when using an XPS Document Writer. 'If output to ActiveWorkbook.Path, use '..................................... sFile = Split(ActiveWorkbook.FullName, ".")(0) 'Edit workbook ref to suit 'If output to a different path, use '.................................. 'Build sFile in logical steps sFile = "C:\Users\Garry\Documents\VBA_Stuff\" '//path 'Append the filename as per your requirements sFile = sFile & Split(ActiveWorkbook.name, ".")(0) 'Edit workbook ref to suit '[Specifying a range of sheets, or a selected sheets grouping] '................................................. ............ 'To print StartWith/EndWith range of sheets, use rng = Split("1,2", ",") '//From=rng(0),To=rng(1) 'OR 'To print a random grouping as selected while pressing 'Ctrl', use rng = Split("1", ",") '//makes LBound=UBound '[Printing scenarios] 'To print 1 file per selected sheet (random grouping) PrintTo_FixedFormat FileType:=lTypeXPS, Filename:=sFile 'OR PrintTo_FixedFormat FileType:=lTypePDF, Filename:=sFile 'To print a From/To range of sheets to 1 file rng = Split("1,2", ",") PrintTo_FixedFormat FileType:=lTypeXPS, Filename:=sFile, IsGroup:=True, FromToRng:=rng 'OR PrintTo_FixedFormat FileType:=lTypePDF, Filename:=sFile, IsGroup:=True, FromToRng:=rng 'To print selected sheets to 1 file (random grouping) rng = Split("1", ",") PrintTo_FixedFormat FileType:=lTypeXPS, Filename:=sFile, IsGroup:=True, FromToRng:=rng 'OR PrintTo_FixedFormat FileType:=lTypePDF, Filename:=sFile, IsGroup:=True, FromToRng:=rng 'To print all sheets to 1 file PrintTo_FixedFormat FileType:=lTypeXPS, Filename:=sFile, IsGroup:=True 'OR PrintTo_FixedFormat FileType:=lTypePDF, Filename:=sFile, IsGroup:=True End Sub 'Test_PrintTo_FixedFormat -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com --- Denne e-mail er fri for virus og malware fordi avast! Antivirus beskyttelse er aktiveret. http://www.avast.com |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Excel to PDF or XPS boilerplate routines
Similar functions, including a few others can be found he
http://www.EXCELGAARD.dk/Lib/Range2/ Nice! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to include boilerplate "terms of sale" below range | Excel Programming | |||
Excel VBA Wookbook Routines | Excel Discussion (Misc queries) | |||
Excel VBA Wookbook Routines | Excel Programming | |||
Excel Statistical Functions and Routines | Excel Worksheet Functions | |||
Excel Statistical Functions and Routines | Excel Worksheet Functions |