Print as pdf button / VBA, but i need more compression (and automation)

[Gazz292]

I have 2 sheets that i need to save as a single PDF / print to PDF upto 48 times, they make train driving simulator timetables,
I update the times for each services timetable depending on a service number selected from a dropdown list (XLookup, then it offset all the times in the timetable by however many minutes or hours are relevant, and also changes the front cover with the service number and some random date and time generators)

I wanted a way to have a button i press that would save / print as PDF the 2 relevant sheets and use the number in a cell as the name for the saved file... i have this bit working with help from someone on reddit,

But then i wanted the macro/VBA script to advance the timetable to the next one via the dropdown list, then do the save as / print PDF thing again, this time with the now different number from the cell as the filename.

And to repeat this until it gets to the end of the dropdown list (so some timetables will be 24 in total, but some are 48)



I asked about this on reddit the other day, and was given some VBA code, it selects the relevant sheets, gets the service number and uses the ExportAsFixedFormat function.

but i've found out that doing it like this using the ExportAsFixedFormat function results in a much larger PDF size, i.e. if i print the 2 sheets manually using the print dialogue in excel, the resulting files will be around 55Kb in size (i use ClawPDF printer, as i like how it can remove all the metadata when saving)

So when using the ExportAsFixedFormat 'macro' i get either a ~240 Kb file, or if i use 'Quality:=xlQualityMinimum' in the VBA, it makes them ~130 Kb in size, the idea of the project is to load a folder of timetables onto a kindle type e-ink reader, so i really need the pdf files to be as small as possible (they are just text and lines, no images)

Below is what i am using now.

It is possible to have a VBA code thing that will save the 2 pages using the 'print to pdf' method, so it uses the PDF printer which compresses them it seems, but still automate using the number in a cell as the save as file name?

