Creating Custom Reports in KoalaBrain using Excel and VBA

I created KoalaBrain in part due to a client with unique reporting requirements. Many business and finance types are proficient with Microsoft Excel, and scripting it in Visual Basic for Application, so it made sense to base custom reports around this.

VBA Reports in KoalaBrain dumps sales and payment data into different sheets in a spreadsheet and provides it as an excel .xlsm file. The user can upload an optional template into KoalaBrain, which will add the sheets to that template and return it including the data and any macros which were in the template. This makes for simple, easily produced custom reports at a single click (or uploaded on a schedule for remote viewing if you use KoalaBrain Online).

Here’s how to create a simple report which displays orders which contain items from a specified category, and which where created in a specified time period. It’ll be used to print off order sheets to be sent to the manufacturer of the items in that category.

What you’ll need

Steps

  1. Create a new Excel file in the .xlsm format – this will be the new report template
    • Open Excel to a blank worksheet
    • Rename ‘Sheet1’ to ‘Orders’ – our template VBA code will reference this sheet by name
    • Click ‘File’ -> ‘Save As’
    • Select ‘Excel Macro-Enabled Workbook (.xlsm)’ from the ‘File Format’ Dropdown
    • Enter an appropriate file name and save
  2. Import vba lib into the report
    • Enable the developer tab in the Excel Toolbar
    • Download the file koalaVbaFunctions.bas
    • Click on the ‘Developer’ tab in Excel and then click on ‘Visual Basic’
    • Click ‘File’ -> ‘Import File’ and select the downloaded koalaVbaFunctions.bas file
    • The file will have been imported as ‘Module1’. Click on it in the sidebar, and rename it to ‘koalaFunctions’ in the ‘Properties’ box below the project explorer
  3. Add a reporting module VBA code
    • Click on ‘Insert’ -> ‘Module’
    • The new module will have been created as ‘Module1’. Click on it in the sidebar, and rename it to ‘orderReport’ in the ‘Properties’ box below the project explorer
    • Double-click on ‘orderReport’ in the modules list to bring up the editor. Copy and paste the following code into the editor Sub doReport() 'Define an array of the categories we want to show the orders for Dim categories As Variant: categories = Array("Widgets", "Gizmos") 'Define the timezone we're working in - used when formatting the date Dim timezoneOffset As Double: timezoneOffset = 9.5 'Stop excel from trying to draw the screen while data is being moved about to speed things up Application.ScreenUpdating = False 'Delete old Orders report sheet if it exists If koalaFunctions.Custom_WorksheetExists("Orders") Then Call koalaFunctions.Custom_DeleteSheet(ActiveWorkbook.Sheets("Orders")) End If 'Copy Sales data to new 'Orders' sheet. We're copying from the sheet which includes incomplete sales as orders will not yet have been completed. Bail out if the workbook hasn't been populated with KoalaBrain data If koalaFunctions.Custom_WorksheetExists("kbSalesIncIncompleteData") Then Call koalaFunctions.Custom_CopyRenameSheet(ActiveWorkbook.Sheets("kbSalesIncIncompleteData"), "Orders") Else MsgBox "No Data to work with - please export this template from KoalaBrain before using" Exit Sub End If 'Remove sales which are not Orders from the copied data 'As a habit I avoid using ActiveSheet and similar functions as I find things get confusing when working with large workbooks with many sheets. Instead I explicitely reference the sheet each time. It may look more cluttered, but it helps reduce mistakes Call koalaFunctions.Utility_RemoveRowsNotMatchingColumnValue(ActiveWorkbook.Sheets("Orders"), koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "order"), 1) 'Remove Sale rows where the categoryName is not in the array of specified categories Dim i As Long: i = 2 'The variable we'll increment in the loop to track the current row number. Start at row 2 as row 1 is the column headings Dim length As Long: length = koalaFunctions.Custom_GetLastRow(ActiveWorkbook.Sheets("Orders")) 'Number of rows in the sheet Do While i <= length If Not koalaFunctions.Utility_IsInArray(ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "saleRows.categoryName")).value, categories) Then ActiveWorkbook.Sheets("Orders").rows(i).EntireRow.Delete i = i - 1 'As we've deleted a row, we need to adjust the loop position length = length - 1 ' ...and the loop length End If i = i + 1 'Go to the next row Loop 'Deduplicate the sale rows by saleRowsUuid - As rows may be repeated if they have multiple tax or discount rows (so that the additional tax/discount rows can be included) these need to be trimmed. We don't need the additional rows as we aren't calculating any totals in this report, just the contents of the order 'UUIDs are unique strings which identify a particular record in the KoalaBrain database Call koalaFunctions.Custom_RemoveDuplicates(ActiveWorkbook.Sheets("Orders"), koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "saleRows.uuid")) 'Sort by Sale uuid to ensure sale rows from the same sale are grouped Call koalaFunctions.Custom_SortSheetByColumn(ActiveWorkbook.Sheets("Orders"), koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "uuid"), "Ascending") 'Rename the columns which will be used in the final report ActiveWorkbook.Sheets("Orders").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "shortId")).value = "Sale Short ID" ActiveWorkbook.Sheets("Orders").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "dueByDate")).value = "Due Date" ActiveWorkbook.Sheets("Orders").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "saleRows.quantity")).value = "Quantity" ActiveWorkbook.Sheets("Orders").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "saleRows.productName")).value = "Product" ActiveWorkbook.Sheets("Orders").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "note")).value = "Note" ActiveWorkbook.Sheets("Orders").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "contactName")).value = "Contact" ActiveWorkbook.Sheets("Orders").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "contactPhone")).value = "Phone" 'Remove unneccesary columns Call koalaFunctions.Utility_RemoveColumnsNotMatchingHeaders(ActiveWorkbook.Sheets("Orders"), Array("uuid", "Sale Short ID", "Due Date", "Quantity", "Product", "Note", "Contact", "Phone")) 'Format the Date Due Column Call dateFormat(ActiveWorkbook.Sheets("Orders"), koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Due Date"), timezoneOffset) 'Resize cols. All formatting will be lost when KoalaBrain inserts it's data, so any formatting must be added via VBA code ActiveWorkbook.Sheets("Orders").Range("B:B").ColumnWidth = 35 ActiveWorkbook.Sheets("Orders").Range("C:E").ColumnWidth = 12 'Loop through the Sale Rows and perform formatting - highlighting and adding dividers between items in different sales. We know the sale has changed when the Sale UUID column is not the same as the last. i = 2 'Reset loop values length = koalaFunctions.Custom_GetLastRow(ActiveWorkbook.Sheets("Orders")) 'Reset loop values Dim lastSaleUuid As String 'Keeping track of the last sale in the lopo by UUID. Column A will hold the sale UUID after the above column deletions, and be removed at the end of this section, so treat column B as the start of the real formatted report Do While i <= length 'Merge quantity and product Name Cells ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Product")).value = ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Quantity")).value & "x " & ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Product")).value If lastSaleUuid = ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "uuid")).value Then ' Remove repeated information like the sale short ID and note to make things look nicer. ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Sale Short ID")).Clear ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Due Date")).Clear ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Note")).Clear ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Contact")).Clear ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Phone")).Clear 'Move the Quantity, Product columns to the left. As the number of columns is now < 10 I've started referring to them by column letter rather than using a lookup function ActiveWorkbook.Sheets("Orders").Range("B" & i & ":G" & i).Delete Shift:=xlShiftToLeft Else 'Duplicate the first row in a sale - the first will become the header with a grey background ActiveWorkbook.Sheets("Orders").Cells(i, 1).EntireRow.Copy ActiveWorkbook.Sheets("Orders").Cells(i, 1).EntireRow.Insert Shift:=xlDown ActiveWorkbook.Sheets("Orders").Cells(i, 1).EntireRow.Interior.Color = RGB(225, 225, 225) 'Add another row below and move the note into it - it's preformatted text so it needs room ActiveWorkbook.Sheets("Orders").Cells(i + 1, 1).EntireRow.Insert Shift:=xlDown ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Note")).Cut ActiveWorkbook.Sheets("Orders").Cells(i + 1, 2) ' Pasting into the row BELOW as the loop is still in the position of the first row. Pasting into the second column as the first (A) is the UUID which is only used for caculations and later removed ActiveWorkbook.Sheets("Orders").Range("B" & i + 1).value = "Note: " & vbNewLine & ActiveWorkbook.Sheets("Orders").Range("B" & i + 1).value ActiveWorkbook.Sheets("Orders").Range("B" & i + 1).WrapText = True ' Wrap note text ActiveWorkbook.Sheets("Orders").rows(i + 1).EntireRow.AutoFit ' Fit cell to note text height 'With duplicating and moving now complete, make the first row of the order details bold With ActiveWorkbook.Sheets("Orders").Cells(i, 1).EntireRow.Font .Bold = True End With i = i + 2 'We've added rows, update the loop position length = length + 2 'And the loop length 'The duplicated row will have it's repeated information removed and it's Quantity, Product columns moved to the left, as above ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Sale Short ID")).Clear ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Due Date")).Clear ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Note")).Clear ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Contact")).Clear ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Phone")).Clear ActiveWorkbook.Sheets("Orders").Range("B" & i & ":G" & i).Delete Shift:=xlShiftToLeft End If lastSaleUuid = ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "uuid")).value i = i + 1 'Go to the next row Loop 'Remove more unneccesary columns Call koalaFunctions.Utility_RemoveColumnsNotMatchingHeaders(ActiveWorkbook.Sheets("Orders"), Array("Sale Short ID", "Due Date", "Contact", "Phone")) 'Add a heading to the sheet including a report name, and the date range reported. Date range is retrieved from the sheet 'kbReportInfo' ActiveWorkbook.Sheets("Orders").rows(1).EntireRow.Insert ActiveWorkbook.Sheets("Orders").rows(1).EntireRow.Insert ActiveWorkbook.Sheets("Orders").rows(1).EntireRow.Insert ActiveWorkbook.Sheets("Orders").Range("A1").value = "Orders Report" ActiveWorkbook.Sheets("Orders").Range("A2:C2").NumberFormat = "dd/mmm/yyyy" ActiveWorkbook.Sheets("Orders").Range("A2").value = DateValue(Mid(ActiveWorkbook.Sheets("kbReportInfo").Range("K2").value, 1, 10)) + TimeValue(Mid(ActiveWorkbook.Sheets("kbReportInfo").Range("K2").value, 12, 8)) + timezoneOffset / 24 ActiveWorkbook.Sheets("Orders").Range("B2").value = "to" ActiveWorkbook.Sheets("Orders").Range("C2").value = DateValue(Mid(ActiveWorkbook.Sheets("kbReportInfo").Range("L2").value, 1, 10)) + TimeValue(Mid(ActiveWorkbook.Sheets("kbReportInfo").Range("L2").value, 12, 8)) + timezoneOffset / 24 With ActiveWorkbook.Sheets("Orders").Range("A1").Font .Size = 16 .Bold = True End With With ActiveWorkbook.Sheets("Orders").Range("A1:E4").Font .Bold = True End With 'Print size With Sheets("Orders").PageSetup .FitToPagesWide = 1 .FitToPagesTall = False End With 'Done! Let the user know, and re-enable screen updating MsgBox "Report Build Complete" Application.ScreenUpdating = True End Sub 'Formats a cell to a more readable date format Sub dateFormat(targetSheet As Worksheet, columnReference As Long, timezoneOffset As Double) Dim temp As Variant For i = 2 To koalaFunctions.Custom_GetLastRow(targetSheet) 'i = 2 to skip header row temp = targetSheet.Cells(i, columnReference).value If Len(temp) > 0 Then 'don't try and format blank cells targetSheet.Cells(i, columnReference).NumberFormat = "dd/mmm/yyyy" targetSheet.Cells(i, columnReference).value = DateValue(Mid(temp, 1, 10)) + TimeValue(Mid(temp, 12, 8)) + timezoneOffset / 24 End If Next i End Sub
    • There’s room for refinement but it’s a custom report in a bit over 100 lines of code, including styling. Not bad! The code is commented and will explain what each step does, be sure to read through it 🙂
    • We want this function to run automatically when the workbook opens. Double click the ‘ThisWorkbook’ module in the project explorer
    • Copy and paste the following code into the editor Private Sub Workbook_Open() Call orderReport.doReport End Sub
    • Close the Visual Basic Editor and return to Excel
    • Save your work and quit Excel – Our template is ready for use
  4. Create a new VBA Report in KoalaBrain using the new template
    • Click on the ‘VBA Reports’ icon on the KoalaBrain main menu
    • Click on ‘New Report’ (Label 1)
    • Fill out the new report name, then click ‘Select File’ under ‘Upload a macro-enabled Excel template (.xlsm file)
    • Click ‘Browse’ and browse for the Excel file we created above, then click ‘Select File’
    • Click ‘Save Report’
  5. Generate and view the report
  • Click on the newly created report in the list (Label 2)
    • Click on ‘Generate’ in the preview sidebar (Label 3)
    • Select the date range for the Sale date of the sales/orders you wish to be included in the report
    • Click ‘Generate Report’, and save the report wherever you want it
    • Optionally schedule the report Click on the ‘Schedule Report’ Button (Label 4)
    • Click on ‘New Scheduled Report’ and select a reporting schedule
    • Provided you have a KoalaBrain Online subscription, your reports will be uploaded on your schedule for viewing online
    • View the report in Excel Open the report in Excel and allow content and macros when prompted
    • The report function will run automatically, formatting the data added to the workbook by Koalabrain, and alerting you when complete
    • If you were to want to be able to edit the contents of the Order sheet and save the changes perminantly, you could add a check to the doReport() function in the VBA script to only run once by checking for an empty ‘Orders’ sheet
    • You’re done! See the end result below

Leave a Reply