As another example of integrating different Office applications, you will extract some data from Access, chart it using Excel, and e-mail the chart using Outlook. The code has been set up as four procedures. The first procedure is a sub procedure named EmailChart that establishes the operating parameters and executes the other three procedures. Note that the code uses early binding, and you need to create references to the ADO and Outlook object libraries:
'Gets data from Access using SQL statement 'Creates chart and emails chart file to recipient
Dim rngData As Excel.Range Dim sFileName As String Dim sRecipient As String sSQL = "SELECT Product, Sum(Revenue)" sSQL = sSQL & " FROM SalesData"
sSQL = sSQL & " WHERE Date >= #1/1/2006# and Date<#1/1/2007#" sSQL = sSQL & " GROUP BY Product;"
sFileName = "C:\VBA_Prog_Ref\Chapter19\Chart.xlsx"
' Replace the made up email address with a valid one (perhaps your own) sRecipient = "[email protected]"
Set rngData = rngSalesData(sSQL) ChartData rngData, sFileName SendEmail sRecipient, sFileName
End Sub sSQL is used to hold a string that is a SQL (Structured Query Language) command. SQL is covered in more detail in Chapter 20. In this case, the SQL specifies that you want to select the unique product names and the sum of the revenues for each product from your Access database SalesData table for all dates in the year 2006. sFileName defines the path and filename that will be used to hold the chart workbook. sRecipient holds the e-mail address of the person you are sending the chart to.
The code then executes the rngSalesData function that is listed as follows. The function accepts the SQL statement as an input parameter and returns a reference to the range containing the extracted data, which is assigned to rngData. The ChartData sub procedure is then executed, passing in the data range, as well as the path and filename for the chart workbook. Finally, the SendEMail sub procedure is executed, passing in the recipient's e-mail address and the location of the chart workbook to be attached to the e-mail:
Function rngSalesData(sSQL As String) As Excel.Range 'Function to extract data from database using 'SQL statement in sSQL
'Returns a reference to the range containing 'the data
Dim con As ADODB.Connection Dim rsSales As ADODB.Recordset
'Establish connection to database Set con = New ADODB.Connection con.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
'Open recordset based on Sales Table Set rsSales = New ADODB.Recordset Set rsSales.ActiveConnection = con rsSales.Open sSQL
'Clear sheet and bring in new data
With Worksheets("Data") .Cells.Clear
'Copy entire recordset data to worksheet, starting in A1 .CopyFromRecordset rsSales 'Return reference to data range Set rngSalesData = .CurrentRegion End With End With
'Release object variables Set rsSales = Nothing Set con = Nothing
The rngSalesData function is similar to the GetSalesDataViaADO sub procedure presented earlier. Instead of getting the entire SalesData table from the database, it uses SQL to be more selective. It clears the worksheet named Data and copies the selected data to a range starting in A1. It does not add the field names to the worksheet, just the product names and total revenue. It uses the CurrentRegion property to obtain a reference to all the extracted data and assigns the reference to the return value of the function:
Sub ChartData(rngData As Range, sFileName As String) 'Procedure to create chart based on data in rngData 'Binds data to chart as arrays 'Saves chart to path and file in sFileName
'Create new workbook With Workbooks.Add
'Create new chart sheet With .Charts.Add
'Create new data series and assign data With .SeriesCollection.NewSeries
.XValues = rngData.Columns(1).Value .Values = rngData.Columns(2).Value End With
'Format chart .HasLegend = False .HasTitle = True
.ChartTitle.Text = "Year 2006 Revenue" End With
'Save workbook and close it Application.DisplayAlerts = False .SaveAs sFileName Application.DisplayAlerts = True
.Close End With End Sub
ChartData has input parameters to define the range containing the data to be charted and the destination for the file it creates. It creates a new workbook and adds a chart sheet to it. It creates a new series in the chart and assigns the values from the data range as arrays to the axes of the series. DisplayAlerts is set to False to prevent a warning if it overwrites an old file of the same name.
The following SendEmail sub sends the chart workbook as an attachment to an e-mail:
Sub SendEmail(sRecipient As String, sAttachment As String) 'Send email to sRecipient 'Attaching file in sAttachment
Dim olApp As Object Dim olNameSpace As Object Dim olFolder As Object Dim olMail As Object
Set olApp = CreateObject("Outlook.Application") Set olNameSpace = olApp.GetNamespace("MAPI") 'Might be necessary to Logon 'olNameSpace.Logon "UserName", "Password" Set olFolder = olNameSpace.GetDefaultFolder(6) Set olMail = olApp.CreateItem(0) With olMail
.Subject = "Year 200 6 Revenue Chart"
.Body = "Workbook with chart attached"
.Attachments.Add sAttachment .Send End With
SendEMail has input parameters for the e-mail address of the recipient and the filename of the attachment for the e-mail. If your Outlook configuration requires you to log on, you will need to uncomment the lines that get a reference to the Namespace and supply the username and password. A new mail item is created, using the Createltem method. Text is added for the subject line and the body of the e-mail, and the recipient and attachment are specified. The Send method sends the e-mail.
Was this article helpful?