Excel is still used regularly by accountants yet many are self-taught and are missing out on lots of useful tricks!
To help us learn some more useful Excel functions, Dave Sellick will be giving us a great “Excel Top Tip” in each issue so we can learn how to really use Excel well. Dave is a superuser of Excel and believes it is an absolute game changer and a central part of every accountant’s toolkit – even in 2020.
In this article, Dave provides an introduction to printing customised PDF reports from Excel which is a really useful and essential tool for all accountants.
- I’m fairly obsessed with creating beautiful workflows wherever I can, as most who know me will be aware of!
- In this article, I’m covering a really great workflow tip for anyone who ever needs to translate their Excel spreadsheets into PDF format for reporting or subsequently converting to Powerpoint, Ebook etc.
❗ Note: If you get stuck at any point through this process, refer to the videos at the bottom of the article for a full run-through (albeit based on a PC workflow!)
How this tip will help:
- Turns the laborious, manual process of saving to PDF into an instant click of a button
- It allows for the pre-configuration of report formats that can be printed to PDF instantly
- Creates a quick, controlled and consistent workflow for updating PDF printouts when report changes are made
- Allows consistent and dynamic naming of report PDFs
- PDFs will instantly save into a pre-designated location
Disclaimer:
- The process works differently on Excel Mac & PC – which are effectively two separate bits of software in terms of their underlying architecture
- Running this process on a PC is a smoother, more automated process; however, we’ll also go through how you can get this running on a Mac as well
1. Create a Button or a number of Buttons to Initiate the Printouts
- Go to ‘Insert’ ?? ‘Shapes’
- Pick any shape - I tend to default to a rectangle with rounded corners, but you can choose anything that you think will look good
- Colour and format the shape as appropriate
- Type a word or phrase into the box to make it clear what will happen when you click it – E.g. ‘Print to PDF’
2. Open the VBA Editor
You can do this a couple of few different ways, but I’ll focus on the following 2: ❗ Note; VBA stands for Visual Basic for Applications and is Microsoft’s programming language for Excel and all the other Microsoft Office programs i) Use a Shortcut to Open it
- PC: Alt + F11
- Mac: Option + F11
ii) Access it via the Developer Tab
- Before you do this, you need to have the Developer Tab!
- You should have this on the ribbon anyway if you’re serious about harnessing the full power of Excel
- This will give you easier access to Macros and the VBA editor, without needing to remember shortcuts
- You can add this by going to ‘File’ ?? ‘Options’ ?? ‘ Customize Ribbon’ and then clicking the box to show the Developer tab
- From here you can then select the VBA editor by selecting Developer ?? Visual Basic
- Once open you’ll see an interface that looks a bit like this :
3. Insert a VBA workbook ‘Module’ in the VBA editor
- If you already use VBA then you may already have some active modules in the current workbook
- If not then right-click on ‘Modules’ under the name of the current worksheet in the VBA editor view
- Then select ‘Insert’ ?? ‘Module’
- Select the Blank Module Interface
4. Insert the ‘Baseline’ VBA Code
Copy the below blue code and paste it into the Module interface above
❗ Note. This Code covers both PC & Mac – it’ll identify the operating system and adjust as appropriate
Sub Export_PDF()
Worksheets(Array(“x”, “x”, “x”,”x”)).Select
Dim CurrentFolder As String
Dim FileName As String
Dim myPath As String
Dim UniqueName As Boolean
Dim sMyScript As String
UniqueName = False
‘Store Information About Excel File
myPath = ActiveWorkbook.FullName
CurrentFolder = ActiveWorkbook.Path & “\”
FileName = Mid(myPath, InStrRev(myPath, “\”) + 1, _
InStrRev(myPath, “.”) – InStrRev(myPath, “\”) – 1)
‘Save As PDF Document
If Mac Then
On Error GoTo ProblemSaving
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=”/Users/x/Desktop/” & “x x ” & Format(Range(“Period”), “mmm yy”) & “.pdf”, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
On Error GoTo 0
Else
On Error GoTo ProblemSaving
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=CurrentFolder & “x x ” & Format(Range(“Period”), “mmm yy”) & “.pdf”, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
On Error GoTo 0
End If
‘Confirm Save To User
If Mac Then
MsgBox “PDF Saved onto the Desktop “
Else
With ActiveWorkbook
FolderName = Mid(.Path, InStrRev(.Path, “\”) + 1, Len(.Path) – InStrRev(.Path, “\”))
End With
MsgBox “PDF Saved in the Folder: ” & FolderName
End If
Exit Sub
‘Error Handlers
ProblemSaving:
MsgBox “There was a problem saving your PDF. This is most commonly” & _
” caused by the original PDF file already being open.”
Exit Sub
End Sub
5. Customise the ‘Baseline’ VBA Code: PC
i) Add in the names of the sheets you want to print
- Looking at the first line of the code:
Worksheets(Array(“x”, “x”, “x”,”x”)).Select
- You’ll notice that there are 4 placeholders for sheet names, denoted by “x”
- You’ll need to replace/delete/add additional sheet names into here as appropriate
- E.g. If we have 4 sheets called: – Cover – Total KPIs Summary – P&L Summary – Indirect CF Summary
- Then the code will look like this:
Worksheets(Array(“Cover”, “Total KPIs Summary”, “P&L Summary”,”Indirect CF Summary”)).Select
❗ Note: Your sheets will take the order that they fall from left to right in the tab order of the worksheet rather than the code above. Therefore make sure to arrange your tabs appropriately
ii) Customise the PDF name – Windows PC (skip to part iii if using Mac) ❗ Note: This step is only relevant to PC users of Excel
- Navigate to this section of the code (around 2/3rds down):
Else
On Error GoTo ProblemSaving
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=CurrentFolder & “x x ” & Format(Range(“Period”), “mmm yy”) & “.pdf”, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
On Error GoTo 0
End If
- Then Focus on the following line from this VBA code:
FileName:=CurrentFolder & “x x ” & Format(Range(“Period”), “mmm yy”) & “.pdf”, _
Replace “x x” (see image below ??) with whatever you want to name the print out – As it currently stands, the above code will then add the Month and Year based on the ‘Period’ variable within the report – i.e. the current date ❗
Note: I will cover how to set the ‘Period’ variable at the end of this article
Example:
FileName:=CurrentFolder & “Deckspin Summary Yr1-Yr3 Projections as at ” & Format(Range(“Period”), “mmm yy”) & “.pdf”, _
?? This will give a file name, for the period Feb 2021 (This will save into the ‘current folder’, which will be the folder the current Excel file is saved to), as:
‘Deckspin Summary Yr1-Yr3 Projections as at Feb 21.pdf’
- If you do not wish to make the report dynamic based on the date, then delete this section of the code and just ‘hardcode’ with the name of the report that you wish to see
- Taking the example above, this would like the following:
Example without dynamic dating:
FileName:=CurrentFolder & “Deckspin Summary Yr1-Yr3 Projections”.pdf”, _
?? This will give a file name as:
‘Deckspin Summary Yr1-Yr3 Projections.pdf’
iii) Customise the PDF name – Mac (skip to 6) if using PC – you’ve just done this part!) ❗ Note. This step is only relevant to Mac users of Excel
- Navigate to this section of the code (around halfway down):
#If Mac Then On Error GoTo ProblemSaving ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _
If Mac Then
On Error GoTo ProblemSaving
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=”/Users/x/Desktop/” & “x x ” & Format(Range(“Period”), “mmm yy”) & “.pdf”, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
On Error GoTo 0
- Then Focus on the following line from this VBA code:
FileName:=”/Users/x/Desktop/” & “x x ” & Format(Range(“Period”), “mmm yy”) & “.pdf”, _
- Replace ‘the ‘x’ between ‘Users/’ & ‘/Desktop’ with the username of the mac user (aka your username, most likely)
❗ Note: You can find your user name (if you don’t know it!) by going to the ‘Finder’ (a blue/white face icon – usually the first thing in your dock at the bottom of the screen ), then the”Go” menu in the menu bar at the top of the screen and hold down the Option key for Library to appear. You will then see your username at the top/middle of the screen sitting in the window bezel
- Replace “x x” (see image below ??) with whatever you want to name the print out – As it currently stands, the above code will then add the Month and Year based on the ‘Period’ variable within the report – i.e. the current date
❗ Note. I will cover how to set the ‘Period’ variable at the end of this article
Example:
FileName:=”/Users/davesellick/Desktop/” & “Deckspin Summary Yr1-Yr3 Projections as at ” & Format(Range(“Period”), “mmm yy”) & “.pdf”, _
?? This will give a file name, for the period Feb 2021 (This will save into the ‘current folder’, which will be the folder the current Excel file is saved to), as:
‘Deckspin Summary Yr1-Yr3 Projections as at Feb 21.pdf’
❗ Note. when you run this for the first time it will ask you grant access to the Desktop location to save the file – this is normal, just ensure you follow the instructions to grant access
- If you do not wish to make the report dynamic based on the date, then delete this section of the code and just ‘hardcode’ with the name of the report that you wish to see
- Taking the example above, this would like the following:
Example without dynamic dating:
FileName:=CurrentFolder & “Deckspin Summary Yr1-Yr3 Projections”.pdf”, _
?? This will give a file name as: ‘Deckspin Summary Yr1-Yr3 Projections.pdf’
6. Setting the ‘Period’
❗ Note: If you have chosen not to make the title of the report dynamic based on the month of the report, then skip to step 7)
- Choose a cell on the front page of the report or anywhere that makes sense
- Write in the date of the period
- Select the ‘name’ box
- Input the word ‘Period’ and then press return
- Done ✅ – now this cell will determine the Month & Year included in the report title
- On the back of this, it, therefore, makes sense for anything else in the report that is date sensitive to reference this cell – i.e ‘Period’
❗ Note: Establishing the ‘Period’ in the worksheet is an absolute MUST if you have included the dynamic date formula in your VBA code – you WILL get an error if you try to run the PDF printout without establishing it
7. Assigning the Report Print Out to a Button
- Right-click on the shape that you created in step 1)
- Then select ‘Assign Macro’
- This will open up the Assign Macro interface
- From here you can find and select the relevant print to PDF macro that you have created
- Once you’ve done this you’re ready to run the print to PDF function by clicking the button
8. Creating more than one Button/Report Print out Configuration
i) Duplicate the process above
- Copy the code underneath the previous print out macro
- The VBA editor will automatically recognise that you have created a new macro and it will insert page breaks
- Make the relevant amendments to sheet names and the name of the PDF printout
ii) Create a unique name for each printout macro
- Focus on the first line of the code:
Sub Export_PDF()
- As it currently stands this will result in a macro called ‘Export_PDF’
- ❗ Note. You cannot have spaces in macro names; hence the usage of underscores
- You will need to amend this macro name to ensure that it is uniquely identifiable It makes sense to create something that is also relevant to the report type it is printing out
- E.g:
Sub Export_KPIOnePager_PDF
9. Formatting the Print Area
- The above process assumes that the relevant sheets that are being printed, sit within tabs that have been appropriately formatted for printing
- This typically involves using the Page Break interface to identify where the print margins sit and appropriately formatting the pages to print in a consistent and tidy manner
- You can access the Page Break view from ‘View’?? ‘Page Break Preview’
- Formatting for printing to PDF is a whole subject area in its own right; I may look to cover this at a later stage in the Excel tips series!
? Video Walkthrough Part 1 – Creating Your First Button
? Video Walkthrough Part 2 – Creating Multiple Buttons
A bit about Dave
Text
- I am the founder of a purpose led accounting practice & consultancy based in London called Sidgrove, working exclusively with founder-owned businesses & scaling startups. I initially trained in audit with PwC in banking, before moving into industry for around 6 years with numerous scaling startups; I’ve been running Sidgrove full time for just over a year.
- I’ve spent more hours than is probably healthy?♂️? working with Excel ?as an Accountant over the last 12 years in both industry and practice. During that time I’ve continued to push the boundaries ? of how it can be used to deliver efficiencies and superior insights to my clients.
- My Excel journey really took off ?during my time as a Head of Finance of various startup businesses, where efficiency?, quality commercial insights ?& creative presentation? was imperative.
- I’ve now taken all this experience into my practice, Sidgrove, ??where amongst a wide suite of software and hardware tools, it is still my most prized day-to-day tool ?and canvas ✒
- I believe Excel is still an absolute game changer ??and it needs to stay centre stage in the Accountant’s Toolset, even in 2020.
- However, I know the full functionally of Excel can seem daunting many accountants ?so I am starting a top tips series to help you get the best out of Excel’s most relevant functionality for Accountancy in the modern-day ?.
