I had a small, interesting job involving exporting to PDF last week where the client had an .xlt template with existing in-house VBA to let the user save certifying documents within the an .xls file to a default folder plus “print to pdf” ie creating a pdf file and saving it to a user chosen folder. It used a windows pdf “printer” to do this but it had stopped working and holding up their operations. Maybe someone had deleted this printer from Windows or there was a new PC which showed the problem with that approach. These are small quick fix jobs which shouldn’t cost the client too much, so a bit of pressure on me to quickly understand what had been done and fix it as it is a critical step in their operations.
I was pretty sure straight off saving or somehow exporting to pdf, instead of printing to pdf, would be a more bullet proof approach and work from any PC and not be reliant on specific PC Windows settings. But they needed the user to be able to pick the exact folder from a default path and make new folders as needed for different months which actually naturally was available under the print to pdf approach. So this needed to be created in the saving/exporting solution. Actually the existing application had it’s challenges:
- it was a template (the template was a good approach but are time consuming to work on as when you are coding and testing you are continually reopening the file and finding your last place and sometimes losing testing adjustments because you forget you are in the .xls file not the .xlm file!)
- yes it was in 2003 compatibility mode and needed to stay so
- had formulas linking to a server excel file (one of my no. 1 pet hates) that of course break on your PC (I think its better to copy in the data from the external workbook you need with an update process or use Power Query if you don’t want VBA)
- formulas in merged files (merged cells are one of my other pet hates – I just think never use them they always cause problems and there is always another way to produce the format you want)
- it had to produce several certificates and each one had it’s own code in the worksheet object driven by ActiveX buttons (Code in the worksheet object is not a good idea, better to modularise code so the one sub can handle each certificate linked to form buttons on each certificate)
But I wasn’t given the task of revamping the application, just to fix it. The use of a template was a good approach – you don’t see many people using templates and it can be a good but often forgotten option in a situation where certificates or invoices have to be produced from an easier to maintain template. After a few coffees, this code excerpt concerning saving to pdf using the .ExportAsFixedFormat method seem to worked well in each sheet object. But note, as above if I were writing this from scratch it would be in a module working with a form button.
[Update 15/01/17 I just used what was there before and had to fix as below, sorry for that.]
[vb]
Dim varPdfFolderPath As Variant ‘For the pdf file to be created
Dim sPdfFolderDefPath As String ‘For a default folder
Dim Thisfile as string
‘User defines path in Sheet16 for saving .xls files and folder to start in for user to choose folder to save pdf files
sCertPath = Sheet16.Range(“CertPath”).Value2 & “\”
sPdfFolderDefPath = Sheet16.Range(“pdfCertPath”).Value2
ThisFile = “My Report”
‘displays the save file dialog so user can choose the folder to save pdf in
varPdfFolderPath = Application.GetSaveAsFilename(FileFilter:= _
“Pdf Files (*.pdf), *.pdf”, _
Title:=”Choose XX Pdf Cert folder – if you want a new folder right click New \ Folder”, _
InitialFileName:=sPdfFolderDefPath & “\” & ThisFile & “.pdf”)
‘checks to make sure the user hasn’t cancelled the dialog
If varPdfFolderPath = False Then
MsgBox “Save Process Error, you have hit cancel or made an error so the file has not saved so this
process will terminate and you will have to repeat the SAVE Process”, vbCritical, “TAKE NOTE!”
Exit Sub
End If
‘## Check to see if this file already exists from a previous run, and delete it if it does
If Dir(varPdfFolderPath) <> vbNullString Then
Kill varPdfFolderPath
End If
‘Now finally export sheet as pdf
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=varPdfFolderPath, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
‘confirmation message with file info
MsgBox “PDF Certificate file created at: ” & vbCrLf & varPdfFolderPath
[/vb]