Automation of Excel Templates with Multiple Workbooks and Worksheets with VBA

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:

1. Silently open the template Workbook.
2. Populate it with the specifics.
3. Save it with a new name (even if for temporary use).
4. 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!