Sub Save_As_PDF_Using_Service_Number()
    Dim fPath As String
    Dim fName As String
    Dim wsStart As Worksheet
   
    'What folder to save in?
    fPath = "C:\Users\abcd\Documents\FolderName\411XX Timetables (VBA)\"
   
    'Note where we start at
    Set wsStart = ActiveSheet
   
    'Error check
    If Right(fPath, 1) <> Application.PathSeparator Then
        fPath = fPath & Application.PathSeparator
    End If
   
    'Where is the name for PDF?
    fName = ThisWorkbook.Worksheets("TimeTable_141XX").Range("T5").Value
   
    'Make the PDF
    Application.ScreenUpdating = False
    ThisWorkbook.Sheets(Array("Front_Cover", "TimeTable_141XX")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & fName, Quality:=xlQualityMinimum
    wsStart.Select
    Application.ScreenUpdating = True
End Sub

 

[John_w]

You want to avoid ExportAsFixedFormat function because it produces larger PDFs. The alternative is the PrintOut function, which prints on the active printer or a specifed printer name, either of which could be your ClawPDF 'Print to PDF' virtual printer. The problem is that normally these virtual printers prompt the user for an output file name and you want to avoid that. The solution is to add another ClawPDF printer which outputs to a fixed folder and file name, instead of prompting. The VBA code can print to this 2nd ClawPDF printer and copy the fixed output file to whatever file name you want.

I tried this by adding a new printer, named 'My Print to PDF' which uses the 'Microsoft Print to PDF' driver. The port name of this printer is the fixed file 'D:\Temp\Excel\PDF\Print.pdf'.

Here is my test code which prints 3 active sheets as a PDF using 'My Print to PDF':

Public Sub Print_Selected_Sheets()

    Dim printer As String
    
    printer = FindPrinter("My Print to PDF")            'prints to the fixed file D:\Temp\Excel\PDF\Print.pdf, using Microsoft Print to PDF driver

    If printer <> "" Then
        ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=printer
    Else
        MsgBox "Printer named 'My Print to PDF' not found"
    End If

End Sub


'Written: November 28, 2009
'Author:  Leith Ross
'Summary: Finds a printer by name and returns the printer name and port number.

Public Function FindPrinter(ByVal PrinterName As String) As String

    'This works with Windows 2000 and up
    
    Dim Arr As Variant
    Dim Device As Variant
    Dim Devices As Variant
    Dim printer As String
    Dim RegObj As Object
    Dim RegValue As String
    Const HKEY_CURRENT_USER = &H80000001
    
    Set RegObj = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    RegObj.enumvalues HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Devices, Arr
    
    For Each Device In Devices
        RegObj.getstringvalue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Device, RegValue
        printer = Device & " on " & Split(RegValue, ",")(1)
        If UCase(Device) = UCase(PrinterName) Then
            FindPrinter = printer
            Exit Function
        End If
    Next
      
End Function

 

[Gazz292]

I've figured that i can use the PrintOut method with as little as:

Sub PrintOut()

Worksheets.PrintOut To:=7, ActivePrinter:="clawPDF"

End Sub

This prints out the 7 'pages' that make up the front cover and timetable pages of the latest timetable i'm working on, so it saves them as one nice little ~57Kb PDF, compared to ~300Kb file using the ExportAsFixedFormat method.

BUT, my problems is i have to manually enter the filename when i save,
I can't seem to find a way to change the save as name when using the PrintOut method.


ClawPDF has the ability to use the data from a few different places as the save as title :

But i need to figure out how to change one of the entries in the list above that excel uses to the text in a cell which is to be the save name.

I'm thinking i may need to temporarily save the relevant pages to change the workbook name, print them out then delete those temporary 'worksheets/workbook' ?

 

[John_w]

BUT, my problems is i have to manually enter the filename when i save,
I can't seem to find a way to change the save as name when using the PrintOut method.

Have you tried adding another ClawPDF printer which outputs to a fixed PDF file name, instead of prompting?

These are the steps I followed in the Add Printer dialogue to create another Microsoft Print to PDF printer which prints to a fixed PDF file name.

Add a local printer or network printer with manual settings
Create a new port. Type of port: Local Pport
Port name. Type in C:\Temp\Excel\PDF\Print.pdf
Install the printer driver. For Microsoft Print to PDF, I had to click Windows Update and wait for the list of Manufacturers and Printers to be updated. I could then choose Manufacturer: Microsoft; Printers: Microsoft Print to PDF.
Which version of the driver do you want to use? Use the driver that is currently installed (recommended).
Type a printer name. Type in: 'My Print to PDF'.
Click 'Print a test page' button. Verify that the PDF output file, C:\Temp\Excel\PDF\Print.pdf, has been created.

 

[Gazz292]

ClawPDF has loads of options,

i can set the folder it saves everything in easily in one of its save options, it's just the name for the file,
im going to have ~450 timetables to print/save as pdf files, and each one has a different name (a number that is the trains service number, so 1435, 14147, 3543233, etc)

So i need a way to change something in excel to the text / number that is in a cell to be picked up by ClawPDF as a save as name,
But so far i can't find a way to change any of the 'tokens' that claw can read,

 

[John_w]

I decided to download and install clawPDF and see that it has an API (scripting interface) which can be called from VBA - Scripting Interface

There is also an example PowerShell script which saves an Excel sheet as a PDF - clawPDF/Excel2PDF.ps1 at master · clawsoftware/clawPDF

Based on the PS script, I wrote this VBA macro which saves the active sheet as a PDF file using the clawPDF API. The code uses early binding of the clawPDF objects so you must set a reference to 'clawPDF - Your Open Source PDF Solution', via Tools -> References in the VBA editor.

Public Sub Save_Sheet_As_PDF_Using_ClawPDF()

    'Define worksheet to be saved as a PDF - the active sheet
    Dim ws As Worksheet
    Set ws = ActiveSheet
   
    'Define full path for output PDF file
    Dim fullPath As String
    fullPath = ThisWorkbook.Path & "\Active Sheet.pdf"

    'Create clawPDF object
    Dim clawPDFQueue As clawPDF.Queue
    Set clawPDFQueue = New clawPDF.Queue

    'Initialize clawPDF job queue
    clawPDFQueue.Initialize
       
    'Print worksheet to clawPDF job queue
    'ws.PrintOut From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName, IgnorePrintAreas
    ws.PrintOut ActivePrinter:="clawPDF"

    'Wait for job to arrive at the queue
    Debug.Print Time; "Waiting for the job to arrive at the queue..."

    If Not clawPDFQueue.WaitForJob(10) Then
   
        Debug.Print Time; "The print job did not reach the queue within 10 seconds"
   
    Else
       
        Debug.Print Time; "Currently there are " & clawPDFQueue.Count & " job(s) in the queue"
        Debug.Print Time; "Getting job instance"
       
        'Get print job
        Dim printJob As clawPDF.printJob
        Set printJob = clawPDFQueue.NextJob
       
        'Set job profile
        printJob.SetProfileByGuid "DefaultGuid"
       
        'Set print job metadata and profile settings
        printJob.PrintJobInfo.PrintJobAuthor = ""
        printJob.PrintJobInfo.Subject = ""
        printJob.PrintJobInfo.PrintJobName = ""
        printJob.SetProfileSetting "OpenViewer", False
       
        'Convert job to PDF
        printJob.ConvertTo fullPath
       
        'Check if conversion was successful
        If Not printJob.IsFinished Or Not printJob.IsSuccessful Then
            Debug.Print Time; "Could not convert the file: " & fullPath
        Else
            Debug.Print Time; "Job finished successfully"
        End If
       
    End If
   
    'Release clawPDF object
    Debug.Print Time; "Releasing the object"
    clawPDFQueue.ReleaseCom
   
End Sub

The clawPDF API (scripting interface) means you don't need to bother with configuring its UI or my idea of adding a new clawPDF virtual printer. You should be able to extend the above macro to save 2 sheets as a single PDF.

 

[Gazz292]

Thankyou very much for sticking with this for me,

i really wish coding was easier for me, but it's a totally different language i just can't get my head around.

I've managed to add a couple of lines from the 'ExportAsFixedFormat' code i posted in my first post (that produces 300+Kb files), and i have ClawPDF saving/printing the timetable with the correct timetable name as the filename, at nice small ~57Kb files

The fPath and fName bits i added.

    'Define save path and name
    Dim fPath As String
    Dim fName As String

    'Define worksheet to be saved as a PDF - the active sheet
    Dim ws As Worksheet
    Set ws = ActiveSheet
   
    'Locations of save path and save name
    fPath = "C:\Users\abcd\Documents\SimRail Timetable Stuff\tests\"
    fName = ThisWorkbook.Worksheets("TimeTable").Range("Service").Value
   
    'Define full path for output PDF file
    Dim fullPath As String
    fullPath = fPath & fName

I just need to figure out the bit that sets it to print the front cover which is on another sheet in the same workbook, as well as the timetable.
that used to be done with:
"ThisWorkbook.Sheets(Array("Front_Cover", "Timetable")).Select" with the 'ExportAsFixedFormat' method,

But i need to figure out how / where to add that to the current code.

 

[Gazz292]

i got it working, i imagine i've done it a very convoluted way, and there are better ways?

The bits i changed:

Public Sub Save_Sheet_As_PDF_Using_ClawPDF()
        
    'Define save path and name
    Dim fPath As String
    Dim fName As String

    'Define worksheet to be saved as a PDF - the active sheet
    'Dim ws As Worksheet
    'Set ws = ActiveSheet
    
   
    'Locations of save path and save name
    fPath = "C:\Users\abcd\Documents\SimRail Timetable Stuff\tests\"
    fName = ThisWorkbook.Worksheets("TimeTable").Range("Service").Value
   
    'Define full path for output PDF file
    Dim fullPath As String
    fullPath = fPath & fName

    'Create clawPDF object
    Dim clawPDFQueue As clawPDF.Queue
    Set clawPDFQueue = New clawPDF.Queue

    'Initialize clawPDF job queue
    clawPDFQueue.Initialize
       
    'Print worksheet to clawPDF job queue
    'ws.PrintOut From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName, IgnorePrintAreas
    Worksheets.PrintOut To:=7, ActivePrinter:="clawPDF"

 

[Gazz292]

I've managed to combine some extra code i found to complete all that i needed to do (until i think of more features)...

Which is select each timetable from a dropdown box, (which updates formulas for a lot of entries in the timetable and front cover), then print each front cover and timetable as a single PDF using ClawPDF, resulting in ~60Kb file sizes.
So now a single press of a macro button produces 24 timetables, one each for the daily running of a single service (other timetables are half hourly, so will have 48 individual timetables, hence why i needed to automate this, there will be 100's of services eventually)
Each timetable is automatically saved with the correct file name, which is the service number as shown in the dropdown box cell, and saved into in the correct folder ... ready to load onto a kindle, which will be used to display the relevant timetable for the service being driven in the simulator (the multiplayer Polish train driving and signalling simulator, SimRail... if anyone's wondering)

The code:

Sub Select_Each_Timetable_in_Turn()

    'Define things
    Dim xRg As Range
    Dim xCell As Range
    Dim xRgVList As Range
    
    'Location of dropdown box
    Set xRg = Worksheets("TimeTable").Range("Service")
    Set xRgVList = Evaluate(xRg.Validation.Formula1)
    
    'Select the (next) dropdown entry
    For Each xCell In xRgVList
        xRg = xCell.Value
        
        'Print as PDF using module below
        Call Save_Sheet_As_PDF_Using_ClawPDF
        
    'Repeat until last dropdown entry
    Next
End Sub
______________________________________________________________________________________________________________________

Public Sub Save_Sheet_As_PDF_Using_ClawPDF()
        
    'Define save path and name
    Dim fPath As String
    Dim fName As String

    'Locations of save path and save name
    fPath = "C:\Users\gazz\Documents\SimRail Timetable Stuff\14100\"
    fName = ThisWorkbook.Worksheets("TimeTable").Range("Service").Value
   
    'Define full path for output PDF file
    Dim fullPath As String
    fullPath = fPath & fName

    'Create clawPDF object
    Dim clawPDFQueue As clawPDF.Queue
    Set clawPDFQueue = New clawPDF.Queue

    'Initialize clawPDF job queue
    clawPDFQueue.Initialize
       
    'Print worksheet to clawPDF job queue
    'ws.PrintOut From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName, IgnorePrintAreas
    Worksheets.PrintOut To:=7, ActivePrinter:="clawPDF"

    'Wait for job to arrive at the queue
    Debug.Print Time; "Waiting for the job to arrive at the queue..."

    If Not clawPDFQueue.WaitForJob(10) Then
   
        Debug.Print Time; "The print job did not reach the queue within 10 seconds"
   
    Else
       
        Debug.Print Time; "Currently there are " & clawPDFQueue.Count & " job(s) in the queue"
        Debug.Print Time; "Getting job instance"
       
        'Get print job
        Dim printJob As clawPDF.printJob
        Set printJob = clawPDFQueue.NextJob
       
        'Set job profile
        printJob.SetProfileByGuid "DefaultGuid"
       
        'Set print job metadata and profile settings
        printJob.PrintJobInfo.PrintJobAuthor = ""
        printJob.PrintJobInfo.Subject = ""
        printJob.PrintJobInfo.PrintJobName = ""
        printJob.SetProfileSetting "OpenViewer", False
       
        'Convert job to PDF
        printJob.ConvertTo fullPath
       
        'Check if conversion was successful
        If Not printJob.IsFinished Or Not printJob.IsSuccessful Then
            Debug.Print Time; "Could not convert the file: " & fullPath
        Else
            Debug.Print Time; "Job finished successfully"
        End If
       
    End If
   
    'Release clawPDF object
    Debug.Print Time; "Releasing the object"
    clawPDFQueue.ReleaseCom
   
End Sub

And a sample timetable, i took screenshots of the PDF timetable's pages, and pasted them above each other to make up the whole route to save as a jpg file, you would only view one page at a time on the kindle, which is how the real paper timetables are viewed.



Big thanks to John_w,
Without his help i'd still be stuck manually pressing print and typing in the service numbers... and getting some mixed up, as after a while all the numbers looked the same.

 

[John_w]

    Worksheets.PrintOut To:=7, ActivePrinter:="clawPDF"

    'Wait for job to arrive at the queue
    Debug.Print Time; "Waiting for the job to arrive at the queue..."

    If Not clawPDFQueue.WaitForJob(10) Then

That works if you want to print all the worksheets in the workbook.

If you only want to print 2 specific worksheets as a single PDF then you need 2 PrintOut statements, wait for the 2 print jobs and merge the jobs, using WaitForJobs and MergeAllJobs, like this:

Public Sub Save_2_Sheets_As_PDF()

    Dim saveInFolder As String, PDFfullName As String
    Dim clawPDFQueue As clawPDF.Queue
    Dim printJob As clawPDF.printJob
    
   saveInFolder = ThisWorkbook.Path & "\"
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
    
    PDFfullName = saveInFolder & "2 sheets.pdf"
    
    Application.ScreenUpdating = False
    
    'Initialise clawPDF job queue
    
    Set clawPDFQueue = New clawPDF.Queue
    clawPDFQueue.Initialize
    
    'Print 2 specific worksheets to clawPDF job queue as 2 print jobs
    
    ActiveWorkbook.Worksheets("Front_Cover").PrintOut ActivePrinter:="clawPDF"
    ActiveWorkbook.Worksheets("Timetable").PrintOut ActivePrinter:="clawPDF"
    
    'Wait for the 2 jobs to arrive on the queue

    If clawPDFQueue.WaitForJobs(2, 10) Then

        'Merge the 2 jobs to a single job so that there is 1 output PDF containing the 2 sheets
        
        clawPDFQueue.MergeAllJobs
        Set printJob = clawPDFQueue.NextJob
        
        printJob.SetProfileByGuid "DefaultGuid"
        printJob.SetProfileSetting "OpenViewer", False
        
        'Convert job to PDF
        
        printJob.ConvertTo PDFfullName
        
        'Check if conversion was successful

        If Not printJob.IsFinished Or Not printJob.IsSuccessful Then
            MsgBox "Failed to convert print job to PDF " & PDFfullName
        End If
        
    End If
    
    'Release clawPDF object
    
    clawPDFQueue.ReleaseCom
    
    Application.ScreenUpdating = True
    
    MsgBox "Created " & PDFfullName
    
End Sub