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.
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:
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
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
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
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!
Join today to the Excel VBA Inner Circle with Mor Sagmon.
Get a weekly lesson, weekly live Q&A, monthly deep-dive workshop, monthly challenge and other activities.50% Complete
Once you submit your details, you'll receive an email with a confirmation link. That's it! you're subscribed!