22 April 2019

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
  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
  6. 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
  7. 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