Many small-scale businesses do not use database management systems to generate reports. Most of them stick to spreadsheet packages. Unfortunately Spreadsheet softwares are not equipped with advanced query and report generating features. However, some Excel users struggle with Reports that they update, save and print manually. It is a very tedious job, but VBA can make it better. I have created a spreadsheet application you may used to fill a template/report with different “Records“, save each in a separate workbook, and print automatically.
Borrowing a few terms from Database Packages, first, we need to organize the data that goes into the report in a neat ‘Table’. A database entry, also called a ‘Record‘, has information under various headers called ‘Fields‘. A record needs to be uniquely Identifiable with a ‘Key‘. In this application, the ‘Table‘ is stored in the ‘Data‘ Sheet. The ‘Field‘ names are stored in a Row named ‘Field Names‘. Each Row corresponds to a ‘Record‘ and the ‘Keys‘ are generated automatically when the user clicks the ‘Set-up‘ button.
The next thing we need to do is to set-up the Report which needs to be filled with data from the ‘Table‘. This is done in the ‘Template‘ sheet. The cells in this sheet need to be linked to ‘Fields‘ corresponding to the required ‘Record‘. This is accomplished by using an intermediate ‘Mapping‘ Sheet. In this sheet, the user can extract all the fields of a particular record, by specifying its ‘Key‘. Clicking the ‘Export‘ button loops through all the ‘Keys‘, updates the ‘Mapping‘ sheet and exports the ‘Template’ into individual workbooks.
Detailed Instructions to use this tool are as follows:
Step 1 : Import your records into the ‘Data’ sheet.
- Fill in the Field Names/Column Headers
- Copy paste records. Preferably as Values.
- Click the ‘Set Up’ Button
Step 2 : Setup your Template
- Paste your Template into the ‘Template’ Sheet. Copy pasting all (Ctrl+V).
- Link the “Fields” to the cells in the ‘Mapping’ spreadsheet
- Print Page Setup the sheet. Also set the Default Printer to the available one.
Step 3 : Set Preferences in the ‘Mapping’ sheet
- Which records you want to save? – Set the Start and Stop Record Numbers
- Do you want to print each export when they are created? – Yes/No
- Set up a file name tag that is unique for each record. Example: =”Record – “&TEXT(RecordNumber,”000”). Feel free to use the mapping ranges to add in any unique Identification Numbers. Make sure it does not contain any special characters. Set up a similar name for the sheet name.
- Choose if you’d like to protect the sheets, workbooks and File; and specify the respective password. Remember Protecting the sheet does not allow users to make changes to the sheet; protecting the workbook does not allow the user to change the structure of the workbook. And protecting the file does not allow the user to open the workbook without keying in the correct password.
- While saving a workbook, Excel usually prompts the user for confirmation if a workbook with the same name exists already. Setting the ‘Replace Without Prompt’ option to ‘Yes’ will replace existing files without the prompt.
- Click the browse button to specify where the output files should be saved. If left blank, the files would be saved along with this workbook.
- Hit the ‘Reset’ button to bring back the default options.
Step 4 : Hit the Export Button
I understand that you May not be interested in using this Spreadsheet application in its entirety. However, you may interested in incorporate the Loop-and-Export functionality into your existing applications. Therefore, I have two versatile Macros that you can use in your applications.
Macro 1 : Macro that exports a Spreadsheet into a Workbook.
'==================================================================================== 'A macro that exports a sheet to a workbook. It has a few optional arguments that 'make it versatile. 'Arguments: 'WhichSheet - Worksheet Object. The sheet that needs to be exported. If not specified, ' The active sheet will be exported. 'ToWhichBook - Workbook Object. The sheet specifed earlier will be copied to this ' workbook. If not specified, a new workbook will be created. ' If you'd like a reference to the workbook that was created, pass an ' empty workbook object as argument to this sub. Will come on handy while ' exporting multiple WorkSheets to a new workbook. And it will also help ' in saving the created workbook later. 'SheetName - String Variable. An optional arguemnt that will be used to set the name of ' the newly created sheet. If not specified, it will be set to the name ' of 'WhichSheet' 'OnlyValues - Boolean Variable. An optional argument that can be set to True to convert ' all the references to values. It is set to True by defauly. Set it to ' False if you'd like to retain the formulas. 'ProtectSheet - Boolean Variable. Set to true if you'd like to protect the newly created ' WorkSheet. 'SheetPassword - String Variable. Password to protect the sheet with. 'Author : Ejaz Ahmed - firstname.lastname@example.org 'Date: 30 January 2013 'Website : https://strugglingtoexcel.wordpress.com/ '==================================================================================== Sub ExportSheetToBook(Optional ByRef WhichSheet As Worksheet, _ Optional ByRef ToWhichBook As Workbook, _ Optional ByVal SheetName As String = "", _ Optional ByVal OnlyValues As Boolean = True, _ Optional ByVal ProtectSheet As Boolean = False, _ Optional ByVal SheetPassword As String = "") 'Declare Sub Level Objects/Variables 'A newly created workbook contains a number of sheets. All of them cannot 'be deleted, one has to remain. It has to be deleted only after the intended 'sheet has been imported. Dim shtDeleteFinal As Worksheet 'A WorkSheet object to use in the for loops Dim shtEach As Worksheet 'A WorkSheet object to hold the sheet that was imported Dim shtCopied As Worksheet 'A long variable to use in for loops Dim lngCount As Long 'A Boolean variable to hold the DisplayAllert status temporarily Dim booStatusAlerts As Boolean 'Set the boolean variable to the current DisplayAlerts status booStatusAlerts = Application.DisplayAlerts 'Set WhichSheet to ActiveSheet if a sheet was not passed to the sub If WhichSheet Is Nothing Then Set WhichSheet = Application.ActiveSheet End If 'Create a new workbook if no workbook was passed to the sub. A new 'workbook will be created even if an uninitialized workbook object 'was passed to the sub. If ToWhichBook Is Nothing Then 'Create a new Workbook Set ToWhichBook = Application.Workbooks.Add 'Set the First Sheet to a WorkSheet object, for deleting it later Set shtDeleteFinal = ToWhichBook.Sheets(1) 'Supress Alerts, for excel prompts for a confirmation when the 'user deletes a WorkSheet Application.DisplayAlerts = False 'Delete all the WorkSheets except the first one For Each shtEach In ToWhichBook.Sheets If Not shtEach Is shtDeleteFinal Then shtEach.Delete End If Next shtEach End If 'Copy the WorkSheet to the intended workbook before the first WorkSheet WhichSheet.Copy Before:=ToWhichBook.Worksheets(ToWhichBook.Sheets(1).Name) 'Now the newly copied WorkSheet will be the first sheet in the workbook 'Initialize a WorkSheet object to remember the sheet Set shtCopied = ToWhichBook.Worksheets(ToWhichBook.Sheets(1).Name) 'If the user wants only Values, convert formula to values. If OnlyValues Then Call modSupport.ChangeValueToFormula(shtCopied.Cells.SpecialCells(xlCellTypeFormulas)) End If 'Delete the One sheet that was not deleted earlier If Not shtDeleteFinal Is Nothing Then shtDeleteFinal.Delete Set shtDeleteFinal = Nothing End If 'Restore the DisplayAlerts status Application.DisplayAlerts = booStatusAlerts 'If the user specified a WorkSheet name, then rename it If Not SheetName = vbNullString Then shtCopied.Name = SheetName End If 'If the user wants to protect the sheet, Protect it with the 'specified password. If ProtectSheet Then shtCopied.Protect Password:=SheetPassword End If End Sub
Macro 2 : Loops through a specified range of ‘Keys’ and Exports the ‘Templates’.
'==================================================================================== 'A macro that loops through a specified range of 'Key' and exports the 'Templates'. 'It contains a lot of optional arguments that may be used the customize the export 'Process 'Arguments: 'LngStart - Long Variable. Starting 'Key' number 'LngEnd - Long Variable. Ending 'Key' number 'rngUpdate - Range Object - The cell that needs to be updated with the current ' 'Key' number that is being exported. The Index functions used to ' lookup the records depend on this cell. 'shtTemplate = WorkSheet object. The Template sheet that needs to be exported 'strOutputFolder - String Variable - Folder path where the files need to be ' saved. This workbook's path will be used if left blank. 'booProtectSheet, booProtectBook, booProtectFile - Boolean Variables. The ' respective objects will be protected with corresponding ' passwords specified in strPwdSheet, strPwdBoook, strPwdFile ' respectively. 'strPwdSheet, strPwdBoook, strPwdFile - String Variables. Passwords to protect the ' sheet, book and file respectively. 'rngSheetName - Range Object - The user has the option to set up a cell that will ' create a unique name for the WorkSheet that is exported. If ' this arguement is not specified a default name is generated. 'rngFileName - Range Object - The user has the option to set up a cell that will ' create a unique name for the WorkBook that is created. If this ' arguement is not specified a default name is generated. 'booSaveFile - Boolean Variable. User can choose if each export is saved as an ' individual file. 'booCloseFile - Boolean Variable. The user can choose if the workbook should be ' closed after Saving/Printing. 'booPrint - Boolean Variable. The user can choose if the sxported sheet should be ' printed using the default printer. 'booReplace - Boolean Variable. The user can choose to supress the "Do you want to ' replace the file? prompt. 'booUpdateStatus - Boolean Variable. The user can choose if the status bar should ' show a quick update on which record is being processed. 'booPromptComplete - Boolean Variable. The user can choose to dispaly an alert when ' the loop is done ' 'Author : Ejaz Ahmed - email@example.com 'Date: 30 January 2013 'Website : https://strugglingtoexcel.wordpress.com/ '==================================================================================== Sub ExportRun(ByVal LngStart As Long, _ ByVal LngEnd As Long, _ ByRef rngUpdate As Range, _ ByRef shtTemplate As Worksheet, _ Optional ByVal strOutputFolder As String = vbNullString, _ Optional ByVal booProtectSheet As Boolean = False, _ Optional ByVal strPwdSheet As String = vbNullString, _ Optional ByVal booProtectBook As Boolean = False, _ Optional ByVal strPwdBook As String = vbNullString, _ Optional ByVal booProtectFile As Boolean = False, _ Optional ByVal strPwdFile As String = vbNullString, _ Optional ByRef rngSheetName As Range, _ Optional ByRef rngFileName As Range, _ Optional ByVal booSaveFile As Boolean = True, _ Optional ByVal booCloseFile As Boolean = True, _ Optional ByVal booPrint As Boolean = False, _ Optional ByVal booReplace As Boolean = True, _ Optional ByVal booUpdateStatus As Boolean = False, _ Optional ByVal booPromptComplete As Boolean = True) 'An empty WorkBook object to retain a reference to the new 'workbook that would be created by the 'ExportSheetToBook' macro Dim wkbBook As Workbook 'A Long variable used in the For Loops Dim lngCount As Long 'String Variables to hold the Sheet, Book and File names. Dim strSheetName As String Dim strBookName As String Dim strBookPath As String 'If the user did not specify a part, use ThisWorkBook's path If strOutputFolder = vbNullString Then strOutputFolder = shtTemplate.Parent.Path & "\" End If 'If the user chooses to Replace Files without Prompt 'Suppress the Alerts If booReplace Then Dim booStatusAlerts As Boolean booStatusAlerts = Application.DisplayAlerts Application.DisplayAlerts = False End If 'Start the Loop For lngCount = LngStart To LngEnd 'Update the Record Number rngUpdate.Value = lngCount 'Recalculate Worksheet (in case the user is in Manual Calculate Mode) rngUpdate.Worksheet.Calculate 'If the user has specifed a Cell that contains a SheetName Tag, 'Use it, else set it to Null If Not rngSheetName Is Nothing Then strSheetName = rngSheetName.Value Else strSheetName = vbNullString End If 'If the SheetName tag is empty, create a Generic Name using the 'Record Number If strSheetName = vbNullString Then strSheetName = shtTemplate.Name & "_" & _ Format(lngCount, String(Len(CStr(LngEnd)), "0")) End If 'If the user wants the status bar to be updated, update it 'with Record Number and Sheetname If booUpdateStatus Then Application.StatusBar = "Record " & _ Format(lngCount, String(Len(CStr(LngEnd)), "0")) & _ " of " & LngEnd & " | " & strSheetName End If 'Export the Sheet using an Empty WorkBook argument to retain a reference 'to the newly created WorkBook Call ExportSheetToBook(shtTemplate, wkbBook, strSheetName, True, _ booProtectSheet, strPwdSheet) 'If the user has specifed a range that contains a FileName tag, 'then use it. Else, set it to Null. If Not rngFileName Is Nothing Then strBookName = rngFileName.Value Else strBookName = "" End If 'If the FileName tag is empty, create a Generic Name using the 'Record Number If strBookName = "" Then strBookName = strSheetName End If 'Update a String Variable to hold the full Path strBookPath = strOutputFolder & strBookName 'Protect Workbook If booProtectBook Then wkbBook.Protect Password:=strPwdBook End If 'Save the file If booSaveFile Then 'If the user chooses to Password protect the file, 'protect it with the specifed password If booProtectFile Then wkbBook.SaveAs FileName:=strBookPath, Password:=strPwdFile Else wkbBook.SaveAs FileName:=strBookPath End If End If 'Print the worksheet If booPrint Then wkbBook.Worksheets(strSheetName).PrintOut End If 'Close the File If booCloseFile Then wkbBook.Close End If 'Set the WorkBook object to Nothing for the next iteration Set wkbBook = Nothing Next lngCount 'Restore the StatusBar Message If booUpdateStatus Then Application.StatusBar = False End If 'Restore the Dsiplay Alerts Status If booReplace Then Application.DisplayAlerts = booStatusAlerts End If 'Show the user a Completed Message If booPromptComplete Then Dim strMsgbox As String strMsgbox = "The Export is now done." & vbNewLine & _ "Records " & LngStart & " to " & LngEnd & _ " have been exported." & _ vbNewLine & "The files are saved in : " & _ strOutputFolder MsgBox strMsgbox, vbInformation End If End Sub
In fact, even in my application, I have used a macro that fetches all the arguments that need to be passed on to the aforementioned macros. I have added in detailed comments to help you follow my code.
Scope for Improvement:
- Extend the Tool to work with more than 100 Fields
- Help the user import ‘Field’ names and ‘Records’ from Databases or Csv files.
- An alternative implementation, would be an add-in that would insert these features into an existing application.