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.

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.

The Developer tab should now be appear in the ribbon.

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

1. 1. Under the Developer tab, click on Visual Basicto launch the VBA interface.
   2. Select UserForm from the INSERT menu.
   3. Select the Label icon from the TOOLBOX and place it on the form (as shown).
   4. Right click on the label and select Propertiesfrom the popup menu.  Enter “Starting Invoice Date” in the Caption property (as shown).
   5. Select the Textbox icon from the TOOLBOX and place it next to the “Starting Invoice Date” label on the form (as shown).
   6. Right click in the textbox and select Properties from the popup menu.  Enter “txtStartDate” in the Name property (as shown).
   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).
   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.
   12. Place a checkmark next to Microsoft ActiveX Data Objects 2.6 Library.  Click OK.
   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)Dim 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.Private 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.
   16. Enter SalesReport for the Macro name (as shown). Click Create.
   17. Enter the code “UserForm1.Show” right after the “Sub SalesReport()” (as shown)

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 Menu
2. Select “SalesReport” from the Macro list. Click on Run
3. Enter the starting and ending dates for the report.  Click Generate Report
4. And there you go…the report based on your date range.