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’.
-
- Under the Developer tab, click on Visual Basicto launch the VBA interface.
- Select UserForm from the INSERT menu.
- Select the Label icon from the TOOLBOX and place it on the form (as shown).
- Right click on the label and select Propertiesfrom the popup menu. Enter “Starting Invoice Date” in the Caption property (as shown).
- Select the Textbox icon from the TOOLBOX and place it next to the “Starting Invoice Date” label on the form (as shown).
- Right click in the textbox and select Properties from the popup menu. Enter “txtStartDate” in the Name property (as shown).
- Repeat steps 3 through 6 to enter another label and textbox with the following properties:Label Caption: “Ending Invoice Date”
Textbox Name: “txtEndDate” - Select the CommandButton from the TOOLBOX and place it on the form (as shown).
- Right click on the CommandButton and select Propertiesfrom the popup menu. Enter “Generate Report” in the Caption property (as shown).
- 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”.
- Before we start inserting code we need to set one reference in order to access the VISUAL database. Select References… from the TOOLS menu.
- Place a checkmark next to Microsoft ActiveX Data Objects 2.6 Library. Click OK.
- 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, adOpenStaticIf 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.AutoFitEnd If
rs.Close
Set rs = Nothing
dbConn.Close
me.Hide - 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 - 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.
- Enter SalesReport for the Macro name (as shown). Click Create.
- Enter the code “UserForm1.Show” right after the “Sub SalesReport()” (as shown)
- Under the Developer tab, click on Visual Basicto launch the VBA interface.
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:
- Click on the Macros Menu
- Select “SalesReport” from the Macro list. Click on Run
- Enter the starting and ending dates for the report. Click Generate Report
- And there you go…the report based on your date range.