Using Excel and VBA to generate your VISUAL reports

If you find yourself always needing to export and import your VISUAL data into Excel – why not just retrieve the data straight from Excel?  You can do it with VBA.  Visual Basic for Applications (VBA) is a programming language built into most of Microsoft Office products.  You can do many things with VBA to control Excel – like connecting and querying a database and returning the results to the spreadsheet.

There’s tons of VBA resources online – just google “VBA Resources”

With VBA, there’s no reason why you cannot have Excel be your primary reporting application.  Check out Sales & Gross Margins Analysis Reporting in Excel with Drill Down Capabilities.  This was all done with VBA.

The example below generates a sales report for a given date range.  This mini project involves creating a dialog box prompting the user to enter a start and end date for the report.

To use VBA, you need to first make sure you have access to the Developer tab in the Menu Ribbon.

Using Excel and VBA to generate your VISUAL reports

If it’s not there, click the Office Button and click Excel Options. In the Excel Options dialog box, place a checkmark next to the Show Developer tab in the Ribbon and then click OK.

Show Developer tab in the Ribbon

The Developer tab should now be appear in the ribbon.

Developer Tab in Menu

Now you’re all set.  So let’s get crackin’.

    1. Under the Developer tab, click on Visual Basicto launch the VBA interface.Visual Basic in Excel
    2. Select UserForm from the INSERT menu.UserForm Menu in VBA Excel
    3. Select the Label icon from the TOOLBOX and place it on the form (as shown).Label in toolbar VBA
    4. Right click on the label and select Propertiesfrom the popup menu.  Enter “Starting Invoice Date” in the Caption property (as shown).Label Properties Excel VBA
    5. Select the Textbox icon from the TOOLBOX and place it next to the “Starting Invoice Date” label on the form (as shown).Textbox Toolbar Excel VBA
    6. Right click in the textbox and select Properties from the popup menu.  Enter “txtStartDate” in the Name property (as shown).Textbox Properties
    7. Repeat steps 3 through 6 to enter another label and textbox with the following properties:Label Caption:     “Ending Invoice Date”
      Textbox Name:    “txtEndDate”
    8. Select the CommandButton from the TOOLBOX and place it on the form (as shown).Command in Toolbar Excel VBA
    9. Right click on the CommandButton and select Propertiesfrom the popup menu.  Enter “Generate Report” in the Caption property (as shown).
    10. OK…we just completed the user-interface where a date range can be entered to base the report on.  Now we need to enter the code that will query the VISUAL database to generate the report.  To open the Code window, double click on the CommandButton “Generate Report”.
    11. Before we start inserting code we need to set one reference in order to access the VISUAL database.  Select References… from the TOOLS menu.Referenced Menu Excel VBA
    12. Place a checkmark next to Microsoft ActiveX Data Objects 2.6 Library.  Click OK.References List
    13. In the Code window, place the code below right after the “Private Sub CommandButton1_Click()” (as shown).  You can DOWNLOAD the code (in case you’re trouble copying the code)Command Button VBA CodeDim SQL As String
      Dim dbConn As New ADODB.Connection
      Dim rs As New ADODB.RecordsetDim iCols As IntegerDim strServerName As String
      Dim strDatabase As String
      Dim strUserName As String
      Dim strPassword As String

      ‘***********************************************************************************************
      ‘Be sure to modify the following 4 variables to include your database connection information
      ‘***********************************************************************************************
      strServerName = “E N T E R   N A M E   O F   S Q L   S E R V E R”
      strDatabase = “E N T E R   D A T A B A S E   N A M E”
      strUserName = “E N T E R   U S E R N A M E”
      strPassword = “E N T E R   P A S S W O R D”

      ‘Clear Spreadsheet
      Application.Sheets(“Sheet1”).Range(“A1:Z60000”).Cells.Clear

      ‘Validate Starting Invoice Date entered
      If Not IsDate(txtStartDate.Text) Then
      MsgBox “Invalid Starting Invoice Date.  Please verify”, vbOKOnly, “Invalid Date”
      txtStartDate.SetFocus
      Exit Sub
      End If

      ‘Validate Ending Invoice Date entered
      If Not IsDate(txtEndDate.Text) Then
      MsgBox “Invalid Ending Invoice Date.  Please verify”, vbOKOnly, “Invalid Date”
      txtEndDate.SetFocus
      Exit Sub
      End If

      ‘Setup connection to VISUAL database
      Set dbConn = CreateObject(“ADODB.Connection”)
      dbConn.Open “Driver={SQL Server};Server=” & strServerName & “;Database=” & strDatabase & “;Uid=” & strUserName & “;Pwd=” & strPassword

      ‘Setup recordset to query for invoices for specified date range
      Set rs = CreateObject(“ADODB.Recordset”)
      SQL = “SELECT REL.INVOICE_ID as [Invoice ID],RE.INVOICE_DATE as [Invoice Date], RE.CUSTOMER_ID as [Customer ID], C.NAME as [Customer], Sum(REL.AMOUNT*RE.SELL_RATE) as Revenue ” & _
      “FROM ((RECEIVABLE RE INNER JOIN RECEIVABLE_LINE REL ON RE.INVOICE_ID = REL.INVOICE_ID) INNER JOIN CUSTOMER C ON RE.CUSTOMER_ID = C.ID) INNER JOIN ACCOUNT A ON REL.GL_ACCOUNT_ID = A.ID ” & _
      “WHERE (((A.TYPE)=’R’)) ” & _
      “GROUP BY REL.INVOICE_ID, RE.CUSTOMER_ID, C.NAME, RE.INVOICE_DATE ” & _
      “HAVING RE.INVOICE_DATE>='” & SQLDate(txtStartDate.Text) & “‘ and RE.INVOICE_DATE<='” & SQLDate(txtEndDate.Text) & “‘ ” & _
      “ORDER BY REL.INVOICE_ID, RE.INVOICE_DATE”
      rs.Open SQL, dbConn, adOpenStatic

      If rs.State = 1 Then

          ‘Enter Column Headings onto spreadsheet from the recordset
      For iCols = 0 To rs.Fields.Count – 1
      Application.Sheets(“Sheet1”).Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
      Next

         ‘Set column headings to BOLD
      Application.Sheets(“Sheet1”).Range(Application.Sheets(“Sheet1”).Cells(1, 1), Application.Sheets(“Sheet1”).Cells(1, rs.Fields.Count)).Font.Bold = True

         ‘Paste recordset to spreadsheet
      Application.Sheets(“Sheet1”).Range(“A2”).CopyFromRecordset rs

         ‘Auto-fit columns widths
      Application.Sheets(“Sheet1”).Range(“A1:Z60000”).Columns.AutoFit

      End If

      rs.Close

      Set rs = Nothing
      dbConn.Close
      me.Hide

    14. You also need to add the following 2 functions to the Code window. These functions are used to format the dates for queries on a SQL Server database. Be sure to place them outside of the CommandButton1_Click() procedure. You can download these functions.VBA CodePrivate Function SQLDate(dt)
      SQLDate = Year(dt) & pd(Month(dt), 2) & pd(Day(dt), 2)
      End FunctionPrivate Function pd(n, totalDigits)
      If totalDigits > Len(n) Then
      pd = String(totalDigits – Len(n), “0”) & n
      Else
      pd = n
      End If
      End Function
    15. OK…We’re almost done…I promise. Now we need a macro that will prompt the user to enter the date range for the report. Under the Developer tab, click on Macros menu item.Macro Menu Excel VBA
    16. Enter SalesReport for the Macro name (as shown). Click Create.Create Macro
    17. Enter the code “UserForm1.Show” right after the “Sub SalesReport()” (as shown)UserForm.show

And we’re done!  The report is ready to be used (and distributed) just like any other excel file.

I know it may have seemed complicated but it really isn’t.  Once you get the hang of it…it’s actually quite simple and easy to put in place.

To run the report:

  1. Click on the Macros MenuMacro Menu Excel VBA
  2. Select “SalesReport” from the Macro list. Click on RunRun Macro
  3. Enter the starting and ending dates for the report.  Click Generate ReportUser Form
  4. And there you go…the report based on your date range.Results

 

 

 

Copyright 2019 visualnuggets © All Rights Reserved