Do you need to generate Excel reports based on a template on a regular basis? Do you need to copy the template Worksheet to another Workbook, or from another Workbook? Here are some approaches with VBA code
Possible use cases for Excel templates
- Your monthly report is based on an Excel Worksheet you prepared, and each month needs to be stored as a separate Workbook with its specific calculations for the month.
- You are preparing and sending your customers a standard letter with calculations / charts that are changing every time. Maybe a quote, or a model calculation. You want to send them an Excel file with the specific model every time.
- Your annual accounting is maintained in a Workbook holding 12 Worksheets, one for every month. You want to create a new Workbook at the beginning of every year, based on the monthly template you maintain in a dedicated Workbook.
- You need to prepare a set of Excel Workbooks, one for each of your team, based on the same template.
The common challenge in such use cases is automating the exchange of template Sheets, before or after populating it with the specific results, between different Workbooks or Worksheets.
Let’s consider some possible approaches and implement them in VBA.
A dedicated Template file – Working with multiple Workbooks
In this scenario, you maintain the template in a dedicated Workbook. This Workbook is not participating int the logic, construction, or data storage of your solution, but merely holds the re-usable template in a dedicated Worksheet. Usually there is only one Worksheet unless you maintain a repository of different templates in this Workbook.
By separating the template from the “master” working file, you reduce the load on your working file, as the template is only needed occasionally for creating the report.
The resulting report can be anything you like, once you have populated the template with the specifics: a new Workbook, a PDF report, a printout, etc.
The general approach here would be:
- Silently open the template Workbook.
- Populate it with the specifics.
- Save it with a new name (even if for temporary use).
- Use it further (PDF / printout / email / PowerPoint).
Option 3 may not be needed, for example, if you only send the resulting report to the printer, or if you save a PDF of the result and need not keep a copy of the resulting Workbook.
Option 4 may not be needed, if all you are doing is archiving a periodic report, with no need for immediate action on it.
Silently means you are not intrusive to the user: nothing really happens on the screen while the template Workbook is being opened and worked with. This is achieved by turning off the ScreenUpdating property of the Excel application.
Here’s a general program for that:
Function CreateMonthlyReport() As Boolean
Dim strTemplateFileName As String
Dim wkbMaster As Workbook
Dim wkbTemplate As Workbook
On Error GoTo FailedReport
Application.ScreenUpdating = False
strTemplateFileName = "\MyPath\ReportTemplate.xls"
If (OpenExcelFile(strTemplateFileName, False) = vbNullString) Then GoTo FailedSub
Set wkbTemplate = ActiveWorkbook
If Not PopulateMonthlyReport(wkbMaster, wkbTemplate) Then GoTo FailedReport
If Not PublishMonthlyReport(wkbTemplate) Then GoTo FailedReport
CreateMonthlyReport = True
CloseSub:
If Not wkbTemplate Is Nothing Then wkbTemplate.Close SaveChanges:=False
wkbMaster.Activate
Application.ScreenUpdating = True
Exit Function
FailedReport:
'Possibly display an error message
GoTo CloseSub
End Function
The important observation here is that once I have a handle to the open template file (wkbTemplate) and to the master file (wkbMaster), I can do whatever I want with them.
Here I’m calling a dedicated function to populate the template from the master, and another function to publish the calculated template.
As you can see, I have generic functions for opening an Excel Workbook (if not already open), stripping a Workbook’s name from the full path and for testing if a Workbook is already open or not.
These are included in my free 105 Excel VBA Functions pack, but for completeness, I’m happy to share them here with you:
Function OpenExcelFile(strFullPath As String, bolReadOnly As Boolean) As String
'Opens an Excel file if not already open.
'Returns the Workbook name or "" is failed
Dim WorkbookName As String 'Name without full path
WorkbookName = StripFileNameFromPath(strFullPath)
OpenExcelFile = vbNullString
If (IsWorkBookOpen(WorkbookName)) Then
Workbooks(WorkbookName).Activate
Else
On Error GoTo FailedFileOpen
Workbooks.Open Filename:=strFullPath, ReadOnly:=bolReadOnly
End If
OpenExcelFile = ActiveWorkbook.Name
Exit Function
FailedFileOpen:
'Possibly display an error message
End Function
Function IsWorkBookOpen(strName As String) As Boolean
Dim xWb As Workbook
On Error Resume Next
Set xWb = Application.Workbooks.Item(strName)
IsWorkBookOpen = (Not xWb Is Nothing)
End Function
Function StripFileNameFromPath(ByVal strFullPath As String) As String
Dim position As Integer
position = InStrRev(strFullPath, "\")
If (position > 0) Then
StripFileNameFromPath = Mid(strFullPath, position + 1)
Else
StripFileNameFromPath = strFullPath
End If
End Function
Duplicating the Template Sheet – Working with multiple Worksheets
This time we will find our template Worksheet in our working Workbook and we will also target our result Worksheet as an added Worksheet inside our working Workbook as well.
The first function here will do the whole trick. It will copy the template Worksheet (passed as the second argument) as a new Worksheet at the end of the Worksheets collection and will return a pointer to that Worksheet – ready to be populated or otherwise manipulated:
Function GetNewWorksheet(ByRef wkb As Workbook, ByVal strSourceSheet) As Worksheet
'returns handle to new worksheet added at the end of wkb, copied from strSourceSheet
With wkb
.Sheets(strSourceSheet).Copy After:=.Sheets(.Worksheets.Count)
Set GetNewWorksheet = .Sheets(.Worksheets.Count)
End With
End Function
Creating a new Workbook with the template ready for user interaction
Lastly, let’s consider a scenario in which you wish to prepare a new Workbook for the user, with the template Worksheet in it ready for interaction.
I introduce a little trick here in order to make sure no redundant Sheets are left in the new Workbook – only the newly created template Sheet – named as desired. I always add it to be the last one, so that I know I need to loop all Sheets in the Worksheets collection – except for the last one. After they have been deleting, it becomes the first (and only) Sheet, for easy reference and name change.
Function OpenWorksheetAsNewExcel(strSheetSourceName As String, strSheetName As String) As Workbook
'Create a new Workbook with the worksheet in it and leave active for the user
'@strSheetName - The Worksheet name in the new Workbook
Dim wkbReport As Workbook 'New Workbook
Dim wkbCurrent As Workbook
On Error GoTo FailedSub
Set wkbCurrent = ActiveWorkbook
Set wkbReport = Workbooks.Add
With wkbReport
wkbCurrent.Sheets(strSheetSourceName).Copy After:=.Sheets(.Sheets.Count)
Application.DisplayAlerts = False
For i = 1 To .Sheets.Count - 1
.Sheets(i).Delete
Next i
Application.DisplayAlerts = True
.Sheets(1).Name = strSheetName
End With
Set OpenWorksheetAsNewExcel = wkbReport
CloseSub:
Exit Function
FailedSub:
'Possibly display an error message
GoTo CloseSub
End Function
Take it any way you want
The above code samples can serve many other scenarios and combinations involving Workbooks, Worksheets and templates.
You can take what you need and adjust accordingly to meet your specific requirements.
Please send this post to your friends and colleagues so that they can automate their reports too!
WANT TO LEARN PROGRAMMING WITH EXCEL VBA?
I created a complete 8-course program for you.
From programming to databases with Excel VBA to deliver business-grade solutions.
Take the first course today: Computer Programming with Excel VBA.
Click the button to see the complete program and start now!
Recent Comments